How to Import Multiple csv files into a MySQL Data

问题:

Is there a way to import multiple csv files at the same time into a MySQL database? Some sort of batch import?

I'm on Mac OSX running a MAMP server.

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?

回答1:

Use a shell script like this:

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

回答2:

There's a little PHP script for you:

#!/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 INFILE options which fit your documents.

回答3:

You could use a shell script to loop through the files (this one assumes they're in the current directory):

#!/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

回答4:

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

1. load data local infile instead of load data infile : [file to be loaded was local to mysql server]
2. Added delimiter switches to match my data.
3. --local-infile to enabled local data load mode on client.

回答5:

For windows User use this batch

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 -> d for drive letter, p for path to file, n for filename, x for extension and f is file variable

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

回答6:

@hlosukwakha you want to use mysqlimport. this searches for a table named like the file. use mysqlimport -help to find the correct parameters, but they're basically identical to mysql

回答7:

In python you can use d6tstack which makes this simple

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.