Simple and best solution for importing csv to MySQL. Good Job. Two thumbs up from me.

Many thanks for the reference. Worked for me cheers!

i love you!

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)

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 “”“”

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.

Wow, this saved me SO MUCH TIME.

I was gonna write this in C, but forget about that now !

Thanks a bunch

Spent ages trying to use the CSV import function in phpMyAdmin. Used this and imported the data in minutes.

Basics are always best! Cheers!

this worked great!!
thank you

Thats amazin! It worked brilliant! Thanks for that.

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.

Wow, thanks! Works like a charm with Query Browser and saves me having to buy expensive tools like Navicat.

awesome, thanks a lot man.

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.

Superbbbb. U saved me from a sleepless night tonight. :)

May I know what keyboard character is: “”“” used by the ENCLOSED BY?

Thanks

@Iggy: Its a double quote ( “ ).

nice to use a simple command, instead of writing programs or using shell scripting or other scripts. Thank you!

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 & “)”

Bear in mind that your carriage return might be different than \n. For example, on Mac OS X, it’s \r instead.

…ah but where does the csv file go? …..

Error 2: (HY000) File ‘uniq.csv’ not found (Errcode: 2)

Thanks… this is showing a lot a of promise :D

I couldn’t get any of the below to work, even when I put the file in the default database directory (which was not the database I was working on)

adding the csv file to the C: drive and changing ‘uniq.csv’ to “C:/uniq.csv” worked just as well. :)

Thanks! I have been looking for a solution like this one for a half day now. Works wonders!

[Source: http://dev.mysql.com/doc/refman/5.4/en/load-data.html

“The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:
If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

If the file name is an absolute path name, the server uses it as given.

If the file name is a relative path name with one or more leading components, the server searches for the file relative to the server’s data directory.

If a file name with no leading components is given, the server looks for the file in the database directory of the default database.

Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server’s data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database:

LOAD DATA INFILE ‘data.txt’ INTO TABLE db2.my_table;

Windows path names are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them. “

….. for rest of this —- see the URL at the top of this post…