How to Import Multiple csv files into a MySQL Database

Solution 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

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

Solution 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

Solution 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

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

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