如何将 csv 文件导入 MySQL 工作台？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11429827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import a csv file into MySQL workbench?
提问by vps
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.
我有一个 CSV 文件。它包含 140 万行数据，因此我无法在 Excel 中打开该 csv 文件，因为它的限制约为 100 万行。
Therefore, I want to import this file in MySQL workbench. This csv file contains columns like
因此，我想在 MySQL 工作台中导入这个文件。这个 csv 文件包含像这样的列
"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
我正在尝试在 MySQL 工作台中创建一个名为“虚拟”的表，其中包含类似的列
ServiceAreaCodes,PhoneNumbers,Preferences,Opstyp,PhoneTyp.
The CSV file is named model.csv. My code in workbench is like this:
CSV 文件名为model.csv. 我在工作台中的代码是这样的：
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
但我收到了类似的错误 model.CSV file not found
回答by Packet Tracer
I guess you're missing the ENCLOSED BY clause
我猜你错过了 ENCLOSED BY 子句
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
并指定csv文件完整路径
回答by Gabriel Chung
In case you have smaller data set, a way to achieve it by GUI is:
如果您的数据集较小，则通过 GUI 实现它的方法是：
- Open a query window
- SELECT * FROM [table_name]
- Select Import from the menu bar
- Press Apply on the bottom right below the Result Grid
- 打开查询窗口
- SELECT * FROM [table_name]
- 从菜单栏中选择导入
- 按结果网格下方右下角的应用
Reference: http://www.youtube.com/watch?v=tnhJa_zYNVY
回答by DannyPadilla
In the navigator under SCHEMAS, right click your schema/database and select "Table Data Import Wizard"
在 SCHEMAS 下的导航器中，右键单击您的架构/数据库并选择“表数据导入向导”
Works for mac too.
也适用于 mac。
回答by Panagiotis Moustafellos
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.
目前不可能在所有平台上导入 CSV（使用 MySQL Workbench），如果所述文件与 MySQL 服务器主机不在同一主机中，也不建议这样做。
However, you can use mysqlimport.
但是，您可以使用mysqlimport。
Example:
例子：
mysqlimport --local --compress --user=username --password --host=hostname \
--fields-terminated-by=',' Acme sales.part_*
In this example mysqlimportis 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.
在此示例mysqlimport中，指示加载所有名为“sales”且扩展名为“part_”的文件。这是加载在“拆分”示例中创建的所有文件的便捷方法。使用 --compress 选项来最小化网络流量。--fields-terminated-by=',' 选项用于 CSV 文件，--local 选项指定传入数据位于客户端。如果没有 --local 选项，MySQL 将在数据库主机上查找数据，因此始终指定 --local 选项。
There is useful information on the subject in AWS RDS documentation.
AWS RDS 文档中有关于该主题的有用信息。
回答by Shyju M
You can use MySQL Table Data Import Wizard
您可以使用 MySQL表数据导入向导
回答by Eduardo Chongkan
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:
如果文件在mysql服务器所在的同一台机器上，请确保mysql用户具有读/写文件的权限，或者将文件复制到mysql架构目录中：
In my case in ubuntu it was: /var/lib/mysql/db_myschema/myfile.csv
在我的 ubuntu 案例中，它是：/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.
另外，不是相对于这个问题，但是如果新行有问题，请使用 sublimeTEXT 将行尾更改为 WINDOWS 格式，保存文件并重试。
回答by Chen
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".
您只需要打开表格（右键单击“选择行-限制 10000”），您就会打开一个新窗口。在这个新窗口中，您将找到“导入图标”。
