/**
- * PHP Header import CSV file
- * Editor: Bbs.it-home.org
- */
- Header (' content-type:text/html;charset=utf-8; ');
- @date_default_timezone_set (' Asia/shanghai ');
- @error_reporting (E_all);
- @ini_set (' display_errors ', ' on ');
- @ini_set (' Memory_limit ', ' 512M ');
- Define (' db_host ', ' localhost ');
- Define (' Db_user ', ' root ');
- Define (' Db_pass ', ');
- Define (' db_name ', ' db_xxx ');
Bulk import of CSV format data and generate an import SQL file
- CSV file listed as province (province, city, area, position, address), note that the database field corresponds to
- $sql _list = Array ();
- foreach (Array (
- ' chongqing.csv ' = = Array (' name ' = ' Chongqing ', ' sort ' = + 4),
- ' guizhou.csv ' = = Array (' name ' = ' Guizhou province ', ' sort ' = 24),
- ' shanghai.csv ' = = Array (' name ' = ' + ' Shanghai ', ' sort ' = + 2),
- ' sichuan.csv ' = = Array (' name ' = ' "Sichuan Province ', ' sort ' = 23),
- ' Yunnan.csv ' + array (' name ' = ' "Yunnan province ', ' sort ' = + 25)
- ) as $filename = $_data) {
- $sql = $sql _start = "INSERT into ' tbl_data ' (' Province ', ' City ', ' area ', ' position ', ' address ', ' sort ') VALUES";
- $handle = fopen ($filename, ' R ');
- $i = 0;
- Echo '
'; - while ($row = Fgetcsv ($handle, 1000, ', ')) {
- if (! $row) continue;
- if ($i <1000000) {
- $sql. = "('". Iconv (' GBK ', ' utf-8 ', trim ($row [0])). "', '". Iconv (' GBK ', ' utf-8 ', trim ($row [1])). "', '". Iconv (' GBK ', ' Utf-8 ', Trim ($row [2])). "', '". Iconv (' GBK ', ' utf-8 ', Trim ($row [3])). "', '". Iconv (' GBK ', ' utf-8 ', Trim ($row [4])). "', '". $_data[' sort ']. "'),";
- }else{
- Break
- }
- $i + +;
- }
- $sql = RTrim ($sql, ', ');
- $sql _list[] = $sql;
- $csv = ' Csv_import_ '. $filename. ' _ '. Date (' y-m-d '). SQL ';
- $csv _arr[] = $csv;
- $write _result = file_put_contents ($csv, implode ("\ n", $sql _list));
- if ($write _result) {
- echo $csv. " File generation succeeded. \ n ";
- }else{
- echo $csv. " File Write Failed!!! \ n ";
- }
}
Write to database (SQL file generated by PHP import CSV file)
- $lnk = mysql_connect (Db_host, Db_user, Db_pass);
- mysql_select_db (db_name, $lnk);
- mysql_query (' SET sql_mode= ' "', $lnk);
- mysql_query (' SET NAMES UTF8 ', $lnk);
- $i = 0;
- foreach ($sql _list as $sql) {
- $query _result = mysql_query ($sql, $lnk);
- if ($query _result) {
- Echo ' Write data: ';
- echo $csv _arr[$i]. " The data was written successfully. \n\r ";
- }else{
- Echo ' Write data failed ';
- echo $csv _arr[$i]. " Data write Failed!!! \n\r ";
- }
- $i + +;
- }
- Mysql_close ($lnk);
- Unset ($sql, $sql _list, $write _result, $csv _arr, $query _result, $lnk);
- ?>
Copy Code |