Monday, October 19, 2009

Import Excel into MySql database....!!!!

Its easy and quick, thought to share it with you all....

Convert Excel into csv file with Save As.
Open the csv file in notepad/wordpad, see the delimeter (should be a comma).
Write the following command in the query browser
LOAD DATA LOCAL INFILE 'D:\\converted_csv.csv' INTO TABLE database.table FIELDS TERMINATED BY ',' ENCLOSED BY ‘”‘ LINES TERMINATED BY '\r\n' (table.field1, table.field2,table.field3...);

If you get any error, probably the single and double qoutes are not correct, sometimes the editor changes the quotes ASCII, better delete the quotes and write again...

TERMINATED BY is the delimeter you use "comma in our case"
LINES TERMINATED BY – new line character, if you do not use \r, the query will insert a new line character in the database.

Hope this helps....

Thanks
Sayed Azharuddin

No comments:

Post a Comment