如何将 csv 文件导入到 MySQL 工作台？

我有一份 CSV 档案。它包含140万行数据，所以我无法在 Excel 中打开那个 csv 文件，因为它的限制是大约100万行。

因此，我想在 MySQL 工作台中导入这个文件

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

我正试图在 MySQL 工作台中创建一个名为“虚拟”的表，其中包含如下列的列

ServiceAreaCodes,PhoneNumbers,Preferences,Opstyp,PhoneTyp.

CSV 文件名为 model.csv:

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

但是我得到了一个像 model.CSV file not found这样的错误

272657 次浏览
小开
最佳答案

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

小开

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

小开

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.

小开

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

小开

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.

小开

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

Works for mac too.

小开

You can use MySQL Table Data Import Wizard

小开

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.


提交答案