Mysql – Create a table from a CSV file with headers

bulkcsvimportMySQL

I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties;

"|" delimited.
First row specifies the column names (headers), also "|" delimited.
Column names & order are not fixed.
The number of columns is not fixed.
Files are of a large size (1 mil rows / 50 columns).

In Excel this is all rather simple, however with MySQL it does not appear to be (no luck with Google). Any suggestions on what I should be looking at?

Best Answer

You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files):

Linux or Mac OS X
free and open source
sudo pip install csvkit
Example: csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql
It creates a CREATE TABLE statement based on the file content. Column names are taken from the first line of the CSV file.

To extend on ivansabik's answer using pandas, see How to insert pandas dataframe via mysqldb into database?.

Related Solutions

Sql-server – query a tab-delimited file from SSMS

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

If this activity needs to be repeated frequently, I would suggest a script to create schema.ini. Multiple files can be referenced in the same schema.ini or a separate schema.ini can be included with each text file in its own directory.

Mysql – import data from csv to thesql – good database structure

You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod. id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table) the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.

Best Answer

Related Solutions

Sql-server – query a tab-delimited file from SSMS

Mysql – import data from csv to thesql – good database structure

Related Question