Spiga

parsing csv file with php and inserting into mysql database

May 22, 08 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:
  1. $columns = "`c1` , `c2`, `c3`, `c4` ";
  2. $handle = fopen("test.csv", "r");
  3. while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  4.   foreach( $data as $v ) {
  5.      $insertValues="'".addslashes(trim($v))."'";
  6.   }
  7.   $values=implode(',',$insertValues);
  8.   $sql = "INSERT INTO `tableName` ( $columns ) VALUES ( $values )";
  9.   mysql_query($sql) or die('SQL ERROR:'.mysql_error());
  10. }
  11. 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.