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

WBOY
Lepaskan: 2016-07-25 08:56:12
asal
818 orang telah melayarinya
  1. /**

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

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

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

  30. echo "File not found. Make sure you specified the correct path.\n";
  31. exit;
  32. }
  33. $file = fopen($csvfile,"r");

  34. if (!$file) {

  35. echo "Error opening data file.\n";
  36. exit;
  37. }
  38. $size = filesize($csvfile);

  39. if (!$size) {

  40. echo "File is empty.\n";
  41. exit;
  42. }
  43. $csvcontent = fread($file,$size);

  44. fclose($file);

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

  46. @mysql_select_db($databasename) or die(mysql_error());
  47. $lines = 0;

  48. $queries = "";
  49. $linearray = array();
  50. foreach(split($lineseparator,$csvcontent) as $line) {

  51. $lines++;

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

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

  54. /************************************

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

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

  61. if($addauto)

  62. $query = "insert into $databasetable values('','$linemysql');";
  63. else
  64. $query = "insert into $databasetable values('$linemysql');";
  65. $queries .= $query . "\n";

  66. @mysql_query($query);

  67. }
  68. @mysql_close($con);

  69. if ($save) {

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

  71. echo "File is not writable, check permissions.\n";
  72. }
  73. else {

  74. $file2 = fopen($outputfile,"w");
  75. if(!$file2) {

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

  84. echo "在csv文件中共找到多少 $lines 条记录.\n";
  85. ?>
复制代码


sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan