Re: Excel data to mysql database

[David West]

There are several options as indicated by the Devart Team. Loading Excel files directly into mysql will generally need to be done using some kind of Migration Tool as mentioned.

Alternatively, you could convert (i.e., save as) the Excel file into a csv or txt file. You may need to modify the data set in the csv file depending on your data types and use of the data. For example, you may need to format dates as yyyy-mm-dd and numerical values as "number" without the comma and finally, you may have to find and replace empty cells with a blank space using Excel's find-replace function. Otherwise, see the simple example below.

The Load Date Infile ...will depend on your permissions..check with admin.

Create table load_csv_table(
Field1 varchar(50),
Field2 varchar(50),
Field1 varchar(50));

Load Data Infile 'load_csv_file1.csv'
Into load_csv_table
Fields Terminated by ','
Optionally Enclosed by '"' #this may or may not be required
Lines Terminated by '\r\n' #this may or may not be required
Ignore 1 Lines ;#if you have a header row that you don't need to insert