如何将 CSV 文件导入 MySQL 表

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

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

然后，您可以通过运行将其导入 MySQL 表：

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

如继续阅读：Import CSV file directly into MySQL

编辑

对于您的情况，您需要先编写一个解释器，以查找第一行并将它们分配为列名。

编辑-2

来自 MySQL 文档 on LOAD DATA syntax：

IGNORE number LINES 选项可用于忽略文件开头的行。例如，您可以使用 IGNORE 1 LINES 跳过包含列名的初始标题行： LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

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

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

---

这是一个简单的 PHP 命令行脚本，可以满足您的需要：

<?php

$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'database';

$db = mysql_connect($host, $user, $pass);
mysql_query("use $database", $db);

/********************************************************************************/
// Parameters: filename.csv table_name

$argv = $_SERVER[argv];

if($argv[1]) { $file = $argv[1]; }
else {
    echo "Please provide a file name\n"; exit; 
}
if($argv[2]) { $table = $argv[2]; }
else {
    $table = pathinfo($file);
    $table = $table['filename'];
}

/********************************************************************************/
// Get the first row to create the column headings

$fp = fopen($file, 'r');
$frow = fgetcsv($fp);

foreach($frow as $column) {
    if($columns) $columns .= ', ';
    $columns .= "`$column` varchar(250)";
}

$create = "create table if not exists $table ($columns);";
mysql_query($create, $db);

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].'/'.$file;
$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
mysql_query($q, $db);

?>

它将基于第一行创建一个表并将剩余的行导入其中。这是命令行语法：

php csv_import.php csv_file.csv table_name

---

如果您有能力安装 phpadmin 有一个导入部分，您可以在其中将 csv 文件导入数据库，甚至还有一个复选框可以将标题设置为文件的第一行包含表列名称（如果未选中，则第一行将成为数据的一部分

---

首先在数据库中创建一个表，其列数与 csv 文件中的列数相同。

然后使用以下查询

LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

---

如果您以“mysql -u -p --local-infile”启动mysql，它将正常工作

---

要从文本文件或 csv 文件加载数据，命令是

load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);

在上面的命令中，在我的情况下，只有一列要加载，因此没有“终止于”和“封闭于”，所以我将其保持为空，否则程序员可以输入分隔符。例如。 ,（逗号）或“或；或任何东西。

**适用于使用mysql 5及以上版本的人**

在将文件加载到 mysql 之前，必须确保在 etc/mysql/my.cnf 侧添加以下牵引线

编辑 my.cnf 命令是

sudo vi /etc/mysql/my.cnf

[mysqld]  
local-infile

[mysql]  
local-infile  

---

我写了一些代码来做到这一点，我会放几个片段：

$dir = getcwd(); // Get current working directory where this .php script lives
$fileList = scandir($dir); // scan the directory where this .php lives and make array of file names

然后获取 CSV 标头，以便您可以告诉 mysql 如何导入（注意：确保您的 mysql 列与 csv 列完全匹配）：

//extract headers from .csv for use in import command
$headers = str_replace("\"", "`", array_shift(file($path)));
$headers = str_replace("\n", "", $headers);

然后将您的查询发送到 mysql 服务器：

mysqli_query($cons, '
        LOAD DATA LOCAL INFILE "'.$path.'"
            INTO TABLE '.$dbTable.'  
            FIELDS TERMINATED by \',\' ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            IGNORE 1 LINES
            ('.$headers.')
            ;
        ')or die(mysql_error());

---

我为此挣扎了一段时间。问题不在于如何加载数据，而在于如何构造表来保存它。在导入数据之前，您必须生成 DDL 语句来构建表。

如果表有大量列，则特别困难。

这是一个（几乎）完成这项工作的python脚本：

#!/usr/bin/python    
import sys
import csv

# get file name (and hence table name) from command line
# exit with usage if no suitable argument   
if len(sys.argv) < 2:
   sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename')
ifile = sys.argv[1]

# emit the standard invocation
print 'create table ' + ifile + ' ('

with open(ifile + '.csv') as inputfile:
   reader = csv.DictReader(inputfile)
   for row in reader:
      k = row.keys()
      for item in k:
         print '`' + item + '` TEXT,'
      break
   print ')\n'

它要解决的问题是最终的字段名称和数据类型声明以逗号结尾，而 mySQL 解析器不会容忍这种情况。

当然，它也有一个问题，就是每个字段都使用 TEXT 数据类型。如果表有几百列，那么 VARCHAR(64) 会使表太大。

这似乎也打破了 mySQL 的最大列数。如果可以的话，那就是迁移到 Hive 或 HBase 的时候了。

---

以下是我在 Python 中使用 csv 和 MySQL Connector 的方法：

import csv
import mysql.connector

credentials = dict(user='...', password='...', database='...', host='...')
connection = mysql.connector.connect(**credentials)
cursor = connection.cursor(prepared=True)
stream = open('filename.csv', 'rb')
csv_file = csv.DictReader(stream, skipinitialspace=True)

query = 'CREATE TABLE t ('
query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames)
query += ')'
cursor.execute(query)
for row in csv_file:
    query = 'INSERT INTO t SET '
    query += ','.join('`{}` = ?'.format(column) for column in row.keys())
    cursor.execute(query, row.values())

stream.close()
cursor.close()
connection.close()

关键点

对 INSERT 使用准备好的语句

在“rb”二进制文件中打开 file.csv

某些 CSV 文件可能需要调整，例如 skipinitialspace 选项。

如果 255 不够宽，您将在 INSERT 上收到错误，并且必须重新开始。

