How to Batch Import Multiple CSV Files into a MySQL Database?

Batch Import Multiple CSV Files into a MySQL Database

Importing individual CSV files into a MySQL database can be a tedious and time-consuming task, especially when dealing with a large number of files. This issue is particularly prevalent for users with limited time or access to automated tools.

The Solution: A Shell Script for Batch Import

To address this challenge, a straightforward solution is to utilize a shell script that automates the import process for multiple CSV files. Here's a sample shell script that can be used to import CSV files into a MySQL database:

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

Explanation

This script performs the following actions:

1. Change directory to the folder containing the CSV files.
2. Iterates through each CSV file with the *.csv wildcard.
3. For each CSV file, executes the MySQL command using the LOAD DATA LOCAL INFILE statement to import the data into a specific database and table.

Usage

To use the shell script, follow these steps:

1. Save the script to a file, such as import_csv_files.sh.
2. Edit the script to replace "yourdirectory" with the path to the directory containing the CSV files.
3. Replace "yourDatabase" with the name of the database you want to import the data into.
4. Replace "yourtable" with the name of the table you want to create or update with the CSV data.
5. Run the script by executing sh import_csv_files.sh from your terminal or command line.

This method effectively automates the import process and eliminates the need for manual operations, saving you significant time and effort.

The above is the detailed content of How to Batch Import Multiple CSV Files into a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!