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

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?

This question is related to mysql database csv storage-engines

The answer is


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.


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

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.


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));"

you can use this bash script

convert.sh

and run

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

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.


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.


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.

MySQL Workbench image

More from the MySQL Workbench 6.5.1 Table Data Export and Import Wizard documentation. Download MySQL Workbench 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 - database management application for working with MySQL databases.


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


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

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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to csv

Pandas: ValueError: cannot convert float NaN to integer Export result set on Dbeaver to CSV Convert txt to csv python script How to import an Excel file into SQL Server? "CSV file does not exist" for a filename with embedded quotes Save Dataframe to csv directly to s3 Python Data-frame Object has no Attribute (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape How to write to a CSV line by line? How to check encoding of a CSV file

Examples related to storage-engines

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