Translate

May 15, 2011

Import CSV file directly into MySQL

I got BIG csv containing thousands of records, and I need to put those in mysql table. Writing simple script would do the job, but I thought instead of writing a script to pull in information from a CSV file, I can link MYSQL directly to it and upload the information using the following SQL syntax.

Step 1:
Remove the CSV headers from the CSV file along with empty data.

Step 2:
Create a mysql table equivalent to the CSV headers.
CREATE TABLE inv_data ( id INT(11), description VARCHAR(100), 
            ...
             ...
      ); 

Step 3:
You can then import it into a MySQL table by running:
load data local infile 'inventory_data.csv' into table inv_data fields terminated by ','
enclosed by '"'
lines terminated by '\n

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.

NOTE: If you get error saying :
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Please start mysql using following command

mysql --local_infile=1 -u root -p 


No comments: