MySQL csv

by

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

Leave a comment