Spiga

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 :
[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);
[/php]

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.

  • http://twitter.com/appathy/ 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?

  • http://www.gsdesign.ro 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

  • http://street-streetmachine.blogspot.com/ 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.

  • http://www.gsdesign.ro/ Gabi Solomon

    a where condition to do what ?
    sorry i dont follow

  • http://4wordpress.ru wordpressfun

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

    $values=inplode(‘,’,$insertValues);

    Please, change to

    $values=implode(‘,’,$insertValues);

  • http://www.gsdesign.ro/ 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.

  • http://www.gsdesign.ro/ 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?

  • http://www.gsdesign.ro/ 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 “”;
    }

  • Rhiane Caña

    Hi, Thanks a lot to your blog. It helps me to solve my csv upload. :)

    Because of your blog, I come up an idea for csv upload. I updated you code.

    <?php

    $filename = $_FILES['uploadFile']['name'];
    $file_ext = pathinfo($filename, PATHINFO_EXTENSION);
    $file_path = “uploads/”.$filename;

    if(strtoupper($file_ext)=='CSV'){
    if(move_uploaded_file($_FILES["uploadFile"]['tmp_name'],$file_path)){

    $file1 = fopen($file_path, 'r');
    // initialize header…
    $ctr1 = 0;
    while(($data1 = fgetcsv($file1 , 1000, “,”)) !== FALSE){
    if($ctr1==0){
    foreach($data1 as $v1){
    $insert_headers[] = “'”. addslashes(trim($v1)) .”'”;
    }
    $columns = implode(',', $insert_headers);
    unset($insert_headers);
    $ctr1 = 1;
    }
    }
    fclose($file1);

    $file = fopen($file_path, 'r');

    // initialize data…
    $ctr2 = 0;
    while(($data = fgetcsv($file , 1000, “,”)) !== FALSE){
    foreach($data as $v){
    $insert_values[] = “'”. addslashes(trim($v)) .”'”;
    }

    $values = implode(',', $insert_values);
    unset($insert_values);

    if($ctr2!=0){
    $sql_query = “INSERT INTO
    tbl_article_info($columns)
    VALUES($values)”;
    }
    else{
    $ctr2 = 1;
    }

    mysql_query($sql_query) or die(“Query Error:” . mysql_error());
    }
    fclose($file);

    echo “success”;
    }
    else{
    echo 'failed';
    }
    }
    else{
    echo “THE FILE IS NOT IN CSV FORMAT.”;
    }
    ?>

  • http://twitter.com/sanaindia Sana India Associate

    Great Code

  • Poster

    You don’t need PHP to do this though, you can use MySQL Load Data Infile (or local infile) http://dev.mysql.com/doc/refman/5.1/en/load-data.html

  • Anonymous

    I need to write a script to do the following.
    Hopefully someone can help me with this. Seems pretty similar to the above, but I am still learning.
    I need to write a script that does the following.1. read data from csv files in a bunch of directories (the list of directories will grow in time)2. the idea is that these csv files will be updated by car dealers and then uploaded to their respective directories thru ftp (I’m good with this bit as I will set the dir’s and access)3. problem comes in that at the dealer side the content of the files will keep changing. (some car info will remain / some will be deleted / and new ones will be added), this goes for all the dealers.4. With every csv file they will upload images relating to the data in the csv files, so I will need to link this as well somehow.5. the idea is to run this file with cron.php at set intervals to update the database (will ask how to do this when I get there)What I’ve managed so far is to loop through the lot with the glob function, and adding the files, but my problem is updating the database. (cars removed from the csv files must not be deleted just set to sold, so we can keep that data for statistics)Hope someone can point me in the right direction.This is the code i’ve written so far:PS: there are no headings in the csv files.<?php$username ="root";$password = "myPasword";$host = "localhost";$table = "csv_table";$conn = new mysqli("$host", "$username", "$password");// echo "Connected to localhost" . "”;mysql_select_db(“csvdb”) or die(mysql_error());// echo “Connected to Database”;?><?php// Set variable for csv file path $dir = "dealer_upload/*/*.csv"; // Open a known directory, and proceed to read its contents foreach(glob($dir) as $file) { //echo "PATH AND FILENAME: " . $file . "”;// Create the array$fileTemp = $file;$fp = fopen($fileTemp,’r');$datas = array();while (($data = fgetcsv($fp)) !== FALSE){$stockNumber = trim($data[0]);$make = trim($data[1]);$model = trim($data[2]);$derivative = trim($data[3]);$series = trim($data[4]);$reg = trim($data[5]);$vin = trim($data[6]);$driveAwayPrice = trim($data[7]);$priceExcluding = trim($data[8]);$specialPrice = trim($data[9]);$year = trim($data[10]);$kilometres = trim($data[11]);$body = trim($data[12]);$colour = trim($data[13]);$engine = trim($data[14]);$transmission = trim($data[15]);$fuel = trim($data[16]);$options = trim($data[17]);$sellingPoints = trim($data[18]);$nvic = trim($data[19]);$redBook = trim($data[20]);// Insert Datamysql_query (“INSERT INTO $table (id_dealer, stockNumber, make, model, derivative, series, reg, vin, driveAwayPrice, priceExcluding, specialPrice, year, kilometres, body, colour, engine, transmission, fuel, options, sellingPoints, nvic, redBook)VALUES (‘$file’, ‘$stockNumber’, ‘$make’, ‘$model’, ‘$derivative’, ‘$series’, ‘$reg’, ‘$vin’, ‘$driveAwayPrice’, ‘$priceExcluding’ ,’$specialPrice’ , ‘$year’ , ‘$kilometres’ , ‘$body’, ‘$colour’, ‘$engine’, ‘$transmission’, ‘$fuel’, ‘$options’, ‘$sellingPoints’, ‘$nvic’, ‘$redBook’)”)or die (mysql_error());} } ?>