如何将 CSV 文件导入 MySQL 表

2021-11-20 00:00:00 csv import mysql load-data-infile database-table

如何将 CSV 文件导入 MySQL 表?我想将第一行数据用作列名.

How can I import a CSV file into a MySQL table? I would like for the first row of data be used as the column names.

我阅读了如何将 CSV 文件导入 MySQL 表?，但唯一的答案是使用 GUI 而不是外壳?

I read How do I import CSV file into a MySQL table?, but the only answer was to use a GUI and not a shell?

推荐答案

您可以将 MYSQL 直接链接到它并使用以下 SQL 语法上传信息，而不是编写脚本来从 CSV 文件中提取信息.

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.

>

要将 Excel 文件导入 MySQL，首先将其导出为 CSV 文件.从生成的 CSV 文件中删除 CSV 标题以及 Excel 可能放在 CSV 文件末尾的空数据.

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

然后您可以通过运行将其导入到 MySQL 表中:

You can then import it into a MySQL table by running:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (uniqName, uniqCity, uniqComments)

阅读:将 CSV 文件直接导入 MySQL

对于您的情况，您需要先编写一个解释器，用于查找第一行并将它们指定为列名.

For your case, you'll need to write an interpreter first, for finding the first row, and assigning them as column names.

来自 MySQL 文档 关于 LOAD DATA 语法:

From MySQL docs on LOAD DATA syntax:

IGNORE number LINES 选项可用于忽略在文件的开头.例如，您可以使用 IGNORE 1 LINES 跳过在包含列名的初始标题行上:

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

因此，您可以使用以下语句:

Therefore, you can use the following statement:

LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)

相关文章