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

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.

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.csvecho "create table $1 ( "head -1 $1 | sed -e 's/,/ varchar(255),\n/g'echo " varchar(255) );"

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.