parsing csv file with php and inserting into mysql database

by Gabi Solomon

As some of my posts they usualy start by a question on a forum i write ( you can find them in the about page ). So this time some one asked how do you insert an excel file into a mysql database, and since this is a common problem for many beginers, and had to answer this before i decided to make a post about it so next time just post the link to this ( will decrease my spent time by not writing the same thing twice and bring some trafic ... a win-win situation ) ).

But enough chit-chat, lets get down to the dirty job. I will try to brake it down into small steps and give a few explination at each one.

1. Export the excel file into CSV (comma-separated values)

For those who don't know CSV is a file type that stores tabular data. This is a very basic and quite old format and this is why CSV files are suported on all computer platforms.
Example:

1997	Ford	E350	ac, abs, moon	3000.00
1999	Chevy	Venture "Extended Edition"		4900.00
1996	Jeep	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799.00

The above table of data may be represented in CSV format as follows:

1997,Ford,E350,ac, abs, moon,3000.00
1999,Chevy,Venture "Extended Edition",,4900.00
1996,Jeep,Grand Cherokee,MUST SELL! air, moon roof, loaded,4799.00

As you can see from the above example each row from the table is stored in a separate in the CSV file, and the values from each row are separated with a comma, from wich comma-separated values name, pretty no-brainer deduction. )

When you will try to export the excel file into this format you will most likely get an error like :

file.csv may contain features that are not compatible with CSV ...DO U WANT TO KEEP THE WORKSBOOK IN THIS FORMAT ?

This is quite normal since the excel file contains things like formulas, cell styles and other things that cannot be saved in the CSV format.

2. Import the CSV into MySQL database

For this step you actualy have 2 options. One the one you read in the title of this post, by using PHP, but there is an easier way to do it by using PhpMyAdmin.

Importing CSV using PhpMyAdmin
This is done quite easy. Most PMA versions have support for CSV, have not seen a server installed PMA that didnt.
You just need to login into PMA, and go to the table you want to import the data into. Once there you select the Import Tab and on the page select the CSV format, select your file and click import.
One thing you must keep in mind is that you need to have the same number of columns in the mySQL database and the CSV file.

Importing CSV using Php
This method although a bit more tricky for a beginner gives you a bit more freedom with the import procedure. For one thing it doesn't matter how many columns does the CSV file have or the order in it. You can make the script insert the data in almost any manner you want.

So here is the actual code :

PLAIN TEXT

PHP:

$columns = "`c1` , `c2`, `c3`, `c4` ";
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
foreach( $data as $v ) {
$insertValues="'".addslashes(trim($v))."'";
}
$values=implode(',',$insertValues);
$sql = "INSERT INTO `tableName` ( $columns ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
}
fclose($handle);

That is about it ... of course i assume you have a mysql connection opened ... if you dont know what i am talking about you might want to google-it.

There is another alternative, to use a php class from phpclasses.org called Quick CSV import that is going to make things a little easyer for you.

I am not going to be to descriptive about the script, because i think it is pretty basic, it basically makes a mysql insert query for each row. For those who don't understand parts of it i am happily to answer any of you questions.

Hope you have gained even a little knowledge from this article, and i am awaiting your comments.

Date May 22, 2008 at 23:43
Under Programing, php&mysql 101 Tags: csv, import, mysql, parsing, php
You can follow any responses to this entry through the RSS 2.0 feed .
You can leave a response , or trackback from your own site .

Calvin Lough

Are you sure that your script will work when there is a comma inside of a value? For example, the Jeep line where you have "air, moon roof, loaded" won't it think that those are seperate values and split those up too. I think you would end up with 7 values instead of 5. There isn't a very easy way to fix that either. And don't you need to have quotes around string values that you insert into mysql?

Gabi Solomon

Thank you Calvin Lough for pointing that error out. I modified the script to avoid that from happening. And about the quotes in the mysql query, they are in place look at line 05

Alex

I found your site on technorati and read a few of your other posts. Keep up the good work. I just added your RSS feed to my Google News Reader. Looking forward to reading more from you down the road!

xdrone

How do I ad a where clause if my csv contains a column id.

Gabi Solomon

a where condition to do what ?
sorry i dont follow

wordpressfun

Cool!
In progress of work of our post a found little error.

$values=inplode(',',$insertValues);

Please, change to

$values=implode(',',$insertValues);

Gabi Solomon

thanks for the error spoting :)
fixed

Tom Schäfer

Your way of importing csv data is too slow.
Ever thought of using LOAD DATA INFILE ?
The major work is to clean corrupted file data. But these operations have their focus on normalizing the columns in the import files. (Unicode checks, Linebreaks converting, identifying separators and delimiters)

When these works are done, an import of big files is very fast.
1 million lines in less then 60 seconds. The same row by row lasts 10 minutes and more.
Cheers.

Gabi Solomon

@Tom
I agree with you that is a much more faster option.
This was just to show how you could do it with php.

yanti

hi,...i had tried use ur code but it gives me an error saying that SQL error: Column count doesnt match with values count in 1 row...
wht does it mean?

Gabi Solomon

it means you have a different number of columns in mysql then in your CSV file

yanti

the code is like this,..

if(isset($_POST['submit']))
{
$columns = "no_ahli, no_pin, nama, no_ic_baru, no_ic_lama";
$handle = fopen("filename.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
foreach( $data as $v ) {
$insertValues="'".addslashes(trim($v))."'";
}
$values=implode(',',$insertValues);
$sql = "INSERT INTO `kmmb_member1` ( $columns ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
}
fclose($handle);
print "Import done";
}
else
{

print "";
print "Type file name to import:";
print "";
print "";
}

GS Design Just another developer blog