如何将多个csv文件导入到MySQL数据库中

Question

有没有一种方法可以同时将多个csv文件导入到MySQL数据库中？有批量导入的方式吗？

我的电脑是Mac OSX，运行着MAMP服务器。

我有185个csv文件需要导入到MySQL表中。我可以使用phpMyAdmin的导入选项逐个导入它们，但这需要很长时间。是否有更好的方法？

- Laxmidi

Answer 1

使用这样的 shell 脚本：

#!/usr/bin/env bash
cd yourdirectory
for f in *.csv
do
        mysql -e "USE yourDatabase LOAD DATA LOCAL INFILE '"$f"'INTO TABLE yourtable"
done

- nazar554

---

你好nazar554，感谢您的建议。不幸的是，我收到了“-bash：mysql：command not found”错误消息。我不确定我做错了什么。 - Laxmidi

此外，你正在为每个文件调用一次mysql，这是相当浪费的。 - einpoklum

你会如何递归地执行这个操作（包括子目录）？ - Al Grant

Answer 2

这里有一个小的PHP脚本：

#!/usr/bin/php
<?
mysql_connect('localhost','root','root'); // MAMP defaults
mysql_select_db('yourdatabase');
$files = glob('*.csv');
foreach($files as $file){
    mysql_query("LOAD DATA INFILE '".$file."' INTO TABLE yourtable");
}

请参考MySQL手册中关于LOAD DATA INFILE选项的相关信息，以确定适合您文档的选项。

- konsolenfreddy

---

这个能用来让第一行数据作为列名吗？ - Rain Man

Answer 3

你可以使用一个shell脚本来循环处理文件（假设它们在当前目录中）：

#!/bin/bash

for f in *.csv
do
    mysql -e "load data infile '"$f"' into table my_table" -u username --password=your_password my_database
done

- Tom H

---

嗨Tom H，谢谢你的留言。我不知道如何使用bash。我在终端中遇到了这个错误：“-bash：：命令未找到”。你有什么想法吗？谢谢。 - Laxmidi

将shebang替换为nazar554脚本中的shebang（#!/usr/bin/env bash）。之后，您将收到与他相同的错误（mysql：找不到命令），因为我们两个碰巧给出了相同的答案。您需要在托管数据库的同一台机器上运行此脚本（或使用-h开关进行远程连接），并且必须安装mysql命令行客户端。 - Tom H

Answer 4

我修改了Tom的脚本，以解决遇到的一些问题。

#!/bin/bash

for f in *.csv
do
    mysql -e "load data local infile '"$f"' into table myTable fields TERMINATED BY ',' LINES TERMINATED BY '\n'"  -u myUser--password=myPassword fmeter --local-infile
done

1. 使用load data local infile代替load data infile：[需要加载的文件在mysql服务器本地]
2. 添加分隔符开关以匹配我的数据。
3. 使用--local-infile启用客户端本地数据加载模式。

- Abhijeet Apsunde

---

这只是抛出一个错误：语法错误：意外的单词（期望“do”） - John Mellor

Answer 5

对于Windows用户，请使用此批处理文件

echo off
setlocal enabledelayedexpansion
FOR %%f IN ("*.csv") DO (
  set old=%%~dpnxf
  set new=!old:\=\\!
  mysql -e "load data local infile '"!new!"' IGNORE into table email_us.business  COLUMNS TERMINATED BY ','" -u root
  echo %%~nxf DONE
)

• email_us -> DB
• business -> 表格
• IGNORE -> 忽略重复插入并在错误时继续操作
• ~dpnxf -> d 代表驱动器，p 代表文件路径，n 代表文件名，x 代表扩展名，f 是文件变量

步骤： - 将批处理文件放置在包含多个 csv 文件的目录中，并将其命名为 something.bat - 以管理员身份运行 cmd.exe，并调用该 something.bat 文件，享受导入过程...

- Wasim A.

Answer 6

在Python中，您可以使用d6tstack来使其变得简单。

import d6tstack
import glob

c = d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv'))
c.to_mysql_combine('mysql+mysqlconnector://usr:pwd@localhost/db', 'tablename')

