I did a bit of tinkering, and I just can't stop Excel from converting 001 into a number column and stripping the leading zeros.
So I googled, and here's an alternate idea:
Data | Get External Data | Import Text File
This will lead you through a 3 step wizard, during which you can specify comma separated columns, and apply 'text' format to all of them (or just the ones with leading zeros).
Finally, it will import your csv properly.
Here's the magic: Save this file as an Excel template, and provide the template to your end users (probably as a download link). They will need to download this template once.
Then when they want updated data, they can download the latest csv file. When they open the template... they are asked if they'd like to refresh the data from the file. They can browse to where the csv file was saved, and Excel will populate the 'template' with the csv data.
There is even a toolbar that appears with an exclamation point. Clicking this will refresh the spreadsheet from that file again. So to refresh would be a two step for your users... re-download the csv file, and click the exclamation in Excel.
There is also an option to run a web query from excel. You might want to investigate this as well. You could supply two different version for different users. A csv file, plus a very simple HTML web page, containing a simple table layout of your data. The same steps would ensue... save it as a template, and when they open the template, excel would go to the web and get your data.
Also, you can create a macro to drop that file onto your template. I export a table of prices(Natural Gas at different locations accross NA) from a database and check it back to the originals to make sure everything is up-to date before the stuff runs at night. For some weird reason excel read all my dates as the year 2002, so I surrounded them in quotes on my export, then use Lookups to match everthing. So, I have a template Sheet 1 which is formated the way I want, Sheet 2 looks all nice for anyone to check. I've got a macro that copies the daily csv file onto Sheet 1, paste values, so all the formats stay the same. Works great!
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com