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

Lubin

You can do it also in dbForge Studio for MySQL import wizard. It automatically generates a table in such way prnt.sc/e5iqy1 – Burtonburty

Answer 1

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

Paletot

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 – Lubin

csv file is stored in my system desktop – Lubin

check the answer again, you should place the file inside mysql data directory, better though specify the whole path – Paletot

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 – Micra

also one shouldn't forget the keyword LOCAL, your query is just right – Dermoid

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. – Amelita

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" – Paletot

Would this update the table with the data in the CSV or is it just temporary? – Wakerife

Answer 2

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

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

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

Vasti

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. – Nameplate

Don't forget to press Apply after importing. – Boon

Beware, this can be very slow. It took a couple of hours do to 25,000 rows – Ergograph

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. – Neurocoele

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. – Bloch

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. – Brakesman

@Nameplate This appears to no longer be the case, it appears for me on the Linux version. – Curbing

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.. – Tomb

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 – Fairweather

In this way you should use , separator. – Daphinedaphna

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. – Anathematize

i can't get ths to work since my table has an auto increment column – Ringe

is this feature available atleast now on mac? – Eckart

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. – Toots

Answer 3

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

Works for mac too.

Preface

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. – Centenary

Thank you, I thought I was losing my mind over my inability to find this in the UI. – Extraditable

Answer 4

You can use MySQL Table Data Import Wizard

Vaclav

Thanks :) it solved my problem. we can change the column name later from UI(not for experts), once it gets imported... – Karr

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. – Gluteus

Answer 5

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.

Tussock

Answer 6

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.

Barrus

Answer 7

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".

Gibbeon

Answer 8

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.

Kofu