Exporting .xls to .csv issues

Question

I have been trying to import this excel file my mysql database, and it has been rough. There are around 7,000 rows in the .xls file, but when I export it to .csv and parse it with PHP I get one of two issues:

• Excel does not escape anything, ie. the commas in each row split the data
• When I change all commas to * then export for some reason excel changes a whole bunch of lines to a bunch of pound signs #####.

(Is there a reason it does this?)

I can upload the .xls somewhere if need be.

Answer 1

As a suggestion, if you are dealing with cells that do contain commas which are widespread, an easy solution would be to export from Excel in a Tab-Delimited format (TXT), or yet alone any format that contains a character you would not normally encounter.

I've found it to be a pretty simple way to take care of many of the troubles associated with exporting from Excel and importing into a database medium.

Answer 2

Figured it out!

Turns out it was Excel's fault. I checked the .xls before opening it in Excel, not ###. But after saving it in Excel they appeared.

So I opened it in Numbers (part of iWork), changed all , to *, and all quotes to % (these never appeared in any line. Exported it to .csv, then changed them back when importing with PHP.

Worked great. :-)