Create a table from a CSV file with headers

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?

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?.

StackExchange Database Administrators Q#91966, answer score: 15