"Just upload a CSV", says the developer to the scared little product manager. Sure, it may be easier for any script to parse a CSV file than an Excel file, but what about your customer? Isn't the whole concept behind a "script" to eliminate needless, repetitive work?
If you customer needs to export their Products Excel Sheet to a CSV file each time they change a price or part number, they are wasting time, and possibly introducing errors into the data.
Of course, the challenge for us developers is that the Excel data format is far more complex than the format for CSV file. On the other hand, the format is at least somewhat consistent, due to it's proprietary nature.
A CSV file can be found with single quotes, double quotes, no quotes, commas embedded in the values, quotes embedded in the values, and a whole bunch of other things that make parsing difficult. At this point, you are saying, "my development language offers a simple CSV parsing function, which handles all of these details". But that begs the question, if you are using a built-in or 3rd party function to parse CSV files, why not do it for Excel files instead.
Some languages offer this capability built-in, others through 3rd party libraries, but generally speaking, the capability is available for all of the major web scripting languages.
After demoing this capability in a few languages, I've found ColdFusion's built-in Excel spreadsheet management functions to be the easiest to use (and yes, they work on a linux installation).
Before I show example code, if you don't have ColdFusion at your disposal, you will want to consider whether obtaining a cheap ColdFusion account at GoDaddy or elsewhere is a viable option for you. If not, read no further, because you will fall in love with how simple this process is with ColdFusion, and be torn that you can't make use of it.
If you decide to get a shared ColdFusion hosting account, just for the process of converting Excel sheets to usable data, there's a method that might work for you. If your primary database is accessible remotely, you have your file upload form post to the ColdFusion script on your shared hosting account. This script will update your database remotely, then return the user back to the original server.
I won't keep you waiting any longer. The following sample code reads an Excel file from the server, then outputs the results in a table:
<cfspreadsheet action="read" src="/src/mydata.xls"
query="products"
columnnames="title,description,price" />
<table>
<cfoutput query="products">
<tr>
<td>#title#</td>
<td>#description#</td>
<td>#price#</td>
</tr>
</cfoutput>
</table>
Can anyone comment and tell me there's a development language out there with an easier solution than that?!
PHP has PHPExcel, Python has Python-Excel, etc. But, neither compare in simplicity to ColdFusions <cfspreadsheet> tag. That's besides the moral of this post though, which is: Make your customer or client's life easier, and allow them to upload Excel spreadsheets. The extra effort on your part is minimal.