当前位置：Gxlcms > PHP教程 > php导入csv到MySql数据库的例子

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

时间：2021-07-01 10:21:17 帮助过：56人阅读

  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. ?>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！