Import CSV file directly into MySQL

Contributed by katy8439

Tagged: 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.

80 Comments -

Rafael said on October 7, 2008

Thank you. Short and sweet. Very straight to the point.
Ng said on October 10, 2008

Thanks, buddy!
robert said on October 22, 2008

thanks pal! finally somethin’ working :)
ashikur said on October 26, 2008

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
Chris said on December 2, 2008

Thank you Thank you. Very nice.
priyadarshan said on December 23, 2008

very very superb 1 line query.. i love it !!!!!!!!!!!!!!
ir said on February 3, 2009

help!! can this be possible??
(idnum,username,md5(password))
how about if i used md5 in my password??
and i want my password field converted into md5.
email me pls. ir_bs756@yahoo.com
ashish said on February 5, 2009

hey
please send full code of importing file into mysql database. at my email (ashish@tiainterweb.com)
thanks
zild said on February 18, 2009

Can MySQL automatically assign the column names using the column headers from the csv file?
vasu said on April 14, 2009

where to place the csv file…
Partha Kar said on April 25, 2009

Thank you very much.
Dave said on May 9, 2009

@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 said on May 24, 2009

the above code is can do it. You need to specify the url of the csv file with / slash and not .
Okpali D Great said on May 24, 2009

thanks very much
Mindy said on June 29, 2009

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.
Mindy said on June 29, 2009

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 said on July 8, 2009

Thanks kaly…
jaky said on September 8, 2009

Thank you mate!
Sari said on September 11, 2009

Thank You!!! I have 7 MB CSV file successfully imported to Mysql in 1.6 seconds!
eqcc said on October 9, 2009

Thanks for the info! It worked perfectly! Thanks again!
Anonymous said on October 12, 2009

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 said on November 23, 2009

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

Ecalvillo.
Anonymous said on January 9, 2010

what if, we have two excel sheets in a excel file. How do we import ?
Anonymous said on January 20, 2010

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 said on January 25, 2010

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 said on January 25, 2010

where to place the csv file ?

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

thanks
Anonymous said on January 25, 2010

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 said on March 18, 2010

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)
steffen said on March 22, 2010

hi dood, this is wicked, can insert a URL to the file?
Anonymous said on April 5, 2010

where to place the csv file?
pari said on April 7, 2010

Awesome!!! u r great!! Tonnes of thanks.. I was desperately looking for this
Akilah said on April 9, 2010

What if your data of items that contain commas, i.e. text datatypes?
Anonymous said on April 13, 2010

Thanks so much for the code. You saved me a lot of time. Thanks again.
Anonymous said on April 13, 2010

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

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 said on May 4, 2010

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 said on May 27, 2010

VERY NICE!!! YES ALL CAPS!!! VERY NICE!!! THANK YOU!!!
GbMax78 said on June 5, 2010

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
webguest said on June 19, 2010

thanks for your comment of the path, i know where to put the file and make it work!
Gbmax78 said on June 24, 2010

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 said on July 5, 2010

how can i put date from csv to mysql?
celega said on July 22, 2010

How come when I used the code, only the first row is inserted into a mysql table? thanks!
Rob said on July 30, 2010

Ditto…very succinct. Thanks!
Fourteen_naveed said on August 12, 2010

Thanks it is working fine
Jason said on August 13, 2010

Thanks, just what i was looking for. But can you use a absolute path to point to the csv files?
Sibbald said on August 17, 2010

just did it using c:/file.csv

@katy8439 thanks, you saved me lot of hassle
Rajmahendran said on October 19, 2010

thank you its working. keep it up!!
rob said on October 24, 2010

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 said on October 24, 2010

Thanks for the additional information!
Bhavya said on November 2, 2010

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 said on December 9, 2010

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 said on December 22, 2010

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);
Jeyanthi said on December 30, 2010

hi,…
i am also unable to upload the date and time through this, but it works when you change the system time to the corresponding date format of your CSV data…

try it..

Jeyanthi, jeyanthi1983@gmail.com
karuna said on December 30, 2010

hi,
but i cant able to import dat time info… whats wrong i dont know.. can you tell if you know…
KG said on January 13, 2011

Hi, Is there a script that can help automatically import this file on a daily basis?
Steve said on January 28, 2011

Thank you for the path. This was the only unknown. Great post from all. Thanks!
Nikamvishvajeet said on February 18, 2011

Thanx for Information
atrichtc said on April 7, 2011

Excellent post. Very quick and readily launchable from PHP, unlike direct dump or messy scripts. Quite helpful.
Asd said on April 18, 2011

me too… :(
Scott said on April 19, 2011

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.
Munir hussain said on May 6, 2011

Thanks a lot
Vijayanand Gurudev said on May 6, 2011

@vgurudev Thanks a lot, saves lot of time.
Bob Jackman said on May 31, 2011

Hi. Thanks for this :)
Cinto Thomas said on June 28, 2011

Thanks saved my 2 hours………
Harminder singh said on June 30, 2011

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 said on July 16, 2011

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. :)
Iyap77 said on July 18, 2011

How can I “UPDATE” date not add new data? Can anybody help me?
Iyap77 said on July 18, 2011

How can I “UPDATE” date not add new data? Can anybody help me?
Iyap77 said on July 18, 2011

How can I “UPDATE” data not adding new data? Can anybody help me?
Jary Hernandez said on July 27, 2011

The same, happens to me. Do you know how can I solve this problems. Please if you can help me you can send me an email at jary.hernandez@gmail.com. Thanks!!
Jary Hernandez said on July 27, 2011

The same, happens to me. Do you know how can I solve this problems. Please if you can help me you can send me an email at jary.hernandez@gmail.com. Thanks!!
Mandeep said on August 5, 2011

$data = fgetcsv($filename,1000,’,')
Mandeep said on August 5, 2011

update `table_name` set `feild_name1` = ‘$variable1′ , `feild_name2` = ‘$variable2′;
Keira Heu-Jwyn Chang said on August 9, 2011

I was having the same problem and my line terminator ended up being ‘r’ but some others might be ‘rn’
Maan said on September 6, 2011

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

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!
Luis Reis said on November 9, 2011

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 said on December 5, 2011

Thank you. It is worked..
Shane said on January 4, 2012

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 said on January 10, 2012

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

Thanks
ravi

RSS feed for comments on this post. TrackBack URL

Import CSV file directly into 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.

You may also like -

80 Comments -

Leave a comment -

RECENT POPULAR RECIPES

TECH-RECIPES BLOGS