Updated: 06 July 2024
Dynamically create a MySQL table which accommodates a CSV file, then import that CSV file.
#!/bin/bash
# create-table.sql
# CREATE TABLE `csv` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# PRIMARY KEY (`id`)
# );
mysql -u user -ppassword < create-table.sql
# Read column headers into an array.
IFS=',' read -ra COLS <<< $(head -n1 file.csv)
# Loop through the array and create a
# new table column for each csv column.
for COL in "${COLS[@]}"; do
mysql -u user -ppassword -e "ALTER TABLE csv ADD \`$COL\` TEXT DEFAULT NULL;"
done
# load-csv-into-table.sql
# LOAD DATA LOCAL INFILE 'file.csv'
# INTO TABLE `csv`
# FIELDS TERMINATED BY ',' ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# IGNORE 1 ROWS;
mysql -u user -ppassword < load-csv-into-table.sql