PHP import CSV to SQL file automatically insert database (sample)

Source: Internet
Author: User
  1. /**

  2. * PHP Header import CSV file
  3. * Editor: Bbs.it-home.org
  4. */
  5. Header (' content-type:text/html;charset=utf-8; ');
  6. @date_default_timezone_set (' Asia/shanghai ');
  7. @error_reporting (E_all);
  8. @ini_set (' display_errors ', ' on ');
  9. @ini_set (' Memory_limit ', ' 512M ');
  10. Define (' db_host ', ' localhost ');
  11. Define (' Db_user ', ' root ');
  12. Define (' Db_pass ', ');
  13. Define (' db_name ', ' db_xxx ');

  14. Bulk import of CSV format data and generate an import SQL file

  15. CSV file listed as province (province, city, area, position, address), note that the database field corresponds to
  16. $sql _list = Array ();
  17. foreach (Array (
  18. ' chongqing.csv ' = = Array (' name ' = ' Chongqing ', ' sort ' = + 4),
  19. ' guizhou.csv ' = = Array (' name ' = ' Guizhou province ', ' sort ' = 24),
  20. ' shanghai.csv ' = = Array (' name ' = ' + ' Shanghai ', ' sort ' = + 2),
  21. ' sichuan.csv ' = = Array (' name ' = ' "Sichuan Province ', ' sort ' = 23),
  22. ' Yunnan.csv ' + array (' name ' = ' "Yunnan province ', ' sort ' = + 25)
  23. ) as $filename = $_data) {
  24. $sql = $sql _start = "INSERT into ' tbl_data ' (' Province ', ' City ', ' area ', ' position ', ' address ', ' sort ') VALUES";
  25. $handle = fopen ($filename, ' R ');
  26. $i = 0;
  27. Echo '
    ';
  28. while ($row = Fgetcsv ($handle, 1000, ', ')) {
  29. if (! $row) continue;
  30. if ($i <1000000) {
  31. $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 ']. "'),";
  32. }else{
  33. Break
  34. }
  35. $i + +;
  36. }
  37. $sql = RTrim ($sql, ', ');
  38. $sql _list[] = $sql;
  39. $csv = ' Csv_import_ '. $filename. ' _ '. Date (' y-m-d '). SQL ';
  40. $csv _arr[] = $csv;
  41. $write _result = file_put_contents ($csv, implode ("\ n", $sql _list));
  42. if ($write _result) {
  43. echo $csv. " File generation succeeded. \ n ";
  44. }else{
  45. echo $csv. " File Write Failed!!! \ n ";
  46. }

  47. }

  48. Write to database (SQL file generated by PHP import CSV file)

  49. $lnk = mysql_connect (Db_host, Db_user, Db_pass);
  50. mysql_select_db (db_name, $lnk);
  51. mysql_query (' SET sql_mode= ' "', $lnk);
  52. mysql_query (' SET NAMES UTF8 ', $lnk);
  53. $i = 0;
  54. foreach ($sql _list as $sql) {
  55. $query _result = mysql_query ($sql, $lnk);
  56. if ($query _result) {
  57. Echo ' Write data: ';
  58. echo $csv _arr[$i]. " The data was written successfully. \n\r ";
  59. }else{
  60. Echo ' Write data failed ';
  61. echo $csv _arr[$i]. " Data write Failed!!! \n\r ";
  62. }
  63. $i + +;
  64. }
  65. Mysql_close ($lnk);
  66. Unset ($sql, $sql _list, $write _result, $csv _arr, $query _result, $lnk);
  67. ?>

Copy Code
  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.