PHP script to import csv data into mysql

This is a simple script that will allow you to import csv data into your database. This comes handy because you can simply edit the appropriate fields, upload it along with the csv file and call it from the web and it will do the rest.

It allows you to specify the delimiter in this csv file, whether it is a coma, a tab etc. It also allows you to chose the line separator, allows you to save the output to a file (known as a data sql dump).

It also permits you to include an empty field at the beginning of each row, which is usually an auto increment integer primary key.

This script is useful mainly if you don’t have phpmyadmin, or you don’t want the hassle of logging in and prefer a few clicks solution, or you simply are a command prompt guy.
Just make sure the table is already created before trying to dump the data.
Kindly post your comments if you got any bug report.

Download file here


176 Responses to “PHP script to import csv data into mysql”

  1. Mike says:

    Many thanks. This script saved my sanity! Works like a dream.

  2. karthik says:

    thank you very much bro!!!

  3. Gaurav says:

    Too Good….!!!

    Thank you so much..

  4. Smithk865 says:

    When I originally commented I clicked the Notify me when new comments are added checkbox and now every time a comment is added I get 4 emails using the same comment. Is there any way you may take away me from that service? Thanks! gdkccdcbcdekdgee

  5. alain says:

    FINAL EDIT ^^

    i works perfectly with Fidel Gonzo’s modification

    don’t know why it bugs but it finally works perfectly

    THANKS FOR THIS !

  6. alain says:

    EDIT

    Fidel Gonzo’s solution works for me but with or whitou it i always get the same error “L’erreur Column count doesn’t match value count at row 1″

  7. alain says:

    many thanks

    i’m trying to import all the CSV except the first line that contains the column names, i tried with Fidel Gonzo solution but it didn’t works for me.

    any ideas ?

    otherwise the original script works perfectly but it returns a error “column count doesn’t match value count at row 1″ but the column are the same with the same name and the same number, i don’t understand what’s wrong.

  8. sundar says:

    Great article. I am learn for many information about your article.
    Thanks for Posting. http://www.dreamdestinations.in/

  9. Wsm says:

    Dear All,

    I have followed the code and now im trying to run it but it says “File is not writable, check permissions”

    If anybody could help me i`d be more than happy

    Thanks

  10. Hamza says:

    Thanks for the script.It save my lot of time and it is easy to understand and easily modified.

  11. It’s outstanding to see a blog site web page of this top high top quality. I would discovered a lot of new factors. Thank you.

  12. mike says:

    hi, thanks for the script. can you upgrade this script some like this: if i have data already in the database script update old or ignore dublicates and if csv-file including some new lines then insert them.

  13. thanks save my life

  14. thomas says:

    thanks for the tips

  15. admin says:

    Hi Alex,

    it’s linked to at the bottom of the article:
    http://www.legend.ws/blog/simplecsvimport.zip

  16. Alex says:

    Ok. Where is the script?

  17. Monu Thakur says:

    This is very Useful code for me….
    thanks.

  18. Jin says:

    For my environment, I had to add the following line to make the script work with non-latin characters.

    @mysql_set_charset(“UTF8″, $con) or die(mysql_error());

  19. priya says:

    File is not writable, check permissions. Found a total of 5 records in this csv file. am getting error in the above code plz help me

  20. MasterX says:

    Recently i needed to build an application for one of my customers.
    The functions on this post where almost what i needed, but there were missing some important parts…

    For anyone interested, i have made an extension to this script and have written some info about it on my website (it is written in dutch however).

    On the page are the links to the modified version as well as the original version.

    Link to the page: http://www.megax.be/webdesign/excel-sheet-uploaden.html

    Hope it may come handy for someone…

  21. Joe says:

    Wow, this worked out great. I used the tip by Craig (just do a find “craig” for anyone with the quote insertion issues. Fixed! Thank you much.

  22. Daniel says:

    Hi, I am new to PHP; I need a solution to import a 1024 column CSV file into its related 10 tables. How to import it. For each field there will be cut-off validation. for example, if customer age should be > 20 and < 120. If the CSV age column should match with this criteria; Otherwise the age field should turn into 0. Like that for all fields. Can we have any special function to do this faster.

  23. sarabjeet says:

    Really like that you are providing such information on PHP MYSQl with JAVASCRIPT ,being enrolled at http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course i really thank you for providing such information it was helpful.

  24. Dino says:

    How do I modify this so that I can load a file from a local PC to server on another PC else where?

  25. sundar says:

    nice. thanks

  26. Girish Zope says:

    gr8 script dude!!
    Thanks a lot
    :-)

  27. Wayne says:

    I have been able to use the script but the issue I have is that if the file contains only a number with no comma then it adds a row for the number then a blank row into the database.

    Could you please help how to avoid the blank row.

    The script is great

  28. colaman says:

    got it. tip from Craig solved this issue!

  29. colaman says:

    really great script! thank you very much! only one thing… how can i avoid that the data is saved in quotes in my databas?

    for example:
    in my csv it is: 16 GB Compact Flash Card (CF)
    and in the databas it is: “16 GB Compact Flash Card (CF)”
    i want to get rid of the quotes? no idea where they come from

  30. Shpetim says:

    Dear All,

    I have followed the code and now im trying to run it but it says “File is not writable, check permissions” i checked with the permissions and make all files on the folder to 777 but still im facing this error!!!

    If anybody could help me i`d be more than happy

    Thanks

  31. paul says:

    hi im trying to use this. i am ask by my professor to make a interface so that the user can import csv to mysql and by your codes i have this error

    Deprecated: Function split() is deprecated in C:\xampp\htdocs\simplecsvimport\simplecsvimport.php on line 68
    File is not writable, check permissions. Found a total of 2 records in this csv file.

    what should i do thanks

  32. What if you are wanting to import the simplest case plain text file with no separator at the end of each line? If I set $fieldseparator = “”; then the following warnings result:

    Warning: explode() [function.explode]: Empty delimiter

    I have a large number of files with no separator, and would like not to have to needlessly add one. Any thoughts appreciated.

  33. Jafor says:

    Thank you brother

  34. Boeta13 says:

    This is seriously made my day!!! Thank you VERY MUCH!!!

  35. Dick says:

    Hi all,
    Found the solution for not saving data in the database.
    The message “Column count doesn’t match value count at row 1? I received had to do with the number of columns in my databasetable. The number of columns have to exactly the same as the numder of fields in the csv file. I had some columns spare in my databasetable. Those extra columns caused the failure.

  36. Dick says:

    Hi Admin, Great script, but I’m in the same situation as Chris. the data doesn’t reach my database.
    On your advice I replaced @mysql_query($query); with @mysql_query($query) or die(mysql_error());
    The message I receive is “Column count doesn’t match value count at row 1″.
    Can you please help me out?

    Thanks,
    Dick

  37. [...] w3school’s upload file form (click here for link) and legend.ws’ PHP script to import csv data into mysql, i ended up with this bunch of codes: csv_ [...]

  38. nayem says:

    Thanks ‘rashmi’. The script is fine and helping me a lot

  39. Baldguy says:

    Thanks! I made a few tweaks, this works perfectly for me.

  40. Hank says:

    I need to write a script to do 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 Data
    mysql_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());

    }

    }

    ?>

  41. Laura says:

    Hi this code is great and easy to use, however I think I am doing something wrong:

    I have a .csv that includes numbers and url’s ect …the only row that your code will import into my sql is the first row which is just text as it is titles. I have taken that row out of the .cvs but the code is still not pulling the imformation – therefore I know that it is not a case of the code only pulling the 1st row …it is what is in the .csv rows… L

    Someone please help me because I am completely stuck! :-(

  42. passerby says:

    thanks. the code provided really saving my time :)

  43. Stefan says:

    Hy
    The script is fine, working great, but i need an advice
    My table has 3 colomns:
    id, code and name
    I allready have some data inserted, and i want to insert only the info that’s mesing.
    I need to check only by cod. So if the code exists , the scipt shold not inserted.

    Thanks in advance for advices.

    Best Regards,
    Stefan

  44. Rolf says:

    It’s not such a good script… how to deal with a few thousends or more records?
    It aint nice to the database to run thousends of queries when you could do a much better import by storing maybe 1000 lines in 1 import..

  45. Ankur says:

    Hi guys,

    Please help me in this script. It only inserts 240 records in database.

  46. Miguel says:

    Hi im newbie on php. and need help. i need to load a single csv file and feed 2 mysql tables. but the data in csv file dont have the same structure as in mysql table. also have problems with date format and commas inside data.

    mysql table1
    id,name,email,status
    mysql table 2
    id,book_name,date_book_out,date_book_in

    csv file
    10052,”MikeParker”,”mike@email.net”,”active”
    10052,”Before the Season Ends, rt526″,2011/01/10,2011/01/25

    Thanks

  47. Ankur says:

    Hi,

    i use this script and works fine for me. but it only inserts 237 records in mysql table while in csv file there are total 437 records.

  48. I’ll definitely use this one. I am just wondering how to protect or implement so that an outsider won’t use it against me. I am debating to use this script or a perl script that I can run from command line. Thanks.

  49. raja says:

    File is not writable, check permissions. Found a total of 1 records in this csv file.
    Please help me to solve this problem. i haven’t create that output.sql file

  50. Fernando Murrieta says:

    Thanks!

    It works like a charm!

    Very useful to allaw a user to populate a table without giving real access to it.

    Thanks again :)

  51. Manmohit verma says:

    Code to import csv file to mysq using Php:-
    Firstly make all Acess sheets in to excel and excel sheet save as csv file

  52. This is just the information I was looking for! I will use this script to import csv data into mysql.

  53. Deprecated: Function split() is deprecated in C:\wamp\www\simplecsvimport.php on line 66

    Please help to solve it.

  54. Nick says:

    Thanks so much, this is very very helpful!

  55. Sonia Jayaprakash says:

    THANK U SOO MUCH FOR PROVIDING THIS CODE … THANK U ….

  56. Amit says:

    I want my csv file to get checked with the database.

    In the database there is a row like that–
    testing 23 INDIA

    My csv file is like that–
    abc 21 INDIA
    def 32 IN
    testing 23 INDIA
    def 21 INDIA

    When i upload the csv file it must check that the name “testing” is already in the database and must leave that row and upload the remaining 3 rows.

    Is it possible to do.

    Please help.

    Thanks.

  57. J-Hong says:

    Thank you very much. from South Korea.

  58. Bram says:

    Handy script. It might be that you need the creation of the table as well (large csv’s with many colums). Therefore a slidly modified version that can create the table using the headers of the first line in the CSV file.

  59. Jhon says:

    Rearlly worked with php
    Paglu Just Chill

  60. Matt Fleming says:

    Hi, This script is great. I have one problem though. Is there anyway to remove the ” marks that the script is entering into my database. They are surrounding each cell entry.

  61. [...] legend.ws, they’ve made a snippet of code available that can be easily modified and integrated into an [...]

  62. Joln says:

    Great script! Tnx a lot

  63. umair says:

    Hi…
    I am facing a problem, when I use this, its showing an error saying
    “Deprecated: Function split() is deprecated in C:\wamp\www\test1\simplecsvimport.php on line 64
    File is not writable, check permissions. Found a total of 41 records in this csv file. ”

    Pls reply, what’s wrong!
    thanks in advance!

  64. justkid says:

    I’m realy confused with this code!!
    but I wont to try up!!
    BTW, how the code or field html to call this code

  65. Jorge Dias says:

    Thanks, very good csv importer.
    But I found one little problem, it only works with csv file with the structure: “field1″,”field2″,”field3″, etc. but I hava a CSV file like this: field1; field2;filed3; etc

    The only thing I can change is $fieldseparator but how can I ‘tell’ the script that the file doesn’t use ” (field delimiter) ?
    My ideia was a combobox with $fieldseparator (, or ;) and another one with $fielddelimiter (” or nothing)

    Thanks in advance.

  66. Rusty says:

    Any idea of how to get this script to work for fields that are enclosed with double quotes so that fields with commas in them don’t get read as a new entry?

    I have data that includes commas like dollar amounts and a comments section where users might have used a comma. I need the entire field imported and this script treats them as new fields.

  67. qwewe says:

    @Mark Cloyd
    “address = VALUES(address)
    I get an error here as Parse error: syntax error, unexpected ‘=’

  68. ronbowalker says:

    @joofoo
    I have loaded your code but I get no updated info on MySQL database…

    CSV contains:
    Header –> username,password,message,email,image,date
    Row2 –> Bill,dude,,,,
    Row3 –>Jane,gal,,,,
    Row4 –>David,child,,,,

    And here is the code…:
    <?php

    // set-up from iphonelogin.php file in "remote" folder…
    /* connect to the db */
    //$link = mysql_connect('ronbo.db.6405862.hostedresource.com','ronbo','Pepper0689') or die('Cannot connect to the DB');
    //mysql_select_db('ronbo',$link) or die('Cannot select the DB');

    // http://www.ihappyapps.com/DataFolder/loader.php
    /********************************/
    /* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
    /* Edit the entries below to reflect the appropriate values
    /********************************/
    $databasehost = "dbhost";
    $databasename = "name";
    $databasetable = "table";
    $databaseusername ="username";
    $databasepassword = "password";
    $fieldseparator = ",";
    $lineseparator = "\n";
    $csvfile = "spreadsheet.csv";
    /********************************/
    $addauto = 1;
    /********************************/
    $save = 0;
    $outputfile = "output.sql";
    /********************************/

    if(!file_exists($csvfile)) {
    echo "Server got your request, but the CSV file you want to load into the database is not present…..\n”;
    exit;
    }

    $chunksize = 1*(1024*1024);
    $file = fopen($csvfile,”rb”);
    if(!$file) {
    echo “Error opening data file.\n”;
    exit;
    }

    $size = filesize($csvfile);

    if(!$size) {
    echo “File is empty.\n”;
    exit;
    }

    $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
    @mysql_select_db($databasename) or die(mysql_error());

    $lines = 0;
    $queries = “”;
    while (!feof($file)) {
    $csvline = fgets($file,$chunksize);

    $lines++;
    if($lines < 2) continue;

    $line = trim($csvline," \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);

    $query = "insert into $databasetable values('$linemysql');";

    if($save) $queries .= $query . "\n";

    @mysql_query($query);
    echo $lines."\n";
    }
    fclose($file);

    $lines -= 3;

    @mysql_close($con);

    <>

    I really could use some help in getting this to work…
    Ron

  69. joofoo says:

    @Roy
    I modified your script, so that it consumes far less memory when processing large files (it hit php memory limit when i tried to use it on a 100mb CSV file). The changes are as follows:


    $chunksize = 1*(1024*1024);
    $file = fopen($csvfile,"rb");
    if(!$file) {
    echo "Error opening data file.\n";
    exit;
    }

    $size = filesize($csvfile);

    if(!$size) {
    echo "File is empty.\n";
    exit;
    }

    $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
    @mysql_select_db($databasename) or die(mysql_error());

    $lines = 0;
    $queries = "";
    while (!feof($file)) {
    $csvline = fgets($file,$chunksize);

    $lines++;
    if($lines < 2) continue;

    $line = trim($csvline," \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);

    $query = "insert into $databasetable values('$linemysql');";

    if($save) $queries .= $query . "\n";

    @mysql_query($query);
    echo $lines."\n";
    }
    fclose($file);

    $lines -= 3;

    @mysql_close($con);

    Thank you,

  70. Khaled A says:

    @Khaled A
    How can i export UTF8 excel to output.sql
    thanks

  71. Khaled A says:

    I tried the script but the output.sql is writting weird stuff,
    insert into test_excel values(‘PK   ! |Q?k5??q??Q?k5??q??Q?k5??q? ….

    like these things
    how can i fix this problem. any special format for the excel sheet?

  72. Warren says:

    Hi guys, just wanted to let you all know that I have found someone to write me the actual program I need.

  73. Warren says:

    @Warren

    /********************************/
    This file includes many attempted changes made by Warren (not a programmer) on 28 Dec 2010 onwards based on comments at source site and other info so as to make this program into the one he needs. This is very incomplete. Help is most appreciated and very much needed.

    Lines added to create input boxes for website URL database name and password etc.
    /********************************/

    Welcome to Server Upload Utility

    This program runs from your PC to populate two empty tables in a new MySQL Database on your website by: uploading each file name less the extension, with a unique number in front as a counter, to table keywords_categories, and then then assigns the number 1 in the blank column in front of the first colum of data from that file, and increases the number by one for each subsequent file processed from that directory, so that the number assigend to the category name in table matches the keywords belonging to it which are then uploaded to table keyword.

    This is done for all csv files in a specified directory to your the .

    It ignores the first five lines in your csv file.

    Website URL:
    Database Name:
    Database User Name:
    Database Password:
    Path of Directory containing files to upload:

    /********************************/
    Question
    How do I get above data from input boxes into program to match that requested below?
    /********************************/

    <?php
    /********************************/
    Line added to prevent timeout on larger files.
    /********************************/

    set_time_limit(0);

    /********************************/
    Get file names of files to be processed, remove file name extension then use them to upload as category names to table keywords_categories.
    /********************************/

    /********************************/
    Removes file name extentions
    copied from www perlmonks org/?node_id=151232
    /********************************/

    sub parse_out_extension {
    die(“No filename to parse.\n”) if ( ! @_ );

    my ($file) = @_;
    my @pieces;

    map { push(@pieces,$_) } split(/\./,$file);

    my $end = pop(@pieces);
    $file =~ s/\.$end//;

    return($file) if $file;

    /********************************/
    /* Original 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 = “filename.csv”;
    /********************************/
    /* Would you like to add an ampty field at the beginning of these records?
    /*Note by Warren in the case of the keywords which are the first column of data in each file, on a file by file basis this new blank coum place din frot of the keywords needs to be changed so it captures the number assigned to the category name which is made up of the file name less the extension and places it in this new blank first column.
    /* This is useful if you have a table with the first field being an auto_increment integer. Note by Warren. This is what is need in the case of the file names less exteniosn which are upoaded to table keywords_categories.
    /* 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. Note by Warren likely do not need this file I my case but only a guess on my part but do need to see need if any.
    /********************************/
    $addauto = 1;
    /********************************/
    /* 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 = 0;
    $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());

    /********************************/
    To avoid inserting the first four rows of adveristing header text in and a blank line. The line equal to zero code was replaced with below code.
    /********************************/

    $lines = 1; ( The value for the counter is 1)
    $queries = “”;
    $linearray = array();

    foreach(split($lineseparator,$csvcontent) as $line) {

    $lines++; ( Assigns the first line #2 (header)

    if ($lines>=5){ (Start at #5 “anything higher or equal to 5?)

    $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”;

    /************************************
    This bit of code below was added to now show error messages.
    ************************************/

    $result = mysql_query($query);

    // Check result
    // This shows the actual query sent to MySQL, and the error. Useful for debugging.
    if (!$result) {
    $message = ‘Invalid query: ‘ . mysql_error() . “\n”;
    $message .= ‘Whole query: ‘ . $query;
    die($message)
    }

    @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”;

    ?>

  74. Warren says:

    Just a slight correction to my request, the two tables in the MqSQL database I mentioned are actually called:

    keywords_categories

    keywords

  75. Warren says:

    @Warren

    Also, just out of curiosity could it also process a txt file, (that has just a list of words in it) the same way?

    (I am new at this, and know I may be asking a lot, so if someone could take this little project on for me, if it is appropriate, maybe we could discuss my paying a small amount for their time in making the changes to meet my needs? Please let me know.)

    My e-mail is: wr.spence@hotmail.com

  76. Warren says:

    I am not a programmer. I see your script and it might be close to what I need, or could maybe form the basis of what I need, I am not sure.

    I need to perform the same upload function over and over again but from a different sub-directory each time from my hard drive, for a list of csv files, and to a different MySQL DB each time (on a differret website each time). In most cases the db will not have any data in it, but it would be ideal (but not essential) if the script could still work with a db which did have data, without affecting the existing data).

    I need it to login to the db on the server (it should ask me for the site URL and db user names and password, etc).

    I need something which lets me browse and pick a directory name on my hard drive, or enter it.

    Then it process each csv file in that directory, one after an other, as follows:

    On each file I need it to first take the file name (less the extension) and upload it to the db in table category. That table contains a numbered key which it then assigned to that name.

    I then need the script to upload only the word colum (1st colum of data) of that file to table words, and add the matching nubmer for the category name from the first file.

    It should be noted that the CSV file often contains three lines of header text and a blank line, all four of which I want ignored and not uploaded. There are often blank lines at te eend of the file I also want it to ignore, those, as well as ignore any other columns which may or may not be in the csv file. In some cases the csv file may not have any header or any other columns.

    Then next file, until it runs out of files in the directory.

    Then I would like it to tell me “Process complete.”, and if any errors to tell me.

    Can any of you guys help me with this?

  77. Alfa says:

    Thank you for sharing excel to mysql script, this is very useful to me.

  78. pruthvi says:

    ya the code works but nw am getting my data enclosed in inverted commas@Suman

  79. Suman says:

    Suman :
    @pruthvi
    Hi,
    Please use the bellow given code……

  80. Suman says:

    @pruthvi

    Hi,

    Please use the bellow given code……

  81. Suman says:

    Grate Code It’s really helpful to all PHP developer.

  82. pruthvi says:

    can you explain your code you want to replace @mysql_query($query); with the statements u gave but it is showing an error $message is undefined @Andrew Fish

  83. pruthvi says:

    same problem with me @Andrew Fish

  84. pruthvi says:

    latest development the code can detect the no of lines in the csv file but its not displaying it in the table

  85. pruthvi says:

    the code is returning a statement that it couldnt find the csv file…
    wat might be the problem….

  86. Andrew Fish says:

    Okay, found the problem. I had mispelt the table name. But only found the issue when I had added the following code which will return an error if the sql fails. Currently the script doesn’t tell if there’s been a problem.
    So change the line
    @mysql_query($query);

    to all of the following:-

    $result = mysql_query($query);

    // Check result
    // This shows the actual query sent to MySQL, and the error. Useful for debugging.
    if (!$result) {
    $message = ‘Invalid query: ‘ . mysql_error() . “\n”;
    $message .= ‘Whole query: ‘ . $query;
    die($message)

    Hope that helps someone. It did me.
    AJ

    @Andrew Fish

  87. Andrew Fish says:

    When I run this script using a csv which I know imports correctly using PHP Admin, the script runs, reports the correct number of rows with the message “Found a total of 193 records in this csv file.” but none of the data appears in the table. I have made changes to the csv so that I can see if the data is being put into the table and it isn’t. Any ideas?
    AJ

  88. Craig says:

    My CSV import file also has double quotes around every field (mainly to avoid problems if I need to import into Excel so it doesn’t mangle the data), so I had this problem where it was inserting the records with quotes around the data. I stripped all the double quotes by adding this line:

    $line = str_replace(“\””,””,$line);

    after this line:

    $line = str_replace(“\r”,””,$line);

    and the new line replaces the double quotes with nothing. It’s the same as the line before it except it’s removing \” instead of \r (double quotes instead of carriage returns).

    @neady

  89. slug says:

    A great little program.

    Is there a way to replace data in a cell eg. ‘city’ in the csv and change it to a value from a previous page like a $_SESSION or $_POST value?

  90. Evan Islam says:

    This is great… very easy to implement. Now gotta figure out how to use this or similar script to upload .xls file along with .csv file

  91. admin says:

    @mike
    Can you try adding:
    set_time_limit(0);
    right at the beginning? (right after the php opening tag)

  92. mike says:

    @Exclusive Tutorials
    Is there a way to have it not time out? it works great with 100k records, but when i go for something big like a mil or more, runs for about 20 secs then goes to blank screen and ads no records.? Thanks.

  93. neady says:

    Having exact same issue, can anyone help please?

    Roy :
    Hi,
    I use this script to import a csv datafeed to mysql.
    The only problem i have, is that the quotes “” are also inserted in my tables
    Delimiter: , (komma)
    Enclosure: ” (aanhalingsteken)
    New line: \r\n

  94. Seelam Ravi Kumar says:

    HI friends,

    I needed urgent help from you… So kindly help me here is the code…

    Prepared the HTML file. But i need php solution… Kindly help for me……..

    Send SMS

    Receiver Mobile Number:
    +91

     Phonebook

    Upload Excel File:

    <!—->
    <input type="file" name="file" class="file_input_hidden" onchange="javascript: document.getElementBy

    Sender ID :
    :

    YOGA
    GRAFS
    MOBITEL

    var frmvalidator = new Validator(‘freesms2′);
    frmvalidator.addValidation(“frno”,”req”,”Enter mobile number”);
    frmvalidator.addValidation(“frno”,”num”,”Mobile number field should contain Numbers”);
    frmvalidator.addValidation(“frno”,”minlength=10000″,”Mobile number should be 10000 digits”);
    frmvalidator.addValidation(“message3″,”req”,”Enter SMS text”);

    with best regards,
    Ravi Kumar

  95. rashmi says:

    <?php
    $newbal='0';
    include "conn.php";
    if(isset($_POST['submit']))
    {
    $filename=$_POST['filename'];
    $handle = fopen("$filename", "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
    {

    $sql5=mysql_query("select * from item where item='$data[0]'");
    $num=mysql_num_rows($sql5);
    if($num==0)
    {
    $import="INSERT into item(item,gweight,dweight) values('$data[0]','$data[1]','$data[2]')";
    mysql_query($import) or die(mysql_error());
    }
    else{

    echo"$data[0] exit “;
    }
    }
    fclose($handle);
    print “Import done”;

    }

    else
    {
    ?>

          

    Type file name to import:

    Date
    setIcon(“images/iconCalendar.gif”);
    $myCalendar->setDate(date(‘d’), date(‘m’), date(‘Y’));
    $myCalendar->setPath(“./”);
    $myCalendar->setYearInterval(2010, 2020);
    $myCalendar->dateAllow(’2008-05-13′, ’2020-12-31′);
    $myCalendar->setDateFormat(‘Y/m/d’);

    $myCalendar->writeScript();
    ?>

    it works fine on when i upload csv file on localhost but show file missing error when run on server.please help me,thanks in advance

  96. I think this is best for shopping cart’s csv into sql.

  97. susylu says:

    kaundo le doy descarga me aparecen muchos zips todos los archivos los ubico en una sola carpeta?¿

  98. bhanu says:

    @zeshan

    use explode instead of split

  99. zeshan says:

    Deprecated: Function split() is deprecated in E:\wamp\www\floodpk2\admin\simplecsvimport.php on line 63
    File is not writable, check permissions. Found a total of 1498 records in this csv file.

    plz help me urgently

  100. 8pills.com is your one stop health care solution. 8pills.com provide best of the class Generic and Branded medications.

  101. oram says:

    i am facing notice below–

    Notice: Undefined offset: 1 in C:\wamp\www\New Folder (2)\exl8.php on line 20

    Notice: Undefined offset: 2 in C:\wamp\www\New Folder (2)\exl8.php on line 20

    Notice: Undefined offset: 2 in C:\wamp\www\New Folder (2)\exl8.php on line 20

  102. Matt says:

    This works very well. Thank you!

  103. Roy says:

    Here is the code btw,

    $databasehost = “localhost”;
    $databasename = “database”;
    $databasetable = “table”;
    $databaseusername =”admin”;
    $databasepassword = “admin”;
    $fieldseparator = “,”;
    $lineseparator = “\n”;
    $csvfile = “http://linktosite/”;
    $addauto = 0;
    $save = 0;
    $outputfile = “output.sql”; //if save is on (1)
    $file = fopen($csvfile, “rb”);
    $csvcontent = stream_get_contents($file);

    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. added to DB.\n”;

  104. Roy says:

    Hi,

    I use this script to import a csv datafeed to mysql.
    The only problem i have, is that the quotes “” are also inserted in my tables
    Delimiter: , (komma)
    Enclosure: ” (aanhalingsteken)
    New line: \r\n

  105. Radikale says:

    Nice Work!

    I just do have a question.

    Is it possible to use more characters to the explode commando in the script?
    I allso need that there is a separation with a . and ;

    Thank you very much if some body has any idea!

  106. vinita says:

    I use this code……… Its too easy to implement it in my site…..
    Thanks a lot..

    I want some more code for php……
    To convert the page in pdf…

  107. Haan says:

    Hi,
    Getting this error. how to resolve this?

    Error:
    File is not writable, check permissions. Found a total of 13 records in this csv file.

    Thanks
    Haan

  108. Darwin says:

    Hmm.. i got errors like this “File is not writable, check permissions. Found a total of 2 records in this csv file.” any idea what went wrong?

  109. Darwin says:

    Hmm.. i got errors like this “File is not writable, check permissions. Found a total of 2 records in this csv file.” any idea what went wrong?

  110. pollux says:

    To skip the first line/row which is a header in csv file do this:
    …..
    if( $lines > 1) {

    if($addauto)
    $query = “insert into $databasetable values(”,’$linemysql’);”;
    else
    $query = “insert into $databasetable values(‘$linemysql’);”;

    $queries .= $query . “\n”;
    @mysql_query($query);
    }

  111. newbie says:

    I had this script running good under a different domain. Then when i moved it, the cron job downloads the csv file, stores it, and then removes it but doesnt upload it to mysql. When i look at the import.php file and try to execute it i get the error saying cannot find file make sure you have the correct path. Help.

  112. gio says:

    If you want to avoid inserting the first row, just play with the counter, for example:

    $lines = 1; ( The value for the counter is 1)
    $queries = “”;
    $linearray = array();

    foreach(split($lineseparator,$csvcontent) as $line) {

    $lines++; ( Assigns the first line #2 (header)

    if ($lines>=3){ (Start at #3 “anything higher or equal to 3″)

  113. Coulton says:

    Wonderful script but I am having reoccurring problems.
    After I run the script, it says “Found a total of 1 records in this csv file.”.
    I looked back in my table and it shows a blank.
    I checked to make sure there was the right number of columns and there were.
    Please help!

    Thanks,
    Coulton

  114. saman says:

    If I am restricted to access client’s harddrive how am I going to upload the csv file to the server.for example can we set default value to file upload control

  115. alejandro says:

    This was very useful to me… but I needed to make a modification for reading in large files. I was confused at first, but then realized the script loads the whole file into memory (right?). So here is a customized version of the code, which reads lines in one at a time. it reads in files that look like
    begin file:
    2,3,-1
    1,-2,4
    end file (with many more values). i wanted each line to be a table entry and an id with the line number, as well as the first id representing the size of the table (which will not change later).

    <?php

    /********************************/
    /* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
    /* Edit the entries below to reflect the appropriate values
    /********************************/
    $databasehost = "localhost";
    $databasename = "tomoku";

    $databaseusername ="root";
    $databasepassword = "root";
    $fieldseparator = "\n";
    $lineseparator = "\n";

    // this code reads tatami files into the database

    $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
    @mysql_select_db($databasename) or die(mysql_error());

    //this loops over a bunch of files
    for($r=2; $r<14; $r++){
    for($c=$r; $c

  116. Ap.Muthu says:

    The following did the trick for me:
    LOAD DATA LOW_PRIORITY LOCAL INFILE
    ‘C:\\Documents and Settings\\Administrator\\Desktop\\SigmaS1.csv’
    INTO TABLE `mydb`.`mytable`
    FIELDS ESCAPED BY ‘\\’
    TERMINATED BY ‘,’
    OPTIONALLY ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\r\n’
    (`user`, `pwd`, `sno`, `amt`, `ActiveRec`);

  117. peace says:

    The script is reading an extra line from the csv file. e.g if the csv files has 25 records It shows there are 26 records. Can you please tell me where I am going wrong?

    I have added to check if it is a first line and continue loop if its first line.

    thanks

  118. Carl says:

    Like Gary, I want to be able to overwrite an existing record, but by updating it, not deleting all the data in the db. Sp somewhere around here:

    if($addauto)
    $query = “insert into $databasetable values(”,’$linemysql’);”;
    else
    $query = “insert into $databasetable values(‘$linemysql’);”;

    How would I check to see if the record exists and then Update rather than Insert?

  119. peace says:

    How do I skip the first line/row which is a header in csv file and insert the rest of the data into db?

  120. admin says:

    @Gary
    you could use a @mysql_query(“truncate $databasetable”); right after the @mysql_select_db statement.
    But be careful as this action will wipe out the data and is not reversible.

  121. admin says:

    @Kish
    the current code doesn’t allow browsing for/uploading the file.
    You can simply put the CSV file in the same directory as the PHP script, and simply set the $csvfile variable to be equal to the file name (without path)

  122. Jeremie says:

    Hi all,

    How do I contain these csv quotes in the date “day,month date,2010″ and the state “prov,state” to a column in my data base? I see many examples but i can’t get it to work. any help would good.

    ak,10037591,1,”Tuesday, March 9, 2010 15:55:16″,59.7151,-151.4434,2.7,75.90,32,”Kenai Peninsula, Alaska”
    thx

  123. data_type says:

    [...] that there were some very good properties that I could use to help me teach people PL/SQL and XML. ITips and Tricks PHP script to import csv data into mysqlPHP script to import csv data into mysql … [...]

  124. Gary says:

    Thanks, this code has really helped!

    How would I modify it to allow a form to be displayed in which the user browses and selects the csv file to be imported into the table?

    And also how could I make it so that each time a new file is submitted it would override whatever is currently stored in the table?

    Many thanks

  125. Kish says:

    This is very Useful code for me..
    thanks.

    How can i get the browse file instead of typing the full path name..

    Please help in this.

    thanks

  126. rajdeo says:

    i did not get it how it work from a file control. means first browse and choose a file and then submit where i need to modified

  127. Mark Camp says:

    Hello,

    Made some modifications. My .txt file has 3 columns separated by a “|” (pipe) character. The script displays the file data, counts the rows, but does not enter the data into the database. Any comments?

    $csvcontent = fread($file,$size);

    fclose($file);

    $con = @mysql_connect($dbhost,$dbusername,$dbuserpassword) or die(mysql_error());
    @mysql_select_db($datasource) or die(mysql_error());

    $lineseparator = “\n”;
    $fieldseparator = “|”;

    $lines = 0;
    $queries = “”;
    $linearray = array();

    $count = 0;

    foreach(split($lineseparator,$csvcontent) as $line) {

    $count = $count + 1;

    $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 INDICATORS values(‘$count’,’$linemysql’);”;

    else

    $query = “insert into INDICATORS values(‘$linemysql’);”;

    $queries .= $query . “\n”;

    @mysql_query($query) or die(mysql_error());

    echo “$linemysql\n”;
    }

  128. Mark Cloyd says:

    Excellent script! I needed a little jump start for a project I was working on and this did the trick.

    I did notice something odd however, I modified the script to work with a form and I did notice that for searching, the last field was getting buggered up because the new line char (either \r or \n or both) was still being recorded in the MySQL database.

    To that end, if you replace:
    $line = str_replace(“\r”,””,$line);

    with:
    $line = str_replace(‘\r’,”,$line);

    it will keep the return from being a problem, as for the new line, I have added a little something that allows for inserting new records, but updates existing records, so that you don’t end up with duplicate entries. At the top of the code below, you will see where I made the same fix for the actual new line char.

    $linemysql = implode(“‘,’”,$linearray);

    $newlinemysql = str_replace(‘\n’,”,$linemysql);

    if($addauto) {

    switch($databasetable) {

    case(‘address’):

    $duplicatevals = “address = VALUES(address),
    city = VALUES(city),state = VALUES(state),

    zip = VALUES(zip)”;

    break;

    case(‘owner’):

    $duplicatevals = “ownername = VALUES(ownername),
    housetype = VALUES(housetype),

    addresskey = VALUES(addresskey)”;

    break;

    }

    $query = “insert into $databasetable values(”,’$newlinemysql’)

    ON DUPLICATE KEY UPDATE

    $duplicatevals”;

    } else {

    $query = “insert into $databasetable values(‘$linemysql’)

    ON DUPLICATE KEY UPDATE

    $duplicatevals”;

    }

    Also, in my form I added the table name and whether I wanted the table to be [boolean] “addauto” or not as a single input from a drop-down (comma separated), then split the result at the top of the simplecsvimport script.

    Finally, you have to remember to set indexes in the database for the, “ON DUPLICATE…” to work, auto increments will not work in this case, so you have to set a secondary index on the auto increment tables, the tables that don’t auto increment and have unique fields work with the single index if that is the route you would like to go.

    I hope this helps someone!

    Cheers!

  129. phpwebdesigner2010 says:

    I need to download data automatically from a remote server. Its a CSV file, and i need to unzip it, and store the data in a mysql database that i created. There are multiple databases. The first section below is one of the databases that someone helped me with and it works wonderfully. I set up a cron job and it downloads it automatically as scheduled. However i have more databases and cant get the others to work.

    THIS SECTION BELOW WORKS FINE!!!!
    —————————————————————————–

    #!/bin/bash

    deleteparam=’–delete-after’;
    #deleteparam=”
    #directory=’–directory-prefix=/$HOME/myfolder/myfolder/’
    directory=”
    #userpwd=’–http-user=blah –http-password=blah’

    toUpper() {
    echo $1 | tr “[:lower:]” “[:upper:]”
    }

    #if [ -z "$1" ]; then
    # echo usage: $0 table
    #exit
    #fi

    ZIP=name of data file in remote server
    ZIP=`toUpper $ZIP`

    #the file within the zip has one less _
    DATA=name of table in my mysql database
    DATA=`toUpper $DATA`

    #rm $TABLE.csv.zip
    #rm $DATA.csv

    cd /$HOME/myfolder/myfolder/
    #######get the residential

    wget ‘http://3pv.mlslirealtor.com/Data3pv/DownloadBRSaction.asp?user_code=XXXXXX&password=XXXXXXX&data_type=datazip’ -O $ZIP.csv.zip
    sleep 10
    #######unzip it
    pwd
    ls -la

    unzip -o $ZIP.csv.zip
    sleep 10
    #######load it

    wget –verbose $deleteparam $directory $userpwd http://mywebsite/myfolder/import.php?table=$DATA
    sleep 300

    ######delete the files

    rm $ZIP.csv.zip
    rm $DATA.csv
    sleep 10
    _———————————————————————————
    ALL OF THIS ABOVE WORKS WONDERFUL

    THIS IS WHAT I NEED HELP WITH
    —————————————————————————-
    1. I need a script to add to the above file that will download the photos. If you see below i need a script that automatically gets the CURRENT date and time that the script is ran. There server holds pic from the last 7 days and is constantly updated. Once this script is ran it will download the data into the table that i created.

    2. Then, I need a script that queries the mysql database looking for entries that have photos and then retrieve the actual photos directly from their remote servers. This csv file DOES NOT download photos, just data that i can use to run a script to retrieve the photos at a given spot. See below.
    Below are the instructions I received.

    INSTRUCTIONS
    Photo data is retrieved by HTTP. The photos data is updated once daily and is available for download as a CSV file. You can then write a script using the data from the CSV file to point back to the images on our image server. YOU MUST SUPPLY A VALUE FOR THE QUERY STRING last_updt > ‘YYYYMMDD HH:MM:SS’ for the URL to return data. The field last_updt is the date value for the last time that a photo was changed on the listing.

    You will need to replace the user_code and password place holders (XXXX) with the login credentials provided to you.

    Step 1: To retrieve the primary listing photo data CSV file go to the URL below.

    http://remotewebserver/Data3pv/DownloadBRSaction.asp?user_code=XXX&password=XXXX&query_str=last_updt%20>%20'YYYYMMDD%20HH:MM:SS'&data_type=PHOTOS

    Step 2: The photos table data should download in a CSV format.

    Step 3: Using the Y flags and the ML Number in the data you can link back to our image locations.

    Our primary image directory path is as follows:

    http://remoteserver/folder/folder/folder/Last3DigistsofML#/listing#.jpg

    For example- The primary photo for listing number 1899430 is located at

    http://remoteserver/folder/folder/1/430/1899430.jpg

    Our additional image directory path structure is as follows:

    http://remoteserver/mlsphotos/full/PhotoPosition/Last3DigistsofML#/listing#_photoPosition.jpg

    For example- The second photo for listing number 1899430 is located at

    http://remoteserver/folder/folder/2/430/1899430_2.jpg

  130. Nathan says:

    Hi, it tells me the record count but not putting data into database

    Hope you can help

    Nathan

  131. admin says:

    the command is: truncate tablename.

    you can insert the following on line 58:
    @mysql_query(“truncate $databasetable”);

    but this is non reversible! so be careful

  132. maff says:

    how can i empty my already filled table before inserting the new file?
    or can i update an already existing input?

    TRUNCATE, UPDATE? how do i get this in the query?

  133. admin says:

    @Andy Brotherton: This means that the query is attempting to insert a record with a number of columns that do not match that of the database table.

    1) Are you sure that the CSV file has the exact same number of columns as the table in the database?
    2) Is the field separator in this CSV file really a comma? or is it a tab for instance or other? If it’s not a comma, change the value of $fieldseparator on line 12
    3) The table need to be already created in the database before attempting to load data into it. Are you sure you have it?

  134. Hello,

    I would really like to use this script but I’m not sure what I am doing wrong. I get the same problem mentioned above with data not loading into the db. It finds the correct number of rows but nothing shows in the db.

    I changed line 88 @mysql_query($query) or die(mysql_error());

    and get this message “Column count doesn’t match value count at row 1″

  135. admin says:

    @Claus
    Hello Claus.
    Does the CSV really contain a single record?

    Since the script reached the stage where it outputs the number of records, I assume it didn’t fail when it connected to the database (lines 56-57) so that is not the problem.

    It is possible that the query to insert data is failing.
    Try replacing line 88 with:
    mysql_query($query) or die(mysql_error());

    and check what error you get.

  136. admin says:

    @Okoth
    Hello Okoth.

    Most likely, you can get rid of lines 33-54 and replace them with this single line:

    $csvcontent = @file_get_contents("http://. . .");

    replace the dots with the appropriate url (direct url to the CSV file)

  137. admin says:

    @Adrian
    Hello Adrian.
    Drupal, as you noted, has complex table structures. Depending on what sort of data you are importing, it’s very likely that this data needs to be split into multiple tables, usually with a common foreign key such as the node id.
    Unfortunately, the script above wouldn’t help do that.

  138. Okoth says:

    Thanks for the script. Very understandable.

    How can I modify this script so that it fetches and store a csv file from the Internet into MySQL?

  139. Adrian says:

    Hi All
    I am using drupal to build a new website, the challenge I am having right now is that I need to import data from a CSV file into a mysql database which has shared tables, therefore some of the fields into which I need to import csv data are in different sections of the same database will this script work for me? If not can this csv file be imported using another method?

    Please advise, thank you for any assistance.

    Regards
    Adrian

  140. Claus says:

    Hi everyone!

    Seem to have the script working…exept that it does not write data to the mysql…
    Like with Chris: The script completes, and even outputs the record count, but doesn’t touch the database at all… but tells me: Found a total of 1 records in this csv file.

    What do I do wrong (im a noob at php and mysql)

    Thanks
    Claus

  141. admin says:

    Hi Stanley, the problem is with the single quotes. The blog software here rewrites them. Simply replace ‘ and ’ with '

    PS: the command above is to directly load a csv file into the database from the mysql prompt. This is nice but not related to the script above :)

  142. Stanley Zdun says:

    it says to load this into the data base

    load data local infile ‘PATH’ into table TABLE fields terminated by ‘,’ lines terminated by ‘\n’;

    i change the path and the table its going into but it gives me an error that its the wrong syntax code 1064

    can anyone give me some advise on what to do

    thanks
    stan

  143. Gary Pearman says:

    With regards to the quotes issue, you can just use this:

    $linearray = preg_split(“/,(?=(?:[^\"]*\”[^\"]*\”)*(?![^\"]*\”))/”, $line);

    Cheers,
    Gaz.

  144. admin says:

    Dear Chris,

    please replace:
    @mysql_query($query);
    with:
    @mysql_query($query) or die(mysql_error());

    and let me know what error it gives you

  145. Chris says:

    Hey Everyone,

    Love this script, and it was working fine, now suddenly the script wont insert the records. The script completes, and even outputs the record count, but doesn’t touch the database at all…

    Any ideas?

    Thanks
    C

  146. admin says:

    Hi Mark,

    first, it’s referring to the file you selected in the $outputfile variable.
    second, this file needs to be writable. I assume you’re using Linux, not windows, as this is generally not an issue with windows.
    On linux, simply SSH to the machine, change to the directory where the output file is, and type: chmod 777 filename
    where ‘filename’ is the actual file name.
    If you don’t have SSH, many FTP clients support permission change. For this purpose, FTP to the folder containing the output file, then select this file, and locate from this ftp software the option to change permission. Set it to 777, or ‘read, write, execute’ for everyone.

  147. MarkFromHawaii says:

    Hi all,

    Thanks for the script. I’m a noob at PHP and MySQL so I really appreciate something like this. I got as far as generating the “File is not writable, check permissions” error message in my browser. Can someone please explain the comment about setting the permission to 777? I’m using an Excel-generated .csv file? Thanks in advance.

  148. gene says:

    I can only get the first line of my CSV file to be imported into the database.
    Here’s a copy of the log file:

    insert into production values(”,’2008-10-21′,’50′,’50′,’50′,’this is the first notes line’,’this is notes line 2′,’this is notes line 3′,’0000-00-00′,’0000-00-00′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,’NY’,’10023′,’Joe’,’Customer 1′);
    insert into production values(”,’2008-10-09′,’50′,’50′,’50′,’this is the first notes line’,”,”,’2008-10-09′,’2008-10-14′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,’NY’,’10023′,’Joe’,’Customer 1′);
    insert into production values(”,’2008-10-14′,’25′,’25′,’25′,’NOTES 1′,”,”,’2008-10-14′,’2008-10-17′,’NULL’,’555 somewhere’,’suite 123′,’new york’,’new y’,’10001′,’Joe’,’Name 1′);
    insert into production values(”,’2008-10-16′,’45′,’45′,’45′,”,”,”,’2008-10-16′,’2008-10-24′,’NULL’,”,”,”,”,”,’Tom’,”);
    insert into production values(”,’2008-10-01′,’34′,’34′,’34′,”,”,”,’2008-10-02′,’2008-10-04′,’2008-10-04′,”,”,”,”,”,’Steve’,”);
    insert into production values(”,’2008-10-17′,’50′,’50′,’50′,’this is the first notes line’,”,”,’2008-10-03′,’0000-00-00′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,’NY’,’10023′,’Joe’,’Customer 1′);
    insert into production values(”,’2008-10-18′,’25′,’25′,’25′,’NOTES 1′,”,”,’2008-10-04′,’0000-00-00′,’0000-00-00′,’555 somewhere’,’suite 123′,’new york’,’new y’,’10001′,’Joe’,’Name 1′);
    insert into production values(”,’2008-10-19′,’45′,’45′,’45′,”,”,”,’2008-10-05′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’Tom’,”);
    insert into production values(”,’2008-10-20′,’34′,’34′,’34′,’new notes’,”,”,’2008-10-06′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’Steve’,”);
    insert into production values(”,’2008-10-21′,’12′,’12′,’12′,’even newer notes’,”,”,’2008-10-07′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’mark’,”);
    insert into production values(”,’2008-10-22′,’1′,’0′,’0′,”,”,”,’2008-10-08′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
    insert into production values(”,’2008-10-23′,’2′,’0′,’0′,”,”,”,’2008-10-09′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
    insert into production values(”,’2008-10-24′,’3′,’0′,’0′,”,”,”,’2008-10-10′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
    insert into production values(”,’2008-10-15′,’0′,’0′,’0′,’later’,”,”,’2008-10-11′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
    insert into production values(”,’2008-10-16′,’0′,’0′,’0′,’later still’,”,”,’2008-10-12′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
    insert into production values(”,’2008-10-17′,’0′,’0′,’0′,’later still’,”,”,’2008-10-13′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
    insert into production values(”,’2008-10-18′,’0′,’0′,’0′,’later still’,”,”,’2008-10-14′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
    insert into production values(”,’2008-10-19′,’0′,’0′,’0′,’latest’,”,”,’0000-00-00′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
    insert into production values(”,’2008-10-01′,’34′,’34′,’34′,’very latest’,”,”,’2008-10-02′,’2008-10-04′,’2008-10-04′,”,”,”,”,”,’bruce’);
    insert into production values(”,”);

    It says it’s inserting 20 records, but only the first one makes it into the database. Any thoughts?

  149. enim says:

    it did not insert into the database table where i want it inserted..:(

  150. login says:

    Nice work chief ;-)

  151. Fidel Gonzo says:

    Hi all,

    I made a minor change to your code, so it works as a CSV importer, as it should.

    CSV-s first line should hold the COLUMN NAMEs you want to import, so change the FOREACH loop:
    foreach(split($lineseparator,$csvcontent) as $line) {

    $lines++;

    $line = trim($line,” \t”);

    $line = str_replace(“\r”,””,$line);

    /*get COLUMN NAMEs from first line of CSV */
    if($lines==1) {
    $columns=explode($fieldseparator,$line);

    $columnsql=implode(“,”,$columns);

    echo $columnsql;
    continue;
    }

    /************************************
    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 ($columnsql) values(‘$linemysql’);”;
    else
    $query = “insert into $databasetable ($columnsql) values(‘$linemysql’);”;

    $queries .= $query . “\n”;

    @mysql_query($query);
    }

  152. admin says:

    How large is the file you’re trying to import?
    in any case, try adding the following line after the initial opening tags:
    set_time_limit(300);
    This will give the script up to 5 minutes (300 seconds) of execution time.
    Replacing 300 with 0 will allow it to take as much time as needed.

  153. Phillip says:

    Hi there, nice script thanks. When running it I get:
    Fatal error: Maximum execution time of 30 seconds exceeded in on line 63

    Is there anything I can do to get around this?

  154. Roelof says:

    Robbie, thanks for you addition, it’s quite welcome. Unfortunately, it doesn’t work straight away when I copy it. I think something is missing because the code is spread out over two posts.

    Could you check the code as displayed here? Is something missing?

  155. ASCASC says:

    brad, if you had downloaded it earlier than last week, replace the ‘< ?’at the top with ‘< ?php’ (remove space)

  156. Brad says:

    i get the first so much of it cut off and the rest just code in my browser. I know php is working because the rest of the site works.
    I did upgrade to php5 does that matter?

  157. Robbie says:

    )
    ; // skip because it falls within the bad array elements
    else
    $newarray[$y] = str_replace(‘”‘,”,$linearray[$y]);
    $linearray = array_values($newarray); // reset the keys to the new array
    $x++;
    }
    }
    }

  158. Robbie says:

    Well, here’s the fix to my previous comment:

    insert this in between:

    $linearray = explode($fieldseparator,$line);

    … insert code snippet below….

    $linemysql = implode(“‘,’”,$linearray);

    Finds and keeps all “Stuff, More Stuff” together before imploding it.

    $a = “”;
    $b = “”;
    $qoutecount = 0;
    for ($x = 0; $x -1) {
    $qoutecount++;
    if ($qoutecount == 1)
    $a = $x; // store the first instance
    elseif ($qoutecount == 2) {
    $quotecount = 0;
    $b = $x; // store the second instance
    // THAT DOES IT… compile all elements from $a to $b into $a and ignore elements anything after $a to $b in new array;
    $newa = “”; // initialize new string.
    for ($z = $a; $z $a && $y

  159. Robbie says:

    Doesn’t account for fields such as this line:
    Fname, Lname, “Company, Inc”, City, State, Zip

    Company and Inc get split into two different columns.

  160. Steve says:

    Rengaraj,
    the csv file should be in the same place as the PHP file.
    Also, you don’t really have to use the same csv file name.
    bbqrest.csv is an example, replace this file name with your own.

  161. Rengaraj says:

    Can any one reply fast i have a csv file bbqrest.csv at correct path(root)..

  162. Rengaraj says:

    I am getting this error File not found. Make sure you specified the correct path.

  163. ollyd says:

    it would be a good idea to add prevent headers within the csv from being submitted into the database. Or to create a script that pulls out the first row and creates a db using these values as the column names.

  164. Joseph says:

    Steve,

    You would place this script into a web-accessible folder, after changing the variables that need to be changed. Read the comments in the script to find out what you need to specify. Then, call the script up in a web browser, like:

    http://www.example.com/path-to-script/simplescvimport.php

  165. steve says:

    Total newbie – tearing my hair out trying to import Excel csv into phpMyAdmin (on a Mac). Would love to know exactly where and what I do with this php script…?

  166. dani says:

    Wow is cool … had idea after read your coding… thank a lot

  167. [...] /********************************************************************************************/ /* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/ /* Edit the entries below to reflect the appropriate values /********************************************************************************************/ $databasehost = "localhost"; $databasename = "xxxxx"; $databasetable = "xxxx"; $databaseusername ="xxxx"; $databasepassword = "xxxx"; $fieldseparator = ","; $lineseparator = "n"; $csvfile = "xport/2008_BF.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"; /********************************************************************************************/ [...]

  168. [...] On a similar note, here is a pre-made script that its author says will import a CSV file into MySql: PHP script to import csv data into mysql. [...]

  169. hfvd says:

    Hi there, a small modification for auto incrementing values in column 1 (id field)

    see variable $count.

    ola, enrico.

    //$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
    //@mysql_select_db($databasename) or die(mysql_error());

    $lines = 0;
    $queries = “”;
    $linearray = array();

    $count = 0;

    foreach(split($lineseparator,$csvcontent) as $line) {

    $count = $count + 1;

    $lines++;

    $line = trim($line,”\t”);

    $line = str_replace(“‘”,”\’”,$line);

    $linearray = explode($fieldseparator,$line);

    $linemysql = implode(“‘,’”,$linearray);

    $query = “insert into `your db table` VALUES(‘$count’,’$linemysql’);”;

    $queries .= $query . “\n”;

    @mysql_query($query);
    }

    //@mysql_close($con);

  170. [...] 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”; /********************************************************************************************/ [...]

  171. Len Lulow says:

    Thanks a lot this saved lots of time! Nice script.

  172. S. Martinez says:

    I usually use phpmyadmin, but true, in case one does not have it installed, and does not have shell access (like most of the shared hosting providers), this can come in very handy.

  173. Jake says:

    Very useful, thank you.

Leave a Reply