How to import a csv file into mysql from the prompt
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.
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….
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 …..