将 CSV 文件加载到 MySQL Workbench
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8143110/
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
Loading CSV files into MySQL Workbench
提问by janglosaxon
I have a lot of excel/ CSV files that I need to load into my db in MySQL Workbench (I'm on Mac OS X). I've searched around for a good walk-through or tutorial, but I haven't seen anything that clearly explains how to load CSVs into MySQL Workbench....can anyone help?
我有很多 excel/CSV 文件需要加载到 MySQL Workbench 的数据库中（我使用的是 Mac OS X）。我已经四处搜索了一个很好的演练或教程，但我还没有看到任何清楚地解释如何将 CSV 加载到 MySQL Workbench 的内容......有人可以帮忙吗？
回答by Ben English
are you trying to load csv files into a MySQL table? You can do that easily with the LOAD DATA LOCAL INFILEcommand.
您是否正在尝试将 csv 文件加载到 MySQL 表中？您可以使用该LOAD DATA LOCAL INFILE命令轻松完成此操作。
Example:
例子：
LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','
LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','
You should be able to enter that command from any interface to MySQL. I'm assuming workbench has a way for you to execute sql queries.
您应该能够从任何接口输入该命令到 MySQL。我假设工作台有一种方法可以让您执行 sql 查询。
回答by bananafish
回答by djdy
Does it have to be Workbench?
必须是工作台吗？
Can you use other MySQL bins?
你可以使用其他 MySQL bins 吗？
Here's an example: Create database
下面是一个例子：创建数据库
Create table
创建表
load data local infile '/path/whatever.csv' into table dbName.tableName fields terminated by ',' enclosed by '"' lines terminated by '\n';
load data local infile '/path/whatever.csv' into table dbName.tableName fields terminated by ',' enclosed by '"' lines terminated by '\n';
回答by Pavel
Or you could use Excel to generate a lot of LOAD DATA INFILE statements using a list of files to load.
或者，您可以使用 Excel 使用要加载的文件列表生成大量 LOAD DATA INFILE 语句。
Say, you could:
说，你可以：
- place the names of files in A column of Excel
place a formula like this in B1 cell:
="LOAD DATA LOCAL INFILE 'path/"&A1&"' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"
copy & paste B1 cell in B column cells where needed.
- 将文件名放在Excel的A列中
在 B1 单元格中放置一个这样的公式：
="LOAD DATA LOCAL INFILE 'path/"&A1&"' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"
在需要的地方将 B1 单元格复制并粘贴到 B 列单元格中。
So, you would have LOAD DATA statemens in B column of Excel and could just copy&paste them into mysql command line.
因此，您将在 Excel 的 B 列中有 LOAD DATA 语句，并且可以将它们复制并粘贴到 mysql 命令行中。
Maybe it would not be too convinient to power user of scripting, but could make sense for strong user of MS Excel.
也许对于脚本的高级用户来说不太方便，但对于 MS Excel 的强大用户来说可能有意义。
回答by Andrew Samuelsen
This was convoluted for me, but here is what I did:
这对我来说很复杂，但这是我所做的：
I had PHPmyAdmin installed, so I exported to CSV w/ ,seperated and "as escape character. PHPmyAdmin couldn't handle the "as escape, so I find/replaced all instances of ""with \"and it worked. Suerte!
我安装了 PHPmyAdmin，所以我导出到带有,分隔"符和转义符的CSV 。PHPmyAdmin 无法处理"as 转义，所以我找到/替换了""with 的所有实例\"并且它起作用了。苏尔特！
回答by heimi
Open the Excel file in Numbers iWork 9) and export to CSV. Import in MySQL with 'CSV with LOAD DATA' option works fine.
在 Numbers iWork 9) 中打开 Excel 文件并导出为 CSV。使用 'CSV with LOAD DATA' 选项导入 MySQL 工作正常。
