In this article, I'll share a bash shell script I use periodically to import a directory of CSV files into MySQL tables. This script is most helpful when you need to process a large number of CSV files, and the tables have not yet been created. Of course you could use a GUI tool to accomplish this, but what's the fun in that?
The following script will get a list of CSV files, loop through them, add each table, add each column to the table (based on the first row), and then use the mysqlimport command to load all the CSV records. There are a few caveats though: 1. the first row of each CSV file must contain the column names; 2. it works best when your column names are simple text; and 3. your MySQL user must have permission to process files (see: File_priv).
#!/bin/bash
# show commands being executed, per debug
set -x
# define database connectivity
_db="csv_imports"
_db_user="csv_imports"
_db_password="changeme"
# define directory containing CSV files
_csv_directory="/path/to/the/csv/files"
# go into directory
cd $_csv_directory
# get a list of CSV files in directory
_csv_files=`ls -1 *.csv`
# loop through csv files
for _csv_file in ${_csv_files[@]}
do
# remove file extension
_csv_file_extensionless=`echo $_csv_file | sed 's/\(.*\)\..*/\1/'`
# define table name
_table_name="${_csv_file_extensionless}"
# get header columns from CSV file
_header_columns=`head -1 $_csv_directory/$_csv_file | tr ',' '\n' | sed 's/^"//' | sed 's/"$//' | sed 's/ /_/g'`
_header_columns_string=`head -1 $_csv_directory/$_csv_file | sed 's/ /_/g' | sed 's/"//g'`
# ensure table exists
mysql -u $_db_user -p$_db_password $_db << eof
CREATE TABLE IF NOT EXISTS \`$_table_name\` (
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
eof
# loop through header columns
for _header in ${_header_columns[@]}
do
# add column
mysql -u $_db_user -p$_db_password $_db --execute="alter table \`$_table_name\` add column \`$_header\` text"
done
# import csv into mysql
mysqlimport --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by="\n" --columns=$_header_columns_string -u $_db_user -p$_db_password $_db $_csv_directory/$_csv_file
done
exitAfter creating my shell script file, and making it executable, I executed it. Since I added the line "set -x" to the script, a lot of helpful info is shown to debug.
./import.shLastly I executed some SQL on the command line to verify the results. nice.
$ mysql -u csv_imports -pchangeme csv_imports --execute="select * from albums"
+----+-------------+---------------------+
| id | band | album |
+----+-------------+---------------------+
| 1 | band | album |
| 2 | black keys | attack & release |
| 3 | the dodos | no color |
| 4 | the xx | xx |
| 5 | surf city | kudos |
| 6 | toro y moi | underneath the pine |
| 7 | cut copy | zonoscope |
| 8 | twin shadow | forget |
+----+-------------+---------------------+