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:

MySQL Shell - schema_utils.createFromCsv

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:

MySQL Shell - schema_utils.createFromCsv pk_auto_inc

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:

MySQL Shell - importTable

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

Select

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

Invisible Primary Key

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

schema_utils.create_from_csv without column name

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 !