调整列类型，例如 ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);

添加主键，例如 ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

---

将 CSV 文件导入 mysql 表

LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Character   Escape Sequence
\0      An ASCII NUL (0x00) character
\b      A backspace character
\n      A newline (linefeed) character
\r      A carriage return character
\t      A tab character.
\Z      ASCII 26 (Control+Z)
\N      NULL

访问：http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html

---

使用 TablePlus 应用程序：右键单击右侧面板中的表名 选择 Import... > From CSV 选择 CSV 文件 查看列匹配并点击 Import All 完成！

---

正如其他人所提到的，加载数据本地 infile 工作得很好。我尝试了 Hawkee 发布的 php 脚本，但它对我不起作用。而不是调试它，这就是我所做的：

1) 将 CSV 文件的标题行复制/粘贴到 txt 文件中，并使用 Emacs 进行编辑。在每个字段之间添加一个逗号和 CR，以使每个字段占一行。 2) 将该文件另存为 FieldList.txt。 3) 编辑文件以包含每个字段的定义（大多数是 varchar，但也有不少是 int(x)。将 create table *tablename*（到文件的开头和）添加到文件的末尾。保存它作为 CreateTable.sql。 4) 使用来自 Createtable.sql 文件的输入启动 mysql 客户端以创建表。 5）启动mysql客户端，复制/粘贴大部分'LOAD DATA INFILE'命令，替换我的表名和csv文件名。粘贴到 FieldList.txt 文件中。在粘贴到字段列表之前，请务必包含“IGNORE 1 LINES”。

听起来工作量很大，但使用 Emacs 很容易......

---

所以我尝试使用 Hawkee 提供的脚本，但有些命令已经过时了。使用 mysql_X 已贬值，需要更换为 mysqli_x。在进行了一些故障排除后，我编写了以下脚本，它运行良好。

请注意：以下代码假定您正在输入浮点数。我使用此脚本从 WHO 导入百分位数以获取与增长相关的统计数据。

如果要删除表，请使用 -drop （在文件名之前）

<?php
//This script is for importing the percentile values.
//Written by Daniel Pflieger @ GrowlingFlea Software

$host = 'localhost';
$user = 'root';
$pass = '';
$database = '';

//options.  This is what we need so the user can specify whether or not to drop the table
$short_options = "d::";
$options = getopt($short_options);

//check if the flag "-drop" is entered by the end user.  
if (!empty($options) &&  $options['d'] != "rop"){
    echo "The only available argument is -drop \n";
    exit;
} else if (!empty($options)){
    $dropTable = true;

} else {
    $dropTable = false;

}

//we use mysqli_* since this is required with newer versions of php
$db = mysqli_connect($host, $user, $pass, $database);

// argv changes if the drop flag is used. here we read in the name of the .csv file we want to import
if (isset($argv[1]) && empty($options) ) {
    $file = $argv[1];
} else  if (isset($argv[2]) && $options[1] = "rop" ) {
    $file = $argv[2];
}

//we call the table name the name of the file.  Since this script was used to import who growth chart info
//I appended the '_birth_to_5yrs' to the string.  You probably want to remove this and add something that
//makes sense to you
$table = pathinfo($file);
$table = "who_" . $table['filename'] . "_birth_to_5yrs";
$table = str_replace('-', '_', $table);

// We read the first line of the .csv file.  It is assumed that these are the headers.
$fp = fopen($file, 'r');
$frow = fgetcsv($fp);
$columns = '';

//we get the header names and for this purpose we make every value 'float'.  If you are unsure of
//the datatype you can probably use varchar(250).
foreach($frow as $column) {
    $columns .= "`" .$column . "` float,";

}

//drop the table to prevent data issues, if that is what the end user selects
if ($dropTable) {
    mysqli_query($db, "drop table if exists $table");

}

// here we form the create statement and we create the table.
// we use the mysqli_real_escape_string to make sure we dont damage the DB
$create = "create table if not exists $table ($columns);";
$create = str_replace(',)', ')', $create);
$create = mysqli_real_escape_string($db, $create);
mysqli_query($db, $create);

// We read the values line-by-line in the .csv file and insert them into the table until we are done.
while ($frow = fgetcsv($fp)){
    $insert = implode(", ", $frow);
    $insert = "Insert into $table VALUES ( $insert )";
    $insert = mysqli_real_escape_string($db, $insert);
    $insert = mysqli_query($db, $insert);
}
   

如何运行脚本的示例：

php ../git/growlingflea-dev-tools/importCSV.php -drop wfh-female-percentiles-expanded-tables.csv

---

我在谷歌上搜索了许多将 csv 导入 mysql 的方法，包括“加载数据 infile”、使用 mysql 工作台等。

当我使用mysql工作台导入按钮时，首先你需要自己创建空表，自己设置每个列类型。注意：你必须在末尾添加 ID 列作为主键，而不是 null 和 auto_increment，否则稍后将看不到导入按钮。但是，当我开始加载 CSV 文件时，没有加载任何内容，这似乎是一个错误。我放弃。

幸运的是，到目前为止我发现的最简单的方法是使用 Oracle 的 mysql for excel。你可以从这里下载它mysql for excel

这就是你要做的：在 excel 中打开 csv 文件，在“数据”选项卡上，找到 mysql for excel 按钮

选择所有数据，点击导出到mysql。注意将 ID 列设置为主键。

完成后，去mysql工作台修改表，比如货币类型应该是十进制（19,4）大额小数（10,2）经常使用。其他字段类型可以设置为 varchar(255)。

---