它还处理数据模式更改，创建表并允许您预处理数据。

- citynorman

---

我发现d6tstack很适合合并CSV文件，但是to_mysql_combine失败了，因为我的CSV文件非常大。to_mysql_combine还要求我必须使用pandas的to_sql，并将chunksize设置为200。这种方法还支持其他MySQL连接器，如PyMySQL。将在另一个答案中提供详细信息。 c.to_sql(name='inequity', con=engine, if_exists='append', index=False, chunksize=200) - Edward

Answer 7

我有一个类似的任务，需要处理很多CSV文件并创建一个包含所有CSV数据的表格。下面是我在本地XAMP环境中使用的脚本。

<?php
ini_set('display_errors',1);
echo '### Begin Importation<br>';

$mysqli  =  new mysqli(
"localhost",
"root",
"",
"mydatabase",
3306
);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$files = glob('C:\\xampp\\mysql\\data\\mev2\\*.csv');

foreach($files as $file){

    //clean names if needed
    $filename = explode('\\',$file);
    $filename2clean = str_replace('.csv','', $filename[5]);//because my file is under 5 folders on my PC
    $n = strtolower(str_replace('fileprefix_','', filename2clean));

    echo '<br>Create table <b>'.$n.'</b><hr>';

    $sql = "CREATE TABLE IF NOT EXISTS `mydatabase`.`".$n."` (`email` varchar(60), `lastname` varchar(60), `firstname` varchar(60), `country` varchar(19)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";

    if (!($stmt = $mysqli->query($sql))) {
        echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
    };

    echo '<br>Import data from <b>'.$n.'</b><hr>';

    $sql = "LOAD DATA INFILE '".basename($file)."' INTO TABLE `mydatabase`.`".$n."`  
        FIELDS TERMINATED BY ';'
        LINES TERMINATED BY '\r'  
        IGNORE 1 LINES";

    if (!($stmt = $mysqli->query($sql))) {
        echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
    };

}

echo '### Import finished !<br>';

- devseo

---

很棒的脚本！谢谢分享。 - Nick

Answer 8

@hlosukwakha 你想使用 mysqlimport。它会搜索与文件名类似的表格。使用 mysqlimport -help 命令查找正确的参数，但基本上与 mysql 相同。

- Oskar Must

Answer 9

我使用了Python和d6tstack，就像@citynorman一样，但是因为我有200个CSV文件中的2400万行，这种方法会使我的开发数据库服务器崩溃。

这种方法可以在2或3行代码中提供很多控制和性能。它将2400万行数据导入到一个具有五个索引列的MySQL表中，并在大约2分钟内添加了数据清理。而在MySQL Workbench上的csv导入工具则需要数天才能完成相同的任务。

这是我成功实现的方法：

import pandas as pd
import importlib
import d6tstack.combine_csv as d6tc
import d6tstack
import glob
import pymysql  # This approach also supports other MySQL connectors
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://usr:pass@host:3306/db")

# For testing just pull in one or two csv files - and then take all
# My data had a ; semicolon separator, so change this to your use case if needed
df = d6tc.CombinerCSV(glob.glob('C:/Users/user/Downloads/csvfiles/*.csv'), sep=';').to_pandas()

# Remove Filepath and Filename 
df.drop(columns=["filepath","filename"],inplace=True, axis=1)

# I created Indexes in my database file during testing, so this line
# makes sure there are no null index values in the CSVs
df = df[df['country'].notna()]

# chunksize throttles your database updates so as not to overwhelm any buffers
# NEVER use "if_exists=replace", unless you want to blank your table 100%
df.to_sql(name='table', con=engine, if_exists='append', index=False, chunksize=200)  

- Edward

Answer 10

使用以下 shell 脚本：

for file in /directory/*.csv
do
echo "Importing file $file"
       chown mysql $file
        mysql Fortinet -u user -p'password' <<EOF

LOAD DATA LOCAL INFILE '$file'
IGNORE
INTO TABLE tablename
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
EOF

echo "Completed importing '"$file"' "

done

- Harkeet Bajaj