Step 1
Reading different languages from excel and dump into database require you to make sure
following:
1. Database Charset : UTF-8
2. Table Charset : UTF-8
3. Table Columns Charset : UTF-8 ( columns to store foreign language characters must be
set to UTF-8)
Step 2
Now we need to read Excel and import it into database, there are many libraries can be use to
read excel file using php, I used PHPExcel
Now, what we need is to setup database connection
$cn = mysql_connect('localhost', 'db_user, 'db_password');
$db = mysql_select_db('TableName');
Now before use mysql_query we need to set following
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER SET utf8");
Now, use mysql_query to INSERT records into database for UTF-8 characters
Sample PHP Code using PHPExcel
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = '[ExcelFileName].xls';
/** Create a new Reader of the type defined in $inputFileType **/
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
//$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndexByName('All Countries');
# database connection
$cn = mysql_connect('localhost', 'DB_User', 'DB_Password');
$db = mysql_select_db('DB_TableName');
# set db connection encoding to send correct characters
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER SET utf8");
$data = array();
$i = 0;
foreach ($objWorksheet->getRowIterator() as $row) {
if($i++==0) continue; // skip 1st row if heading
$rowIndex = $row->getRowIndex();
$number = addslashes($objWorksheet->getCell('A' . $rowIndex)->getCalculatedValue());
$currency_code = addslashes($objWorksheet->getCell('B' . $rowIndex)->getCalculatedValue());
$english = addslashes($objWorksheet->getCell('C' . $rowIndex)->getCalculatedValue());
$czech = addslashes($objWorksheet->getCell('D' . $rowIndex)->getCalculatedValue());
$russian = addslashes($objWorksheet->getCell('E' . $rowIndex)->getCalculatedValue());
$polish = addslashes($objWorksheet->getCell('F' . $rowIndex)->getCalculatedValue());
$chiness = addslashes($objWorksheet->getCell('G' . $rowIndex)->getCalculatedValue());
$query = "INSERT INTO `country_codes`
VALUES(null, '$number', '$currency_code', '$english', '$czech', '$russian', '$polish', '$chiness');";
mysql_query($query, $cn);
}
exit;
?>
Comments
Post a Comment