Using MySQL Shell to generate table’s create statement from CSV file

Recently, I wrote several articles on how to load data from CSV files to migrate from different databases to MySQL Database Service:

• How to import data from PostgreSQL to MySQL Database Service
• How to import data from Amazon Redshift to MySQL Database Service
• How to import data from Microsoft SQL Server to MySQL Database Service
• Import data from Amazon Aurora PostgreSQL to MySQL Database Service in OCI

We saw that the most complicate is to write the CREATE TABLE statement that matches the data. I also received some questions about how to generate the table’s definition when only the CSV file was available.

I wrote a MySQL Shell plugins that helps generating a CREATE TABLE statement from a CSV file: schema_utils.createFromCsv()

Let’s have a look to how it works:

This new method accepts the following parameters:

• filename: the CSV file path.
• delimiter: the field delimiter.
• column_name: (boolean) – use the first row as column name.
• first_as_pk: (boolean) – use the first column as Primary Key.
• pk_auto_inc: (boolean) – the PK will be defined as int unsigned auto_increment. If first_as_pk is disabled, a new column will be added but invisible.

Let’s have a look at examples using some of these options:

In the example above, we don’t one to use the first column as Primary Key but we want to have an auto_increment as Primary Key. Therefor, an invisible Primary Key is created to be able to load the data into the table:

We where able to load the csv file and we can retrieve data from it:

As you can see the new Primary Key is invisible bit we can of course retrieve it:

It’s also possible to not use the first row as column names:

Once again, it’s very easy to extend MySQL Shell. If you do and you want to share your plugin(s), don’t hesitate to submit a Pull Request to my GitHub repository.

Enjoy MySQL and MySQL Shell !