9

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?

8

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

If you're ok with using Python, Pandas worked great for me (csvsql hanged forever and less cols and rows than in your case). Something like:

from sqlalchemy import create_engine
import pandas as pd

df = pd.read_csv('/PATH/TO/FILE.csv', sep='|')
# 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, engine, index=False)
  • Where do you define dwh_engine? Is this a typo and you meant engine? – joanolo Mar 28 '17 at 7:00
  • Yes it should be engine! Corrected the answer thanks for spotting – ivansabik Mar 28 '17 at 21:37
  • to_sql takes up too much time if the number of rows is high. For us, around 36000 rows took around 90 mins. A direct load statement was done in 3 seconds. – mvinayakam Dec 3 '18 at 10:32
0

You need to generate a CREATE TABLE based on datatypes, size, etc of the various columns.

Then you use LOAD DATA INFILE ... FIELDS TERMINATED BY '|' LINES TERMINATED BY "\n" SKIP 1 LINE ...; (See the manual page for details.)

Do likewise for each csv --> table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.