Grow your CSS skills. Land your dream job.

Last updated on:

Import CSV into MySQL

<?php

$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.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";

?>

Reference URL

Comments

  1. Jake
    Permalink to comment#

    The only change I would recommend is using PDO instead the MySQL functions.

    • Permalink to comment#

      First thing that crossed my mind when reading this.

      These days it’s pretty much not-done to use mysql_connect … There are plenty of solid OOP solutions out there. Especially a site like this should give her users a good example with these snippets :)

  2. Permalink to comment#

    Function split() is deprecated – replace it with explode()

  3. skytrip
    Permalink to comment#

    Why not using file() in combination with str_getcsv()? I think it is more simple than fopen(). What is your opinion?

    • Permalink to comment#

      Depending where you got your CSV, CSV has an odd syntax where some fields may not be surrounded in double quotes and some may (depending on if your field has a space, another separator (comma), etc), and it escapes double quotes inside of double quotes by preceding it with another double quote, etc. Yes, CSV can get complicated, and I don’t think this snippet handles all of this.

      All of that to say, I agree with skytrip’s general consensus: there are a number of getcsv()-type functions in PHP, and they handle all that function stuff for you, and the possibility that it may or may not be that way.

      It’s also highly recommending when you export CSV, that you use the the putcsv() flavor rather than trying to do it all yourself.

      There’s fgetcsv function that takes a file handle, your delimiters and spits out a two dimensional array, having handled all of the tricky CSV syntax gotchas.

      Also, an even better solution might to just use something like Adminer (1 file on your server to work on the database) and use the Upload CSV file. If it’s too large to upload, you can put it on the server next to the Adminer file and it can import from there.

  4. Gareth
    Permalink to comment#

    I work for a company that uses daily csv feeds from over 50 providers. Any reason why you would use this solution over LOAD DATA INFILE?

    With 200k records in a csv, this snippet would execute 200k queries. Whereas LOAD DATA INFILE would use one.

  5. Devin Dombrowski
    Permalink to comment#

    Not trying to be a troll but there a lot of issues/asumptions with this snippet. Some are listed above. For a quick a dirty solution it should work though.

    Thanks for sharing.

  6. I agree that this will do in a pinch. For anyone that may be looking for a more polished solution but don’t want to use one of the many DEAD csv libraries out there, i made my own library this year and just open sourced it. It’s called Coseva and you can find it on Github.

    I hope someone finds it useful. :)

  7. Maharshi
    Permalink to comment#

    this code gives me error like “File is not writable, check permissions. Found a total of 8 records in this csv file. “… any soluiton ..

  8. Rob
    Permalink to comment#

    Outstanding tutorial. Spent the day looking for the ability to insert a CSV file into a MySQL database with PHP… and BOOM! Here it is on my favorite CSS site. Great job! (and also some good suggestions from the other people) Overall, this is a GREAT snippet to use. Thank you!

Leave a Comment

Current day month ye@r *

*May or may not contain any actual "CSS" or "Tricks".