Tool for importing CSV files into MySQL database?

Question

I've got several CSV files I want to import into a table. They all contain different numbers of columns, so some data will be absent during each import. I need a tool that will let me map which CSV column goes into which MySQL column. Doesn't look like MySQL workbench can do this. What else can I use?

Solution 1

MySQL has the LOAD DATA INFILE syntax:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

See the documentation about custom data handling.

Solution 2

If you're running on a Mac, Sequel Pro is pretty good for this sort of thing (as long as you import each CSV file individually).

http://www.sequelpro.com/

Comments

[mpen]

What if I want to omit a field in the CSV?

[Dan Grossman]

Read the manual page he already linked which has the syntax for mapping CSV fields to columns. It has examples.

[mpen]

@Dan: Sorry... it was half way down the page...have to use a dumbie value.