csm232s csm232s - 1 year ago 89
MySQL Question

Import Excel file into MySql (not a CSV)

I'm having issues importing a CSV file into my MySQL database because of all the quotes an commas. If I remove them from the CSV, the import works fine. However, my data is ruined. So I'm looking for a way to import an Excel spreadsheet file into a MySQL database.

Is there a way to import an Excel spreadsheet (or another format I can save to) directly into a blank MySQL database table?

Answer Source

IIRC Excel files (*.xls etc) cannot be imported in MySQL as they are a binary formatted file. However you could try using a TSV (tab delimited) thereby removing your comma issue. Cant rememer the exact syntax but something like:

LOAD DATA INFILE 'C:/data.csv' into Table Fields terminated by '\t' lines terminated by '\n'

Alternatively you'll need to build a helper app for this.