CODEWITHSUNDEEP
mysqlcsvimport
vps
vps

Reputation: 1367

How to import a csv file into MySQL workbench?

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

Upvotes: 98

Views: 279998

Answers (6)

Chen
Chen

Reputation: 1

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

Upvotes: 0

Packet Tracer
Packet Tracer

Reputation: 3924

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

Upvotes: 146

Gabriel Chung
Gabriel Chung

Reputation: 1587

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

enter image description here

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

Upvotes: 71

DannyPadilla
DannyPadilla

Reputation: 291

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

Works for mac too.

Upvotes: 29

Panagiotis Moustafellos
Panagiotis Moustafellos

Reputation: 1013

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.

Upvotes: 1

Eduardo Chongkan
Eduardo Chongkan

Reputation: 800

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.

Upvotes: 0

Related Questions