Php imports csv files to SQL files and inserts them into the database automatically (example)

Php imports csv files to SQL files and inserts them into the database automatically (example)

/** * Import a csv file using the php header * Edit: 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 ', '12m '); Define ('Db _ host', 'localhost '); Define ('Db _ user', 'root '); Define ('Db _ pass ',''); Define ('Db _ name', 'DB _ XXX '); // Import csv data in batches and generate an imported SQL file // The csv file column is province (province, city, area, position, address). Be sure to match the database field $ 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' ('Vince ', '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 generated successfully. \ N "; } Else { Echo $ csv. "file writing failed !!! \ N "; } } // Write data to the database (php imports the SQL file generated by the 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]. "data is successfully written. \ N \ r "; } Else { Echo 'data writing failed '; Echo $ csv_arr [$ I]. "data writing failed !!! \ N \ r "; } $ I ++; } Mysql_close ($ lnk ); Unset ($ SQL, $ SQL _list, $ write_result, $ csv_arr, $ query_result, $ lnk ); ?>