Trang chủ‎ > ‎IT‎ > ‎Database‎ > ‎

How to Import Multiple csv files into a MySQL Database

1. Linux - Shell scripts
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
#!/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-infiledatabase
done
----------------------------------------------------------------------------------------
for f in /var/lib/mysql/[insert name of database here]/*.csv
do
    mysql -e "LOAD DATA INFILE '"$f"' INTO TABLE [nameoftable] 
      FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES" 
      -u [username] --password= [password] [name of database]
echo "Done: '"$f"' at $(date)"
done
2. PHP script
Use a shell script like this:
 #!/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");
}
3. Windows script
Use a shell script like this:
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
)
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
4. PHP code
 <?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>';

LOAD DATA LOCAL INFILE 'malefirst.txt, femalefirst.txt, allfirst.txt, allfirst.txt' 
INTO TABLE fnames 
 (mal, fml, unk, cpx);

MySQL - How to import data from a CSV file?

August 9, 2018

1. Using mysqlimport from the command line

mysqlimport -h host_name -u user_name -p --ignore-lines=1 --fields-terminated-by=, db_name /path/to/file.csv 

Read more of the syntax from the documentation.

2. Using SQL query

LOAD DATA INFILE 'path/to/file.csv' 
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

If it’s a local file, use LOAD DATA LOCAL INFILE instead.

Note: You might run into an error where it said:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This is because of the server setting, the --secure-file-priv option starts with the server and limits from which directories you can load files using LOAD DATA INFILE.

And you will have some option to fix:

  • Move your file to the directory specified by secure-file-priv.
  • Disable secure-file-priv. This must be removed from startup and cannot be modified dynamically. To do this check your MySQL start up parameters (depending on platform) and my.ini. Then restart your server.
  • Use a GUI.

3. Using TablePlus GUI Tool

  • Right-Click on the table name from the right panel
  • Choose Import CSV
  • Choose CSV file
  • Review column matching and hit Import

All done!

Import CSV file in TablePlus GUI Tool MySQL


Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus free.

TablePlus GUI Tool MySQL



Comments