PHP import CSV to MySQL database example

/** * Import CSV file into MySQL database * edit:bbs.it-home.org */ $databasehost = "localhost"; $databasename = "Test"; $databasetable = "Sample"; $databaseusername = "Test"; $databasepassword = ""; $fieldseparator = ","; $lineseparator = "\ n"; $csvfile = "Filename.csv"; /********************************/ /* Would the Ampty field at the beginning of these records? /* This was useful if you had a table with the first field being an auto_increment integer /* and the CSV file does not having such as empty field before the records. /* Set 1 for Yes and 0 for No. Attention:don ' t set to 1 if is not sure. /* This can-dump data in the wrong field if this extra-does not exist in the table /********************************/ $addauto = 0; /********************************/ /* would to save the MySQL queries in a file? If Yes set $save to 1. /* Permission on the file should is set to 777. Either upload a sample file through FTP and /* Change the permissions, or execute at the Prompt:touch output.sql && chmod 777 Output.sql /********************************/ $save = 1; $outputfile = "Output.sql"; /********************************/ if (!file_exists ($csvfile)) { echo "File not found. Make sure specified the correct path.\n "; Exit } $file = fopen ($csvfile, "R"); if (! $file) { echo "Error opening data file.\n"; Exit } $size = FileSize ($csvfile); if (! $size) { echo "File is empty.\n"; Exit } $csvcontent = Fread ($file, $size); Fclose ($file); $con = @mysql_connect ($databasehost, $databaseusername, $databasepassword) or Die (Mysql_error ()); @mysql_select_db ($databasename) or Die (Mysql_error ()); $lines = 0; $queries = ""; $linearray = Array (); foreach (Split ($lineseparator, $csvcontent) as $line) { $lines + +; $line = Trim ($line, "\ t"); $line = Str_replace ("\ R", "", $line); /************************************ This line escapes the special character. Remove it if entries is already escaped in the CSV file ************************************/ $line = Str_replace ("'", "\ '", $line); /*************************************/ $linearray = Explode ($fieldseparator, $line); $linemysql = Implode ("', '", $linearray); if ($addauto) $query = "INSERT into $databasetable values (', ' $linemysql ');"; Else $query = "INSERT into $databasetable values (' $linemysql ');"; $queries. = $query. "\ n"; @mysql_query ($query); } @mysql_close ($con); if ($save) { if (!is_writable ($outputfile)) { echo "File is not writable, check permissions.\n"; } else { $file 2 = fopen ($outputfile, "w"); if (! $file 2) { echo "Error writing to the output file.\n"; } else { Fwrite ($file 2, $queries); Fclose ($file 2); } } } echo "How many $lines records are found in the CSV file. \ n"; ?> Copy Code

