For the follows out there who do not know how to import coma separated data into mysql from the prompt, here it is:
Assume:
PATH is the path to the csv file (can be relative to the directory you were in before logging into mysql)
TABLE is the table name that you have already created and whos fields match the csv file fields.
USERNAME is the mysql user that have access to insert data into TABLE
DATABASE is the database containing that table
- login to mysql by typing: mysql -u USERNAME -p DATABASE
The prompt will ask you for the user password, type it.
- Execute the following query:
load data local infile ‘PATH’ into table TABLE fields terminated by ‘,’ lines terminated by ‘\n’;
And voila.
Thanks for posting!
I have recently been searching for information about this topic for ages and yours is the best I have discovered so far.
Well, very good post with informative information.
Wonderful site and I wanted to post a note to let you know,
Thanks a lot for your guidance and sharing information.
Yaz? için te?ekkür ederim.
pruthvi, where is the filename.csv located? for instance, if it’s in c:\somefolder\filename.csv, then you need to escape the backslash, and hence use:
load data local infile ‘c:\\somefolder\\filename.csv’ into table …..
the program displays an error “File not found. Make sure you specified the correct path.”
how should i write the file name like filename.csv is ok….