如何将多个 csv 文件导入 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8538995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Import Multiple csv files into a MySQL Database
提问by Laxmidi
Is there a way to import multiple csv files at the same time into a MySQL database? Some sort of batch import?
有没有办法同时将多个 csv 文件导入 MySQL 数据库？某种批量导入？
I'm on Mac OSX running a MAMP server.
我在运行 MAMP 服务器的 Mac OSX 上。
I have 185 csv files that I need to import into a MySQL table. I can import them individually using phpMyAdmin's import tab, but it would take a long time. Does anyone know if there is a better way?
我有 185 个 csv 文件需要导入到 MySQL 表中。我可以使用 phpMyAdmin 的导入选项卡单独导入它们，但这需要很长时间。有谁知道是否有更好的方法？
回答by nazar554
Use a shell script like this:
使用这样的 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
回答by konsolenfreddy
There's a little PHP script for you:
有一个小的 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");
}
See the MySQL Manual for LOAD DATA INFILEoptions which fit your documents.
有关适合您的文档的LOAD DATA INFILE选项，请参阅 MySQL 手册。
回答by Tom H
You could use a shell script to loop through the files (this one assumes they're in the current directory):
您可以使用 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
回答by Abhijeet Apsunde
I've modified Tom's script to solve few issues that faced
我修改了汤姆的脚本来解决面临的几个问题
#!/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
load data local infileinstead ofload data infile: [file to be loaded was local to mysql server]- Added delimiter switches to match my data.
--local-infileto enabled local data load mode on client.
load data local infile而不是load data infile：[要加载的文件是mysql服务器本地的]- 添加了分隔符开关以匹配我的数据。
--local-infile在客户端启用本地数据加载模式。
回答by Wasim A.
For windows User use this batch
对于 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 -> Table
- IGNORE -> Ignore duplicate insert and on error keep continue
- ~dpnxf ->
dfor drive letter,pfor path to file,nfor filename,xfor extension and f is file variable
- email_us -> DB
- 业务 -> 表
- IGNORE -> 忽略重复插入并在出错时继续
- ~dpnxf ->
d驱动器号，p文件路径，文件n名，x扩展名，f 是文件变量
Steps: - Put that batch file in directory where all multiple csv files exist and named it as something.bat - run cmd.exe as adminstrator and call that something.bat file and enjoy importing...
步骤： - 将该批处理文件放在所有多个 csv 文件存在的目录中，并将其命名为 something.bat - 以管理员身份运行 cmd.exe 并调用该 something.bat 文件并享受导入...
回答by Melvita
i had the same task to do with a lot of CSV files and create one table by CSV, so here is my script that i use in local under XAMP.
我有同样的任务来处理很多 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>';
回答by Harkeet Bajaj
Using following shell script:
使用以下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
回答by citynorman
In python you can use d6tstackwhich makes this simple
在 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')
It also deals with data schema changes, creates table and allows you to preprocess data.
它还处理数据模式更改、创建表并允许您预处理数据。
回答by Oskar Must
@hlosukwakha you want to use mysqlimport.
this searches for a table named like the file.
use mysqlimport -helpto find the correct parameters, but they're basically identical to mysql
@hlosukwakha 你想使用mysqlimport。这将搜索名称类似于文件的表。用于mysqlimport -help查找正确的参数，但它们基本上与mysql
