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

Question

I just learned that MySQL has a native CSV storage engine which stores data in a Comma-Separated-Value file per table.

Is it possible to create a table directly from a uploaded CSV file, something like:

CREATE TABLE USERS < PATH/USERS.CSV

where users.csv is uploaded by the user?

Arte

Not exactly an answer but you may be referring to LOAD DATA INFILE dev.mysql.com/doc/refman/5.1/en/load-data.html , the text file for which resembles a CSV structured file. – Kattegat

You can use this tool (csv-schema.surge.sh) to convert CSV to a CREATE TABLE (with schema). It tries to guess the field types (quite good). – Cool

I've found out that PostgreSQL can query CSV directly with file_fdw postgresql.org/docs/9.5/file-fdw.html – Arte

Answer 1

I just discovered csvkit, which is a set of Unix command-line tools for CSV files. I installed it on my Mac with pip install csvkit. The command was:

csvsql --dialect mysql --snifflimit 100000 bigdatafile.csv > maketable.sql

You can alternatively provide a DB connection string and it can load the table directly.

Declinature

this tool is awesome, thanks! free, made in Python, and 'just works' – Corena

On debian-based systems you can install this via sudo apt-get install python3-csvkit – Eleanoraeleanore

To import a csv-file directly to a (mysql) db do something like this: csvsql --db mysql://user:password@localhost:3306/dbschema --tables mytable --insert file.csv (you will also have to install python3-pymysql) – Eleanoraeleanore

This tool is ruddy brilliant. – Hive

I installed csvkit, run the csvsql command, but what I got is "'acsii' codec can't encode character ...". I use python 2.7, ubuntu 14.04. Can anyone give some help? really appreciated! – Unman

Consider this to be the accepted answer instead. Worked like a charm. – Ocotillo

This tool can not handle large files, and easily get OOM error. – Haematoma

Answer 2

This is not possible. To create a table you need a table schema. What you have is a data file. A schema cannot be created with it.

What you can do is check if your file has a header row, and, in that case, you can manually create a table using that header row.

However, there is a way to generate a create table statement using a batch file as described by John Swapceinski in the comment section of the MySQL manual.

Posted by John Swapceinski on September 5 2011 5:33am.
Create a table using the .csv file's header:

#!/bin/sh
# pass in the file name as an argument: ./mktable filename.csv
echo "create table $1 ( "
head -1 $1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"

Mascle

an extended version of that script was posted based on John's at en.positon.org/post/Import-CSV-file-to-MySQL – Classical

The extended version is still up en.positon.org and just CTRL+F "Import CSV file to MySQL". – Darleen

Am I correct to believe that this script creates all columns as VARCHAR(255)? If that's the case then it's not really useful. – Thickset

You are correct. Also note csv doesn't provide type information. So its not possible to use a type here. – Mascle

Was looking all over for this – Cockiness

While literally true that this is not possible with certainty, many spreadsheets and csvkit (below) assume data types pretty well when importing csv files, making schema-creation possible in most cases. And where it's not, it can still give you a mostly-there sql statement saving a ton of time compared to writing from scratch. – Borowski

Answer 3

I'm recommended use MySQL Workbench where is import data. Workbench allows the user to create a new table from a file in CSV or JSON format. It handles table schema and data import in just a few clicks through the wizard.

In MySQL Workbench, use the context menu on table list and click Table Data Import Wizard.

More from the MySQL Workbench 6.5.1 Table Data Export and Import Wizard documentation. Download MySQL Workbench here.

Prismoid

Please don't just give a link but describe how to solve the issue! Please see how to answer – Petronia

Maybe its because this is a more recent addition to MySQL Workbench, but this is a much simpler and straightforward approach than all the previous solutions. – Morgen

Is there a way to do this for multiple csv files? – Podiatry

My csv is big, about 20 MB, it took me almost 20 mins to import them into DB. at some point I thought It got stuck because the progress bar isn't changing. but it was imported eventually with few error messages. not a big deal. – Marley

What would be the equivalent code to this Table Data Import Wizard solution? – Backbreaker

