Advantage to parsing Excel Spreadsheet data vs. CSV?

Question

I have tabulated data in an Excel spreadsheet (file size will likely never be larger than 1 mb). I want to use PHP to parse the data and insert in to a MySQL database.

Is there any advantage to keeping the file as an .xls/.xlsx and parsing it using a PHP Excel Parsing Library? If so, what are some good libraries to use?

Obviuously, I can save the .xls/.xlsx as a CSV and handle the file that way.

Thanks!

Answer 1

If you are just after the values, I would save it as a CSV. This is much easier to parse programatically, especially if you are trying to do this on a non-windows box.

That being said, there will be information lost in the export to CSV. It will only save the values of the cells - not their formatting information, formulas, etc. If you need to use that information, you're better off doing this straight from Excel.

Here is a PHP Excel Reading library. If you decide to read Excel files directly, this may help get you started.

Answer 2

If your excel files contain strictly data and contain no formulas, scripts, macros and etc., I would say parsing through Excel will only add development overhead, and will potentially slow down processing. It would probably be best to convert the files to CSV in this case.

Also consider that MySQL's 'LOAD DATA INFILE' command can be used to import entire CSV files into a table, this can potentially further uncomplicate matters for you.

Answer 3

Remember that if you're importing the csv file directly into Mysql, that you may have problems with the date format (as Mysql uses a different date format to Excel). I find it easier to change the date fields in Excel first (to format yyyy-mm-dd) prior to saving as a csv file. Edit: Although I've not used it myself, others have recommended Navicat as a very good tool for converting Excel spreadsheets or Access data into Mysql databases. May be worth a look.

Answer 4

With Office 2003 there's an XML format called SpreadsheetML which is a bit in-between XML and Excel. I've considered using this format to import/export data to a web site but the format turns out to be a bit complex. Internally, this format turns all references into relative references. (Relative from the current location.) Worse, some cells have an index, thus you might see a row with only two cells, but the second cell might be 6 columns away from the first cell. (In which case Index=5.) Basically, if you want to use the Excel format, you will need to have a good way to calculate the position of each cell and know how to translate the references in the cells in a proper way. If you're onlyinterested in the data, CSV would be much, much easier to implement. As an in-between solution, you could define an XML schema and add an XML mapping to your spreadsheet to export the data to an XML file. It's more complex than CSV i9mport/export, but also a bit more robust. But the Excel or Excel XML formats themselves are horrible to implement. (Or just a nice challenge, if you're a real XML expert.)

Answer 5

when you provide a way for customers to upload excel/csv files, you should consider that

1. CSV files will only export one sheet
2. Having multiline cells will make the CSV parsing complicated
3. You cannot easily detect corrupted/incomplete CSV files
4. CSV files do not include formatting

Besides from that, importing CSV is a lot easier than importing XLS.

Answer 6

worth a look: dbTube.org graphical Excel/PHP/mySQL importer.