如何将 csv 文件导入 MySQL 工作台? [英] How to import a csv file into MySQL workbench?

问题描述

我有一个 CSV 文件.它包含 140 万行数据，因此我无法在 Excel 中打开该 csv 文件，因为它的限制约为 100 万行.

因此，我想在 MySQL 工作台中导入此文件.此 csv 文件包含类似

的列

服务区号"、电话号码"、首选项"、Opstype"、电话类型"

我正在尝试在 MySQL 工作台中创建一个名为虚拟"的表，其中包含

之类的列

ServiceAreaCodes,PhoneNumbers,Preferences,Opstyp,PhoneTyp.

CSV 文件名为 model.csv.我在工作台的代码是这样的:

LOAD DATA LOCAL INFILE 'model.csv' INTO TABLE test.dummy FIELDS TERMINATED BY ',' 行终止于 '
';

但我收到类似model.CSV file not found

的错误

解决方案

我猜你错过了 ENCLOSED BY 子句

LOAD DATA LOCAL INFILE '/path/to/your/csv/file/model.csv'INTO TABLE test.dummy FIELDS TERMINATED BY ','由 '"' 括起来，以 '
' 结尾的行；

并指定csv文件的完整路径

加载数据 Infile - MySQL 文档

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

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

解决方案

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

And specify the csv file full path

Load Data Infile - MySQL documentation

这篇关于如何将 csv 文件导入 MySQL 工作台?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！