php导入csv到MySql数据库的例子

3. /**

4. * 将csv文件导入到mysql数据库
5. * edit: bbs.it-home.org
6. */
7. $databasehost = "localhost";
8. $databasename = "test";
9. $databasetable = "sample";
10. $databaseusername ="test";
11. $databasepassword = "";
12. $fieldseparator = ",";
13. $lineseparator = "\n";
14. $csvfile = "filename.csv";

15. /********************************/

16. /* Would you like to add an ampty field at the beginning of these records?
17. /* This is useful if you have a table with the first field being an auto_increment integer
18. /* and the csv file does not have such as empty field before the records.
19. /* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
20. /* This can dump data in the wrong fields if this extra field does not exist in the table
21. /********************************/
22. $addauto = 0;
23. /********************************/

24. /* Would you like to save the mysql queries in a file? If yes set $save to 1.

25. /* Permission on the file should be set to 777. Either upload a sample file through ftp and
26. /* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
27. /********************************/
28. $save = 1;
29. $outputfile = "output.sql";
30. /********************************/

31. if (!file_exists($csvfile)) {

32. echo "File not found. Make sure you specified the correct path.\n";
33. exit;
34. }

35. $file = fopen($csvfile,"r");

36. if (!$file) {

37. echo "Error opening data file.\n";
38. exit;
39. }

40. $size = filesize($csvfile);

41. if (!$size) {

42. echo "File is empty.\n";
43. exit;
44. }

45. $csvcontent = fread($file,$size);

46. fclose($file);

47. $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());

48. @mysql_select_db($databasename) or die(mysql_error());

49. $lines = 0;

50. $queries = "";
51. $linearray = array();

52. foreach(split($lineseparator,$csvcontent) as $line) {

53. $lines++;

54. $line = trim($line," \t");

55. $line = str_replace("\r","",$line);

56. /************************************

57. This line escapes the special character. remove it if entries are already escaped in the csv file
58. ************************************/
59. $line = str_replace("'","\'",$line);
60. /*************************************/

61. $linearray = explode($fieldseparator,$line);

62. $linemysql = implode("','",$linearray);

63. if($addauto)

64. $query = "insert into $databasetable values('','$linemysql');";
65. else
66. $query = "insert into $databasetable values('$linemysql');";

67. $queries .= $query . "\n";

68. @mysql_query($query);

69. }

70. @mysql_close($con);

71. if ($save) {

72. if (!is_writable($outputfile)) {

73. echo "File is not writable, check permissions.\n";
74. }

75. else {

76. $file2 = fopen($outputfile,"w");

77. if(!$file2) {

78. echo "Error writing to the output file.\n";
79. }
80. else {
81. fwrite($file2,$queries);
82. fclose($file2);
83. }
84. }

85. }

86. echo "在csv文件*找到多少 $lines 条记录.\n";
87. ?>

复制代码