如何将CSV文件导入到MySQL表中

Question

如何将CSV文件导入到MySQL表中

mysqlcsvimportload-data-infiledatabase-table

101

我该如何将CSV文件导入MySQL表中？我希望使用数据的第一行作为列名。

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

- lcm

3

即使是图形用户界面（GUI）解决方案也不会从CSV文件中获取列名...您需要在导入之前创建整个表格。 - Dominique

1

这个问题已经在这里有了答案 https://dev59.com/k3A65IYBdhLWcg3wyRyk#45703889 - David

1

请使用在原问题中提到的mysqlimport来进行操作。 - Ferris

这是一个重复的问题，链接为https://dev59.com/k3A65IYBdhLWcg3wyRyk，即使您不喜欢GUI。您可以发布悬赏以寻求更好的答案。在这种情况下，大约1年后提供了一个非GUI的答案。我今天已经用完了投票次数，但明天会回来。 - TylerH

SQL有一个LOAD DATA INFILE，可以用来读取和格式化CSV文件。它非常强大，但文档不是很完善。这篇文章讲解了如何使用它：https://blog.terresquall.com/2021/11/importing-a-csv-file-into-an-sql-table/ - John Doe

显示剩余2条评论

15个回答

27

这是一个简单的PHP命令行脚本，可以完成你需要的功能：

<?php

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


$db = mysqli_connect($host, $user, $pass) or die ("could not connect to mysql");
mysqli_select_db($db, $database) or die ("no database");
/********************************************************************************/
// 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);";
mysqli_query($db, $create) or die(mysqli_error($db));

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

$file = $_SERVER['PWD'].'/'.$file;
$q = "LOAD DATA INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ignore 1 lines;";
mysqli_query($db, $q) or die(mysqli_error($db));

?>

它将根据第一行创建一个表，并将其余的行导入其中。以下是命令行语法：

php csv_import.php csv_file.csv table_name

- Hawkee

2

牛逼的脚本。对于那些使用双引号CSV文件（大多数人）的人，将 ENCASED IN '\"' 添加到 fields terminated by ',' 中...它甚至可以处理部分双引号CSV文件。 - Joel Mellon

3

我想你的意思是 ENCLOSED BY '\"'... 此外，如果使用来自Windows的CSV文件，许多人将需要 LINES TERMINATED BY '\r\n'。最后，用反引号转义字段名称是明智的，以防有空格： $columns .= "\$column` varchar(250)";` - dlo

1

这个答案比被采纳的答案好多了。特别是它允许OP所要求的，而我也想要："将第一行数据用作列名"。（我更喜欢Python脚本，这样我就不必安装PHP，但移植应该不难。） - LarsH

2

@YumYumYum，您能详细说明您遇到的问题吗？ - Hawkee

我能给你买杯啤酒吗？ - Joe

这是一个仅包含链接的回答。将相关代码放入帖子本身中，否则当链接失效时，该回答就毫无用处了。 - gre_gor

5

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

- Jose Ortiz

1

我真的很惊讶，你必须使用像phpadmin这样的插件才能获得这个功能。谢谢你的回答。 - chrisfs

这让我的一天变得更美好了。 - Mark

3

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

- marciomolusco

3

首先在数据库中创建一个与csv文件中列数相同的表格。然后使用以下查询语句。

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

- Mukesh

如果我想在CSV中忽略一列怎么办？ - Marci-man

2

我写了一些代码来实现这个功能，以下是几个片段：

```

我编写了一些代码来完成这个任务，以下是一些片段：

```

$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());

- ravenchilde

2

从文本文件或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

- Rakesh

2

LOAD DATA INFILE语句允许直接从文件中将数据加载到表中：

LOAD DATA INFILE 'path_to_csv_file'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

一种替代方法是使用dbForge Studio IDE for MySQL，它可以实现无缝的数据导出和导入。数据导出和数据导入向导提供了逐步指南，使整个过程变得简单易行。您可以导出特定行或一系列行，并且可以将数据导入到新表或现有表中。您可以将MySQL数据导出为多种格式，包括HTML、TXT、XLS、XLSX、MDB、RTF、PDF、JSON、XML、CSV、ODBC、DBF、SQL和Google Sheets。

本指南提供了逐步指南，教您如何从CSV文件中导入MySQL数据。

- Jessica Williams

1

以下是我在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()

要点

使用预处理语句进行插入操作
以'rb'二进制格式打开文件.csv
一些CSV文件可能需要微调，例如skipinitialspace选项。
如果255不够宽，将在插入时出现错误并不得不重新开始。
调整列类型，例如ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);
添加主键，例如ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

- Bob Stein

1

我曾经为此苦恼了一段时间。问题不在于如何加载数据，而在于如何构建用于保存数据的表格。您必须生成一个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的时候了（如果您能够这样做）。

- agentv

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- hjpotter92 · Accepted Answer

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

要将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)

参考自：将CSV文件直接导入MySQL数据库

编辑

你需要先编写一个解释器，找到第一行并将其作为列名分配。

编辑-2

来自MySQL文档LOAD DATA语法：

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)