The Coding Pad

Programming blog – Discussions, Tutorials, Resources

The Coding Pad header image 2







Converting csv to sql using php |

September 24th, 2007 · 6 Comments · Data and Databases, PHP

In previous posts we’ve discussed getting your csv files into sql format using different methods. I walked you through the process from csv to sql using HeidiSQL and PHPMyAdmin. I then did a follow up on how to import a large csv file into a MySQL database using the MySQL command prompt. In all these instances, what we were essentially doing was creating queries, just in different ways.

Well, today I want to present you with another method of getting your csv file into sql, using PHP code. For this piece of code, the full credit goes to legend. You need to make sure the database is already created before you dump the date.

/********************************************************************************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************************************************************************/
$databasehost = “localhost”;
$databasename = “test”;
$databasetable = “sample”;
$databaseusername =”test”;
$databasepassword = “”;
$fieldseparator = “,”;
$lineseparator = “\n”;
$csvfile = “bbqrest.csv”;
/********************************************************************************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don’t set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be 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 you 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 are 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 {
$file2 = fopen($outputfile,”w”);

if(!$file2) {
echo “Error writing to the output file.\n”;
}
else {
fwrite($file2,$queries);
fclose($file2);
}
}

}

echo “Found a total of $lines records in this csv file.\n”;

?>

So that is one nice easy way to do it, and the code is easy to follow and understand, so that you can mod and adapt it to your needs. Many thanks to legend for this code.

If you enjoyed this post, make sure you subscribe to my RSS feed!

Related Posts

Tags:

6 Comments so far ↓

  • reseller hosting

    I once needed a script to convert a cvs file to sql to upload to phpmyadmin. I will surely keep this script in mind and give it a try as it seems to be really useful.

  • paulmo

    mary if you read/respond to questions here…i’ve modified and ftp uploaded code to server (with <?php tag at top of code—it’s missing in your example (?). then i tried to import large cvs file through phpadmin/mysql and it’s still getting hung up. i’m obviously missing something. help? thanks

  • mary

    paulmo,
    Not too sure why it’s getting hung up, might be that you’re running out of execution time. Try adding the following line after the initial opening tags:
    set_time_limit(300);

    You can also check the comments in the original post by legend, click on the link in the post where I credit him for the code, you might find some answers there. I have used this for smaller csv file imports and it worked great, so you just might be running out of execution time because of the size of your file.

    Let me know how it goes for you.

    mary

  • reviews

    Thanks for a lovely site, I am very impressed :-)

  • kalid

    This code does not work! I have got the respectively and copying and pasting the code in between comes up with syntax errors?? what am I doing wrong ? i ignored the comments in the code and just copied and code

Leave a Comment

Subscribe without commenting