php导入csv到sql文件自动插入数据库(示例)

3. /**

4. * php header导入csv文件
5. * 编辑：bbs.it-home.org
6. */
7. header('Content-type:text/html;charset=utf-8;');
8. @date_default_timezone_set('Asia/Shanghai');
9. @error_reporting(E_ALL);
10. @ini_set('display_errors', 'On');
11. @ini_set('memory_limit', '512M');
12. define('DB_HOST', 'localhost');
13. define('DB_USER', 'root');
14. define('DB_PASS', '');
15. define('DB_NAME', 'DB_XXX');

16. //批量导入csv格式数据并生成导入sql文件

17. //csv文件列为 省（province、city、area、position、address），注意要和数据库字段对应
18. $sql_list = array();
19. foreach(array(
20. 'chongqing.csv' => array('name' => '重庆市','sort' => 4),
21. 'guizhou.csv' => array('name' => '贵州省','sort' => 24),
22. 'shanghai.csv' => array('name' => '上海','sort' => 2),
23. 'sichuan.csv' => array('name' => '四川省','sort' => 23),
24. 'yunnan.csv' => array('name' => '云南省','sort' => 25)
25. ) as $filename => $_data) {
26. $sql = $sql_start = "INSERT INTO `Tbl_Data` (`province`,`city`,`area`,`position`,`address`,`sort`) VALUES ";
27. $handle = fopen($filename,'r');
28. $i=0;
29. echo '

    ';

30. while( $row = fgetcsv($handle,1000,',') ){
31. if(!$row) continue;
32. if($i $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']."'),";
33. }else{
34. break;
35. }
36. $i++;
37. }
38. $sql = rtrim($sql,',');
39. $sql_list[] = $sql;
40. $csv = 'csv_import_'.$filename.'_'.date('Y-m-d').'.sql';
41. $csv_arr[] = $csv;
42. $write_result = file_put_contents($csv,implode("\n\n",$sql_list));
43. if($write_result){
44. echo $csv."文件生成成功。\n";
45. }else{
46. echo $csv."文件写入失败！！！\n";
47. }

48. }

49. //写入数据库(php导入csv文件生成的sql文件)

50. $lnk = mysql_connect(DB_HOST, DB_USER, DB_PASS);
51. mysql_select_db(DB_NAME, $lnk);
52. mysql_query('SET SQL_MODE=""', $lnk);
53. mysql_query('SET NAMES utf8', $lnk);
54. $i = 0;
55. foreach($sql_list as $sql) {
56. $query_result = mysql_query($sql,$lnk);
57. if($query_result){
58. echo '写入数据：';
59. echo $csv_arr[$i]."数据写入成功。\n\r";
60. }else{
61. echo '写入数据失败';
62. echo $csv_arr[$i]."数据写入失败！！！\n\r";
63. }
64. $i++;
65. }
66. mysql_close($lnk);
67. unset($sql,$sql_list,$write_result,$csv_arr,$query_result,$lnk);
68. ?>

复制代码