How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE

mysql csv
Ugesh Gali picture Ugesh Gali · Nov 17, 2010 · Viewed 116k times · Source

I have a CSV file which contains 10 columns. I want to select only some columns from that file and load them into a MySQL database using the LOAD DATA INFILE command.

Answer

ArK picture ArK · Nov 17, 2010

Load data into a table in MySQL and specify columns:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

@col1,2,3,4 are variables to hold the csv file columns (assume 4 ) name,id are table columns.

Related questions

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | …

Read More
How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column …

Read More
MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD …

Read More