Create MySQL table from xls spreadsheet

Question

Dear all,

I wonder if there is a (native) possibility to create a MySQL table from an .xls or .xlsx spreadsheet. Note that I do not want to import a file into an existing table with LOAD DATA INFILE or INSERT INTO, but to create the table from scratch. i.e using the header as columns (with some default field type e.g. INT) and then insert the data in one step.

So far I used a python script to build a create statement and imported the file afterwards, but somehow I feel clumsy with that approach.

Answer 1

There is no native MySQL tool that does this, but the MySQL PROCEDURE ANALYSE might help you suggest the correct column types.

Answer 2

With a VB Script you could do that. At my client we have a script which takes the worksheet name, the heading names and the field formats and generates a SQL script containing a CREATE TABLE and a the INSERT INTO statements. We use Oracle but mySQL is the same principle.

Of course you could do it even more sophisticated by accessing mySQL from Excel by ODBC and post the CREATE TABLE and INSERT INTO statements that way.

I cannot provide you with the script as it is the belonging of my client but I can answer your questions on how to write such a script if you want to write one.