19

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?

kmas
  • 6,401
  • 13
  • 40
  • 62
janglosaxon
  • 207
  • 1
  • 2
  • 4

6 Answers6

12

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.

Ben English
  • 3,900
  • 2
  • 22
  • 32
  • 1
    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 Nov 19 '11 at 02:51
  • 3
    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 ''` – Ben English Nov 21 '11 at 04:52
  • For me it was the `\r`. Thanks for that. – Eric Brotto Jun 16 '13 at 08:30
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.

bananafish
  • 2,877
  • 20
  • 29
  • Thank you, bananafish. I know `LOAD DATA LOCAL INFILE` before, but [HeidiSQL](http://www.heidisql.com/) is a very nice option! – Emerson Rocha Aug 07 '13 at 15:39
3

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';

djdy
  • 6,779
  • 6
  • 38
  • 62
  • MySQL workbench looked like the easiest option for me..I'm open to any suggestions? – janglosaxon Nov 19 '11 at 02:53
  • 1
    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. – djdy Nov 19 '11 at 03:33
1

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.

Community
  • 1
  • 1
Pavel
  • 11
  • 1
0

This was convoluted for me, but here is what I did:

I had PHPmyAdmin installed, so I exported to CSV w/ , seperated and " as escape character. PHPmyAdmin couldn't handle the " as escape, so I find/replaced all instances of "" with \" and it worked. Suerte!

Andrew Samuelsen
  • 5,325
  • 9
  • 47
  • 69
0

Open the Excel file in Numbers iWork 9) and export to CSV. Import in MySQL with 'CSV with LOAD DATA' option works fine.

heimi
  • 499
  • 7
  • 16