将CSV文件加载到MySQL Workbench中

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工作台(我在Mac OS X)。我在寻找一个好的演练或教程,但我没有看到任何明显解释如何华禾投资加载到MySQL工作台....谁能帮忙吗?

6 个解决方案

#1

are you trying to load csv files into a MySQL table? You can do that easily with the LOAD DATA LOCAL INFILE command.

是否尝试将csv文件加载到MySQL表中?您可以使用LOAD DATA LOCAL INFILE命令轻松实现这一点。

Example:

例子:

LOAD DATA LOCAL INFILE '/data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','

加载数据本地文件'/数据。csv'进入表my_table字段，以'，'终止

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接口输入该命令。我假设workbench能够执行sql查询。

#2

Does it have to be Workbench?

它一定是工作台吗?

Can you use other MySQL bins?

你能使用其他的MySQL箱吗?

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

加载数据本地文件'/路径/等等。成表dbName csv”。以' ' '，' ' ' ' ' ' ' ' ' '的' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '的' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

#3

For reference, I wanted to do this on Windows and used HeidiSQL to accomplish the task. It was very straightforward, I used Tools -> Import CSV file and it worked on the first try.

作为参考，我想在Windows上这样做，并使用HeidiSQL完成任务。它非常简单，我使用了工具—>导入CSV文件，它在第一次尝试时起作用。

#4

Or you could use Excel to generate a lot of LOAD DATA INFILE statements using a list of files to load.

或者，您可以使用Excel来使用文件列表来加载大量的加载数据。

Say, you could:

说,你可以:

place the names of files in A column of Excel
将文件名称放在Excel列中
place a formula like this in B1 cell:

在B1细胞中放置这样的公式:

="LOAD DATA LOCAL INFILE 'path/"&A1&"' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"

="将本地数据载入'path/ ' & ' '到表tablename字段中，以'\ ' r\n'终止;' ' ' ' ' ' ' ' ' '\r\n'终止;"
copy & paste B1 cell in B column cells where needed.

在需要的地方复制粘贴B列细胞中的B1细胞。

So, you would have LOAD DATA statemens in B column of Excel and could just copy&paste them into mysql command line.

所以，你可以在Excel的B栏中载入数据，然后将它们复制粘贴到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的强用户来说是有意义的。

#5

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 w/，分离和“作为转义字符”。PHPmyAdmin无法处理“as escape”，因此我找到/替换了“”的所有实例，并成功了。Suerte !

#6

Open the Excel file in Numbers iWork 9) and export to CSV. Import in MySQL with 'CSV with LOAD DATA' option works fine.

打开数字iWork 9中的Excel文件并导出到CSV。在MySQL中导入带有“LOAD DATA”选项的“CSV”选项工作正常。

#1