28 February 2005
Import Excel/CSV into MySQL
Posted by Mikhail Esteves under: LAMP; Tips .
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.
19 Comments so far...
Minh T. Says:
24 March 2005 at 11:01 am.
Simple and best solution for importing csv to MySQL. Good Job. Two thumbs up from me.
Ch3W Says:
30 May 2005 at 6:23 am.
Many thanks for the reference. Worked for me cheers!
chad french Says:
4 October 2005 at 2:23 am.
i love you!
Anselm Says:
8 February 2006 at 6:16 pm.
I have run into this problem, so thought I would leave a message here for others. If you get an error message such as :
ERROR 1148: The used command is not allowed with this MySQL version
It means that (for security reasons, generally) either your client, or the MySql server refuses to load files from local machines.
However, if your file is located on the same machine as the database server, you can run the same instruction without the ‘LOCAL’ keyword, as in :
LOAD DATA INFILE ‘uniq.csv’ INTO TABLE tblUniq
FIELDS TERMINATED BY ‘,’
(uniqName, uniqCity, uniqComments)
Sharjeel Says:
24 March 2006 at 2:03 am.
I had a value which contained comma. e.g “Redmond, WA”. Mysql treated this as a delimiter too thereby treating it as two values.
To prevent treating comma in value as delimiter add this parameter in the statement:
ENCLOSED BY “”“”
Joe Sieben Says:
20 April 2006 at 7:53 am.
Thank you, thank you, thank you!
It worked great. Sharjeel, your sugestion would work if I knew how to get the “ “” “ in the csv file. I cheated and did a find & replace commas to ^ in Excel, then saved as csv. Opened it up in good old note pad, removed the headers and did two find replaces in here (1: “,” to “|”, then 2: “^” to “,”). This way I could easily import it using the SQL by using the “|” delimeter instead.
May not be the most ellegent way, but it worked for me.
Thanks again.
Great Says:
3 August 2006 at 7:11 am.
Wow, this saved me SO MUCH TIME.
I was gonna write this in C, but forget about that now !
Thanks a bunch
Simon Says:
1 November 2006 at 2:27 am.
Spent ages trying to use the CSV import function in phpMyAdmin. Used this and imported the data in minutes.
Basics are always best! Cheers!
Rahul Says:
16 December 2006 at 10:37 am.
this worked great!!
thank you
Jaxo Says:
21 February 2007 at 8:01 pm.
Thats amazin! It worked brilliant! Thanks for that.
Harry Ng Says:
5 March 2007 at 11:22 am.
Great.
Amazing.
Brilliant
At the same time, I will use ‘\r\n’ instead of only ‘\n’, so the return character is removed also.
Thanks a lot.
Katy Says:
20 March 2007 at 3:36 pm.
Wow, thanks! Works like a charm with Query Browser and saves me having to buy expensive tools like Navicat.
Anonymous Says:
29 July 2007 at 9:54 am.
awesome, thanks a lot man.
Scott Says:
2 August 2007 at 8:22 am.
Very useful — thanks.
BTW, that “ enclosed by ‘”’ “ part just saved me cleaning up thousands of lines of code. Simple enough, and for a newb like me, a weekend saver.
Mudd Says:
15 August 2007 at 9:29 pm.
Superbbbb. U saved me from a sleepless night tonight. :)
Iggy Says:
24 January 2008 at 6:19 am.
May I know what keyboard character is: “”“” used by the ENCLOSED BY?
Thanks
miciisahisti Says:
28 March 2008 at 11:24 pm.
nice to use a simple command, instead of writing programs or using shell scripting or other scripts. Thank you!
Brotherlex Says:
2 May 2008 at 11:14 am.
Thank you. This works great! Here’s my asp variation:
tablename = “sqltablename”
fieldnamestring = “field1, field2, field3”
filenamepath = “C:\inetpub\wwwroot\filename.csv”
enclose1 = “”“”
enclose2 = “”“”“”“”“”
connect_site.execute “LOAD DATA LOCAL INFILE ‘” & replace(filenamepath,”\”,”/”) & “’ INTO TABLE “ & tablename & “ “ &_
“FIELDS TERMINATED BY ‘,’ “ &_
“ENCLOSED BY ‘” & enclose1 & “’ “ &_
“ENCLOSED BY “ & enclose2 & “ “ &_
“LINES TERMINATED BY ‘\n’ “ &_
“(” & fieldnamestring & “)”