Import CSV file directly into MySQL

Posted by katy8439 in MySQL

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.


To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile 'uniq.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

The Conversation

Follow the reactions below and share your own thoughts.

  • Rafael

    Thank you. Short and sweet. Very straight to the point.

  • http://thengamer.com Ng

    Thanks, buddy!

  • robert

    thanks pal! finally somethin’ working :)

  • ashikur

    Thank you!!! it really works !!!

    Copied the csv file into /var/lib/mysql/mydb at my ubuntu box.

    *mydb is the name of my database

    • webguest

      thanks for your comment of the path, i know where to put the file and make it work!

      • rob

        I went to the directory where I placed the csv files, placed the above code into its own file “create.sql” and then launched mysql client ie “mysql -u root -p”. Next I made sure I selected the db ie “use someDB;” then issued the command “. create.sql” and the data got there. Works well.

        • Anonymous

          Thanks for the additional information!

    • Steve

      Thank you for the path. This was the only unknown. Great post from all. Thanks!

  • Chris

    Thank you Thank you. Very nice.

  • priyadarshan

    very very superb 1 line query.. i love it !!!!!!!!!!!!!!

  • ir
  • ashish

    hey
    please send full code of importing file into mysql database. at my email ([email protected])
    thanks

    • Okpali D Great

      the above code is can do it. You need to specify the url of the csv file with / slash and not .

  • zild

    Can MySQL automatically assign the column names using the column headers from the csv file?

  • vasu

    where to place the csv file…

  • Partha Kar

    Thank you very much.

  • http://www.ladadadada.net/ Dave

    @ir:
    If the passwords in your CSV file are not already MD5() then just import them as per the instructions above then run this query:

    UPDATE `users` SET `password` = MD5(`password`);

    @ashish:
    That is the “full code”. There is no more. The only step you have to do before this is to create a table that matches the CSV file you already have. No one can tell you how to do that without having your CSV file. Don’t forget to modify the query above to match the table you have just created.

    @zlid:
    No it can’t. Fields are given names and types at the same time when the table is created. To assign the names automatically, MySQL would have to also assign the column types automatically which would mean scanning down through the entire CSV file to make sure that there were no letters in a column that started with numbers and finding the longest string in a column that started with a string and so on. There are too many options in table creation to allow it to be left up to software. NULL or NOT NULL ? DATE, DATETIME, TIME, CHAR, VARCHAR or TEXT ? What if you have a column full of numbers but one Excel error message that looks like “#value” ?

    You will have to create the table yourself before importing the CSV file.

  • Okpali D Great

    thanks very much

  • http://www.saltedwebsites.com/ Mindy

    Ok, I must be missing something here. Are there more explicit instructions for this? Do I paste this code using phpmyadmin and putting it into the sql box? I’m a little lost here.

    • http://www.saltedwebsites.com/ Mindy

      I wanted to add the fact that I already have my database set up in MySQL with all the appropriate tables. I created a test CSV file and messed around with it a bit but wasn’t sure where and how I was supposed to implement it using the code you have given.

  • Gopi

    Thanks kaly…

  • jaky

    Thank you mate!

  • Sari

    Thank You!!! I have 7 MB CSV file successfully imported to Mysql in 1.6 seconds!

  • eqcc

    Thanks for the info! It worked perfectly! Thanks again!

  • Anonymous

    Thanks, it is so important. But, I have 5 columns, it doesn’t complet, it left the last column. It emty.Could you help me.

  • Anonymous

    Thank you very much for this excelent recomendation, it works good.

    Ecalvillo.

  • Anonymous

    what if, we have two excel sheets in a excel file. How do we import ?

  • Anonymous

    works fine. thanks a lot!!! do you know how can I schedule the sql statement once per day? i want to execute it every day at 3pm for example… thanx

  • Anonymous

    yes nice but i get an error message:

    File ‘omg.csv’ not found (Errcode: 2)

    Where to locate my csv file? in which directory?

    any help

    thanks

  • Anonymous

    where to place the csv file ?

    Couse i get an error message
    File ‘omg.csv’ not found (Errcode: 2)

    thanks

    • Anonymous

      saplamaev, I put the csv file into the same directory as my database. See if you have a ProgramData directory just below your Programs directory, probably on C drive. Inside that I have a MySQL directory. Keep digging until you find your database directory. Hope that helps. I also removed the word ‘local’ from the script. I have other problems. It keeps telling me fields are too long. :-(

      • sheraz

        you can use absolute paths which works fine for me
        e.g.
        load data local infile ‘/this/is/where/my/file/is/uniq.csv’ into table tblUniq
        fields terminated by ‘,’
        enclosed by ‘”‘
        lines terminated by ‘n’
        (uniqName, uniqCity, uniqComments)

  • http://toodamnfresh.com/ steffen

    hi dood, this is wicked, can insert a URL to the file?

  • Anonymous

    where to place the csv file?

  • pari

    Awesome!!! u r great!! Tonnes of thanks.. I was desperately looking for this

  • Akilah

    What if your data of items that contain commas, i.e. text datatypes?

    • Anonymous

      for each data item that has a comma, you would enclose that with quotes ‘ ” ” ‘ like the code suggests “enclosed by ‘ ” ‘ “.

  • Anonymous

    Thanks so much for the code. You saved me a lot of time. Thanks again.

  • Anonymous

    suppose I have database with field employee and address I want to import data like
    yogesh,”line 1,main street”
    vishal,”market street, main bazzar”

    How should I write query to import these field without double quote ["] for address field

  • Leonardo Rebolledo

    Example In windows XP:

    load data local infile ‘D:\Leonardo\correo\listaCorreos\todos\listaCorreos.csv’ into table listaCorreos
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘rn’
    (correo)

    Bye and may God bless us and may the Blessed Virgin Mary intercede for us, sinners.

  • Rob

    VERY NICE!!! YES ALL CAPS!!! VERY NICE!!! THANK YOU!!!

  • GbMax78

    I create a table on my MySQL 5.1 on Debian Squeeze:
    mysql> CREATE TABLE reference
    (
    id INT NOT NULL,
    name VARCHAR(40),
    surname VARCHAR(30),
    city VARCHAR(20)
    );
    After when I import the *.csv file, on id column I have all 0 and on the name column I have the surname, I don’t know how i can start the import file from name column and not from id column.

    Best regards.
    GbMax78

    • Gbmax78

      Sorry for my newbie post, i have solve the “problem” reading the example:

      lines terminated by ‘n’
      (uniqName, uniqCity, uniqComments)

      I have replace uniqName etc. by column2, column3 etc. and the import process start not on column1 (id) but on column2 !

      GbMax78

  • Mcheali

    how can i put date from csv to mysql?

  • celega

    How come when I used the code, only the first row is inserted into a mysql table? thanks!

    • Asd

      me too… :(

  • Rob

    Ditto…very succinct. Thanks!

  • Fourteen_naveed

    Thanks it is working fine

  • http://www.topillusions.com Jason

    Thanks, just what i was looking for. But can you use a absolute path to point to the csv files?

    • Sibbald

      just did it using c:/file.csv

      @katy8439 thanks, you saved me lot of hassle

  • Rajmahendran

    thank you its working. keep it up!!

  • Bhavya

    if i want to select only a particular column from my csv file and insert into mysql database.. which syntax i have to use to get it without making remaining columns of the table as null values

    • Vish

      I am using the same query . it excute properly but csv data is not being inserted into the table. Instead of the csv data a garbage data is being saved into table.

      please any body can tell me why it been happening and how to resolve it.

  • Okaatech

    Excellent, for phpadmin users in Windows, remember to comment out the back slashes and use relative path and it works perfectly. Here is mine

    load data local infile ‘C:\xampp\mysql\data\eweek\alfa.csv’ into table alabama
    fields terminated by ‘,’
    lines terminated by ‘n’
    (abcear,abcde,abcde,xyz,123xyzl, abcdef);

    • karuna

      hi,
      but i cant able to import dat time info… whats wrong i dont know.. can you tell if you know…

  • KG

    Hi, Is there a script that can help automatically import this file on a daily basis?

    • Scott

      If you are in a Linux OS you can use a cron job (/etc/crontab) to run a php script or something similar. In Windows you will have to create a scheduled task and run a batch script or something similar.

  • Nikamvishvajeet

    Thanx for Information

  • atrichtc

    Excellent post. Very quick and readily launchable from PHP, unlike direct dump or messy scripts. Quite helpful.

  • Munir hussain

    Thanks a lot

  • Vijayanand Gurudev

    @vgurudev Thanks a lot, saves lot of time.

  • Bob Jackman

    Hi. Thanks for this :)

  • Cinto Thomas

    Thanks  saved my 2 hours………

  • Harminder singh

    Hi, I am importing a .csv file to mysql and it imports 1 to 133 records but then gives this error “Invalid field count in CSV input on line 133.” . What should i do.

    Thanks in advance
    Harminder Singh

  • Pseudo003

    HI., 

    Simple to do…… write the script as below:

    load data local infile ‘D:/DB Scripts/test.csv’ into table scr_component_risks
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘n’
    (give here all the columns for which you’re importing the field values separated by comma);

    use above and ur done….. 
    Nice. :)

    • varun

      > i want to load data from csv file from any directory using load datalocal infile .
      how it work pls tell

  • Iyap77

    How can I “UPDATE” date not add new data? Can anybody help me?

  • Iyap77

    How can I “UPDATE” date not add new data? Can anybody help me?

  • Iyap77

    How can I “UPDATE” data not adding new data? Can anybody help me?

  • Jary Hernandez
    • Keira Heu-Jwyn Chang

      I was having the same problem and my line terminator ended up being ‘r’ but some others might be ‘rn’

  • Jary Hernandez
  • Mandeep

    $data = fgetcsv($filename,1000,’,’)

  • Mandeep

    update `table_name` set `feild_name1` = ‘$variable1′ , `feild_name2` = ‘$variable2′;

  • Maan

    load data local infile ‘uniq.csv’ into table tblUniq
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\n’
    (uniqName, uniqCity, uniqComments)

  • http://www.invisionequity.com Mark

    This was so helpful. If your on a Mac and your using the MySQL Workbench. Here is what I placed in my Query:
    load data local infile ‘/Users/MarkDickinson/Documents/Saddleback Resources/RKW Database/RKW_weeklys.csv’ into table rkw2011.weeklys
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\n’
    (weekly_id, date, ip_number, title, series_title, author, 057_cd, 056_tape, 049_dat, 003_transcript, 008_outline, 059_note, 060_tio, 027_s_master_wav_audio, 012_mp3, 041_vhs, 042_vhs, 046_beta, 058_pro_res, podcasts, media_center)

    Best of luck!

  • http://luisreis.org Luis Reis

    Hello

    I got one problem when I import data with special characters
    I got one txt file coded in windows(ansi) and when do the import with this command, gives errors where the data have special characters,

    Some like “Ílhavo” gives this error

    Incorrect string value: ‘\xD6lhavo…’ for column ‘DESIG’ at row 5

    and do not import this record

  • Parthiban

    Thank you. It is worked..

  • Shane

    Thanks a lot for that post. I’ve used this method successfully offline. Now I’m trying to take my offline database and put it on my hosted server. When I run that coding on the server and reference it to the CSV on my computer, it obviously can’t access it. More troubling is that even if I upload the CSV file onto the server, MySQL still can’t access it:

    LOAD DATA INFILE “.db_upload/shane.csv”
    INTO TABLE new_table
    FIELDS TERMINATED BY “,”
    OPTIONALLY ENCLOSED BY “”””
    LINES TERMINATED BY “\r\n”

    Any ideas on how to get an offline database onto a hosted server, by this method or any other?

    Thanks!

  • ravi

    Thank you very much buddy, in my case… working perfectly …

    Thanks
    ravi

  • Kostas

    it transfers only the columns with numbers and not text columns. Any idea?

  • Sehaj Mathur

    Thankyou very much. Working PERFECTLY!
    Really helped it alot. Thanks!

    Sehaj Mathur

  • http://zeinertphoto.com Patrick Zeinert

    Uh, DAMN!

    I always come up against this issue and I have used all manner of convoluted php code to figure this out. Most of the time I just quickly need to populate a DB for a client with their existing data and this is perfect.

    BOOKMARKED!

    **Also, I don’t usually use caps and exclamation points, but you have no idea how happy this makes me.

  • http://zeinertphoto.com Patrick Zeinert

    Shane,

    ” I’ve used this method successfully offline.”

    Export the database as an .sql file and import it on your server. I know this works in PHPMyAdmin

  • David Kirk

    @Patrick – Thanks for the input. I’m glad it helped!

  • php-mysql-newbie

    Thank you very much. I’m really appreciate. It’s very helpful.

  • haripriya

    Thanks :)

  • Manoj

    Tnx.. Great help

  • amit

    Hello when i use LOAD DATA LOCAL INFILE command it works fine but inserted data in database is in some other format.

    wat to do?
    Please tell me
    Thankyou in advance

  • Roger Flores

    Thank you very much! It was a great help!!!

  • jatinder

    in my case this works like this (in phpmyadmin browser) :

    load data local infile ‘http://localhost/onlinetest/hello.csv’ into table tab_hello fields terminated by ‘,’ lines terminated by ‘\n’ (Name, Age)

    thanks..

  • Yawar Abbas

    Perfect solution. I was able to import csv file with more than 4000 records just in half a second. Thank you very much.

  • anand dhage

    Thnx!!!!!!!!
    Simple and sweet………………………

  • Danish Ali

    is there any limit on file size or number of rows being inserted?
    I have large CSV files, it contains 13,000 Records, but this query is inserting only 2400 records

  • pooja

    hey this helped me…..i have problem……i am able to import data from .csv file into mysql table…..but if i made some changes in .csv file and fire same query.. its not updating into table……..plz help me out……
    You can send me an email at [email protected]. Thanks!!

  • raja

    i am used xampp server..
    i want insert the data to mysql database from the MS-excel files..
    i hv refer the more sites..
    but i dnt gt the any..
    jst help me..

  • Mahesh Katariya

    Thanks friend this code really helpful.

    Thanks again……

  • manoj

    Really Thanks for valuable information

  • Anup

    What is the CSV file has URL in it as the data?

    Few lines from the CSV for example.

    “ID”,”HealthInc”,”HealthSource”
    1,”http://hh.abcdef.com/category/fitness/cardio/”,”http://www.uvwxyz.com/sports/fitness-equipment”
    2,”http://hh.abcdef.com/category/fitness/diet/”,”http://www.uvwxyz.com/nutrition/dietary-supplements”
    3,”http://hh.abcdef.com/category/fitness/exercise/”,”http://www.uvwxyz.com/sports/fitness-equipment”
    4,”http://hh.abcdef.com/category/fitness/gymming/”,”http://www.uvwxyz.com/sports/fitness-equipment”

    I tried importing but I the fields for URL is blank.

    output:
    +—-+————-+——————+
    | id | healthinc | healthsource |
    +—-+————-+——————+
    | 0 | | |
    | 1 | | |
    | 2 | | |
    | 3 | | |
    | 4 | | |

    I feel slash in the URL caused the issue. Any suggestion on how to get this working ?

    • Anup

      > I aswered my own question…
      You need to escape the quote i.e. enclosed by ‘”‘ –> enclosed by ‘\”‘

  • KHS

    May I know where to implant your code to?

  • ramasamy

    You have to give the full path of the CSV file. EX: C:/wamp/www/SITE/x.csv

  • Andrea

    Thank you, i was wasting my time to import a csv throw a servlet. It’ s so easy!!

  • Christopher Thomas

    Thanks! I just used this and it worked first time!!

  • hans

    Thank you for your article is very nice

  • tim

    Is this a live link to the CSV file or does it have to be refreshed every time an update is made? Will any queries made include the data in the CSV?

  • pfuj

    Man! Been trying to do that whole day. I’ve just gotta say one big THANK YOU!

  • Mudi

    my problem is this my db import data in differenct shapes and words…………why is this???????????

    load data local infile ‘D:\HOSTEL LISTS FROM DEC,12.csv’ into table excel
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\n’
    (id,name,father)

  • Nick

    I found that the documentation on MySQL’s page helpful in conjunction with this. dev.mysql.com/doc/refman/5.0/en/load-data.html. I was beating my head against getting this to work on my standalone server. Removed the LOCAL keyword and it worked like a charm.

  • renu

    really very much help full…. thank you…

  • Nat

    Thank you, great job!

  • Sharafudheenc

    Very good. Thanks

  • http://ozsuper.com IanF

    Here is the windows format…

    load data local infile
    ‘C:/Users/person/Documents/folder/file.csv’
    into table mytable_name
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\n’
    (col1, col2, `col 3`, `col 4`)

    This is a major time saver, so just passing on the favour for others.. IanF.

  • jushcare

    So swit and easy…. Tanx Man

  • Edward Mwangile

    Thanks.You really save me my day.

  • Amitabh

    Hi,
    I was searching for a method for the same.But, I want to export excel data to a table of mysql database using some script or code ,not by the command in console. I am developing a web app in jsp.and I need to do it using some code.Can you help me.

  • Etim

    what if the columns are not following, i mean like there are some columns that i want to leave empty as their data is not included, is there a way i can go about this?

  • vikas

    thanks alot dude…. u tried alot……

  • vikas

    thanks dude…. i tried alot….. u helped me….

  • mahesh