Loading CSV files into MySQL Workbench

Question

I have a lot of excel/ CSV files that I need to load into my db in MySQL Workbench (I'm on Mac OS X). I've searched around for a good walk-through or tutorial, but I haven't seen anything that clearly explains how to load CSVs into MySQL Workbench....can anyone help?

Solution 1

are you trying to load csv files into a MySQL table? You can do that easily with the LOAD DATA LOCAL INFILE command.

Example:

LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','

You should be able to enter that command from any interface to MySQL. I'm assuming workbench has a way for you to execute sql queries.

Solution 2

Does it have to be Workbench?

Can you use other MySQL bins?

Here's an example: Create database

Create table

load data local infile '/path/whatever.csv' into table dbName.tableName fields terminated by ',' enclosed by '"' lines terminated by '\n';

Solution 3

For reference, I wanted to do this on Windows and used HeidiSQL to accomplish the task. It was very straightforward, I used Tools -> Import CSV file and it worked on the first try.

Solution 4

Or you could use Excel to generate a lot of LOAD DATA INFILE statements using a list of files to load.

Say, you could:

1. place the names of files in A column of Excel

2. place a formula like this in B1 cell:

   ="LOAD DATA LOCAL INFILE 'path/"&A1&"' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"

3. copy & paste B1 cell in B column cells where needed.

So, you would have LOAD DATA statemens in B column of Excel and could just copy&paste them into mysql command line.

Maybe it would not be too convinient to power user of scripting, but could make sense for strong user of MS Excel.

View more solutions

Comments

[janglosaxon]

Thanks! That statement worked but it's only loading one row at a time...any experienced help from workbench would be appreciated but I'll give it a shot

[janglosaxon]

MySQL workbench looked like the easiest option for me..I'm open to any suggestions?

[djdy]

If you are looking for GUI, then phpMyAdmin can do CSV imports. On the import screen, you can specify the format of the file and define termination characters.

[Ben English]

it may have to do with the line separator on the file you're trying to import. Try adding either of the following to your query LINES TERMINATED BY '\r\n' or LINES TERMINATED BY '\r'. If you know the line separator character just add LINES TERMINATED BY '<character>'

[Eric Brotto]

For me it was the \r. Thanks for that.

[Emerson Rocha]

Thank you, bananafish. I know LOAD DATA LOCAL INFILE before, but HeidiSQL is a very nice option!