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:

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 !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.