Create mysql table directly from CSV file using the CSV Storage engine?

"[Convert CSV to SQL][1]" helped me. Add your CSV file and you are good to go.

[1]:

[To see links please register here]

In addition to the other solutions mentioned Mac users may want to note that SQL Pro has a CSV import option which works fairly well and is flexible - you can change column names, and field types on import. Choose new table otherwise the initial dialogue can appear somewhat disheartening.

[Sequel Pro][1] - database management application for working with MySQL databases.


[1]:

[To see links please register here]

If someone is looking for a PHP solution see "[PHP_MySQL_wrapper][1]":

$db = new MySQL_wrapper(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();

// this sample gets column names from first row of file
//$db->createTableFromCSV('test_files/countrylist.csv', 'csv_to_table_test');

// this sample generates column names
$db->createTableFromCSV('test_files/countrylist1.csv', 'csv_to_table_test_no_column_names', ',', '"', '\\', 0, array(), 'generate', '\r\n');

/** Create table from CSV file and imports CSV data to Table with possibility to update rows while import.
* @param string $file - CSV File path
* @param string $table - Table name
* @param string $delimiter - COLUMNS TERMINATED BY (Default: ',')
* @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"')
* @param string $escape - ESCAPED BY (Default: '\')
* @param integer $ignore - Number of ignored rows (Default: 1)
* @param array $update - If row fields needed to be updated eg date format or increment (SQL format only @FIELD is variable with content of that field in CSV row) $update = array('SOME_DATE' => 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' => '@SOME_INCREMENT + 1')
* @param string $getColumnsFrom - Get Columns Names from (file or generate) - this is important if there is update while inserting (Default: file)
* @param string $newLine - New line delimiter (Default: \n)
* @return number of inserted rows or false
*/
// function createTableFromCSV($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\r\n')

$db->close();

[1]:

[To see links please register here]

you can use this bash script

[convert.sh](

[To see links please register here]

)

and run

./convert.sh -f example/mycsvfile.csv

This is my script to load a list of CSV files into MySQL database including creation of tables based on the first line of the files:

#!/bin/bash

CREDENTIALS="--user=root --password=password --host=localhost --port=3306"
CSVOPTIONS="$CREDENTIALS --local --delete --lock-tables --fields-terminated-by=; --fields-optionally-enclosed-by=\" --lines-terminated-by=\n"
FOLDER='my/folder'

files=(
foo.csv
bar.csv
)

for i in "${files[@]}"
do
# get table name from file name
TABLE="$(basename -- $i)"
TABLE="${TABLE%.*}"

# create the table
COMMAND="DROP TABLE IF EXISTS $TABLE; CREATE TABLE $TABLE ( $(head -1 $FOLDER/$i | sed -e 's/;/ varchar(255),\n/g') varchar(255) );"
mysql $CREDENTIALS $DB -e "$COMMAND"

# fill in data
mysqlimport $CSVOPTIONS --ignore-lines=1 $DB "$FOLDER/$i"

done