1

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?

csm232s
  • 1,660
  • 4
  • 32
  • 60

2 Answers2

1

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.

Simon
  • 9,197
  • 13
  • 72
  • 115
0

@Simon is right - You can't import XLS directly into MySQL.

When I run into this problem, I use the SED via Terminal. to remove the quotes. I'm sure there is a more elegant way to do it, but it works for me and it doesn't take much time. 

sed  ‘s/"//g’ filename.csv > newfilename.csv

Then, import that newfilename.csv.

Alternatively, you can open the CSV in a text editor and find/replace the quotes.

mikebmassey
  • 8,354
  • 26
  • 70
  • 95