Answer 4

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

Madancy

just what i need. Thanks! – Araarab

Answer 5

There is an easier way if you are using phpMyAdmin as your MySQL front end:

1. Create a database with the default settings.
2. Select the database.
3. Click "Import" at the top of the screen.
4. Select "CSV" under "Format".
5. Choose the options appropriate to your CSV file, open the CSV file in a text editor and reference it to get the "appropriate" options.

If you have problems, no problem, simply drop the database and try again.

Backsword

There is no option under Format to choose anything besides csv – Madancy

You have to rename the table, but this is easiest pma solution. – Gonidium

Says it has a 2MB limit and didn't work - so basically useless – Marsiella

Answer 6

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 - database management application for working with MySQL databases.

Member

Just found out that MySQL Workbench can do this as well, but yes for years Sequel Pro was a go-to just because it handled CSV->Table so nicely. – Styles

Answer 7

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

$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();

Vaticinate

Answer 8

you can use this bash script

convert.sh

and run

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

Carvalho

Answer 9

I adopted the script from shiplu.mokadd.im to fit my needs. Whom it interests:

#!/bin/bash
if [ "$#" -lt 2 ]; then
    if [ "$#" -lt 1 ]; then 
        echo "usage: $0 [path to csv file] <table name> > [sql filename]"
        exit 1
    fi
    TABLENAME=$1
else
    TABLENAME=$2
fi
echo "CREATE TABLE $TABLENAME ( "
FIRSTLINE=$(head -1 $1)
# convert lowercase characters to uppercase
FIRSTLINE=$(echo $FIRSTLINE | tr '[:lower:]' '[:upper:]')
# remove spaces
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/ /_/g')
# add tab char to the beginning of line
FIRSTLINE=$(echo "\t$FIRSTLINE")
# add tabs and newline characters
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/,/,\\n\\t/g')
# add VARCHAR
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/,/ VARCHAR(255),/g')
# print out result
echo -e $FIRSTLINE" VARCHAR(255));"

Rishi

Answer 10

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

Noiseless

Answer 11

This is not possible, you can however overwrite an existing table file. But be sure, that the line endings in your file are unix style (ending only with \n), not windows style (ending with \r\n), whether you are working under windows or not.

Starlike

Answer 12

I have made a Windows command line tool that do just that.

You can download it here: http://commandline.dk/csv2ddl.htm

Usage:

C:\Temp>csv2ddl.exe mysql test.csv test.sql

Or

C:\Temp>csv2ddl.exe mysql advanced doublequote comma test.csv test.sql

Rhetorician

Now, this looked cool, for us poor windows users. Unfortunately the link does not work anymore ;( – Molarity

I searched all over for a quick and dirty way to do this and couldn't find one. After seeing this comment I wrote a Windows Batch file to mimic Johns Shell script found here: positon.org/import-csv-file-to-mysql. I'll upload it to Github soon. – Damascene

Answer 13

MySQL for excel plugin can help you.

http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html

Open your CSV file in excel. You can use this plugin to export excel data into a new table of remote or local mysql server. It will analyze your data (top 100 to 1000 rows) and create a corresponding table schema.

Hertzfeld

Wow. This tool is simply amazing. I had to create a table with 99 fields and 1100 rows. This tool did some basic data validation and created the table and all the fields for me. Very nicely done. I was able to hook up to GoDaddy MySQL tables remotely. Note: this tool is only available for windows operating system. – Ponceau

The link is broken. – Watterson

Answer 14

If you're ok with using Python, Pandas worked great for me (csvsql hanged forever for my case). Something like:

from sqlalchemy import create_engine
import pandas as pd

df = pd.read_csv('/PATH/TO/FILE.csv')
# Optional, set your indexes to get Primary Keys
df = df.set_index(['COL A', 'COL B'])

engine = create_engine('mysql://user:pass@host/db', echo=False)

df.to_sql(table_name, dwh_engine, index=False)

Also this doesn't solve the "using CSV engine" part which was part of the question but might me useful as well.

Counterpoise