将CSV导入到mysql表 [英] Import CSV to mysql table

问题描述

将csv文件上传到mysql表中的最好/最快的方法是什么？我想将第一行数据用作列名。

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names.

找到这个：

a href =http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table>如何将CSV文件导入MySQL表

How to import CSV file to MySQL table

但是唯一的答案是使用GUI而不是shell？

But the only answer was to use a GUI and not shell?

推荐答案

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

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 a>

对于您的情况，您需要先写一个解释器，第一行，并将它们指定为列名称。

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

从MySQL docs 在载入资料语法：

From MySQL docs on LOAD DATA syntax:

IGNORE数字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)

这篇关于将CSV导入到mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！