How to import a csv file into MySQL workbench?

Question

I have a CSV file. It contain 1.4 million rows of data, so I am not able to open that csv file in Excel because its limit is about 1 million rows.

Therefore, I want to import this file in MySQL workbench. This csv file contains columns like

"Service Area Code","Phone Numbers","Preferences","Opstype","Phone Type"

I am trying to create a table in MySQL workbench named as "dummy" containing columns like

ServiceAreaCodes,PhoneNumbers,Preferences,Opstyp,PhoneTyp.

The CSV file is named model.csv. My code in workbench is like this:

LOAD DATA LOCAL INFILE 'model.csv' INTO TABLE test.dummy FIELDS TERMINATED BY ',' lines terminated by '\n';

but I am getting an error like model.CSV file not found

You can do it also in dbForge Studio for [MySQL import wizard](https://www.devart.com/dbforge/mysql/studio/data-export-import.html). It automatically generates a table in such way http://prnt.sc/e5iqy1 — Devart, Sep 11 '18 at 10:08

score 145 · Accepted Answer · edited Nov 29 '17 at 04:39

145

I guess you're missing the ENCLOSED BY clause

LOAD DATA LOCAL INFILE '/path/to/your/csv/file/model.csv'
INTO TABLE test.dummy FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';

And specify the csv file full path

Load Data Infile - MySQL documentation

edited Nov 29 '17 at 04:39

AbsoluteƵERØ

7,816
2
24
35

answered Jul 11 '12 at 09:52

Packet Tracer

3,884
4
26
36

1

thank you, am not getting any error in this command.but am get error like model.csv file not found. that file is store in my system desktop – vps Jul 11 '12 at 09:59
csv file is stored in my system desktop – vps Jul 11 '12 at 10:02
check the answer again, you should place the file inside mysql data directory, better though specify the whole path – Packet Tracer Jul 11 '12 at 10:05
5

UPDATE: If importing strings with non-standard chars, it is useful to point out the need of "CHARACTER SET utf8" to the statement: LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE db_name.table_name CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' lines terminated by '\n'; The target table/columns should also be set to utf-8 – ılǝ May 02 '13 at 09:06
1

also one shouldn't forget the keyword LOCAL, your query is just right – Brij Raj Singh - MSFT Sep 27 '13 at 07:31
2

Worked "kind-of" on windows 7 using mysql workbench 6.2. I had to use double slashes `'//path//to//your//csv//file//model.csv'` and all fields containing single slashes broke. – 576i Nov 20 '14 at 13:42
you have to use any mysql client (CLI, workbench, phpmyadmin). you should have the command line interface available (CLI). To access type "mysql -u your_mysql_root_user -p" – Packet Tracer Nov 24 '14 at 17:30
Would this update the table with the data in the CSV or is it just temporary? – NuWin Nov 16 '16 at 00:41

score 71 · Answer 2 · edited Sep 20 '16 at 19:32

71

In case you have smaller data set, a way to achieve it by GUI is:

Open a query window
SELECT * FROM [table_name]
Select Import from the menu bar
Press Apply on the bottom right below the Result Grid

enter image description here

Reference: http://www.youtube.com/watch?v=tnhJa_zYNVY

edited Sep 20 '16 at 19:32

joshuahedlund

1,722
2
20
25

answered Feb 13 '14 at 06:18

Gabriel Chung

1,527
1
18
30

15

Just a NOTE. That import option is only there on Windows machines. The plugins are apparently still .Net based so do not work on Linux or Mac. – BrianC Mar 24 '14 at 16:31
8

Don't forget to press Apply after importing. – Steve Pitchers Sep 03 '14 at 18:16
9

Beware, this can be very slow. It took a couple of hours do to 25,000 rows – Rob Sedgwick Sep 08 '14 at 09:42
3

The length of time seems to be very hardware dependent. My home computer completely choked on 10K rows. My work computer just imported 243K rows with about 200 columns in 85 seconds. – KathyA. Sep 16 '14 at 14:20
On Linux at least (not sure about windows) there is now an import option. When selecting the CSV the open dialog appears to freeze and the entire program stops responding, if you leave it long enough it does do the import. – Tom B Oct 01 '14 at 15:16
1

Note that this requires the CSV to not have a header row. Also, if it includes only a subset of columns from the table, you can update the `SELECT` query to include just those rows, and the import will add them in the specified order. – Deanna Mar 30 '15 at 15:52
2

@BrianC This appears to no longer be the case, it appears for me on the Linux version. – Jammerx2 Jun 23 '15 at 18:37
one note this is showing only if you execute select * from some_table; if you select specific fields then it doesn't show the import icon.. – aimiliano Apr 15 '16 at 12:47
1

I've found that clicking the Import button versus the sql command LOAD DATA LOCAL INFILE... is worse, it doesn't tell you any error messages when it completes, and usually freezes up, and sometimes also doesn't even show the executing symbol – Yusha Feb 28 '17 at 20:53
1

In this way you should use `,` separator. – onirix Mar 09 '17 at 13:55
1

I agree with @Yusha. I just wasted tons of time with the MySql Workbench Table Data Import Wizard. It always failed silently. I ended up using `LOAD DATA LOCAL INFILE`. – Ryan May 20 '17 at 20:42
i can't get ths to work since my table has an auto increment column – Bachask8 Jul 11 '17 at 17:17
is this feature available atleast now on mac? – Dinesh Oct 20 '17 at 23:56
Export/Import option will not show up in GUI if you are looking at a table without a primary key. I noticed this on a table that had none and then i added a pk,nn, auto-increment and the option showed up. – jtlindsey Jun 30 '21 at 19:03

score 28 · Answer 3 · answered Mar 12 '16 at 04:21

28

In the navigator under SCHEMAS, right click your schema/database and select "Table Data Import Wizard"

Works for mac too.

answered Mar 12 '16 at 04:21

DannyPadilla

281
3
5

2

This worked for me on Ubuntu 18.04 when creating a new table from the csv. However I was unable to add data to an existing table using this method. – L.P. Whigley Jul 16 '18 at 17:00
Thank you, I thought I was losing my mind over my inability to find this in the UI. – siliconrockstar Dec 06 '21 at 22:27

score 6 · Answer 4 · answered Mar 12 '18 at 10:49

6

You can use MySQL Table Data Import Wizard

answered Mar 12 '18 at 10:49

Shyju M

9,387
4
43
48

Thanks :) it solved my problem. we can change the column name later from UI(not for experts), once it gets imported... – vipul kumawat May 07 '19 at 17:03
tried many things that did't work but this finally did. it worked even without messing with local data infile settings. I'm guessing MySQL is running inserts instead. – Peppershaker Jun 17 '20 at 04:19

score 1 · Answer 5 · answered Jul 08 '15 at 13:01

At the moment it is not possible to import a CSV (using MySQL Workbench) in all platforms, nor is advised if said file does not reside in the same host as the MySQL server host.

However, you can use mysqlimport.

Example:

mysqlimport --local --compress --user=username --password --host=hostname \
--fields-terminated-by=',' Acme sales.part_*

In this example mysqlimport is instructed to load all of the files named "sales" with an extension starting with "part_". This is a convenient way to load all of the files created in the "split" example. Use the --compress option to minimize network traffic. The --fields-terminated-by=',' option is used for CSV files and the --local option specifies that the incoming data is located on the client. Without the --local option, MySQL will look for the data on the database host, so always specify the --local option.

There is useful information on the subject in AWS RDS documentation.

score 0 · Answer 6 · answered May 15 '15 at 00:46

If the server resides on a remote machine, make sure the file in in the remote machine and not in your local machine.

If the file is in the same machine where the mysql server is, make sure the mysql user has permissions to read/write the file, or copy teh file into the mysql schema directory:

In my case in ubuntu it was: /var/lib/mysql/db_myschema/myfile.csv

Also, not relative to this problem, but if you have problems with the new lines, use sublimeTEXT to change the line endings to WINDOWS format, save the file and retry.

score 0 · Answer 7 · edited Jan 05 '18 at 14:43

0

It seems a little tricky since it really had bothered me for a long time.

You just need to open the table (right click the "Select Rows- Limit 10000") and you will open a new window. In this new window, you will find "import icon".

edited Jan 05 '18 at 14:43

TylerH

20,799
66
75
101

answered Jun 06 '15 at 09:01

Chen

1

score 0 · Answer 8 · answered Sep 26 '22 at 18:14

0

https://www.convertcsv.com/csv-to-sql.htm

This helped me a lot. You upload your excel (or .csv) file and it would give you an .sql file with SQL statements which you can execute - even in the terminal on Linux.

answered Sep 26 '22 at 18:14

parsecer

4,758
13
71
140

How to import a csv file into MySQL workbench?

8 Answers8

Linked