Sitting around work today we were working on exporting some data to excel when, of course, we ran into the age-old problem of Excel dropping leading zeros on numeric fields that should be treated as text (ISBN numbers in this case). So, if an ISBN was 0-596-00712-4 (good book by the way) it would be stored in the DB as 0596007124 and when spooled out to a CSV file and opened in Excel, the leading "0" would be decapitated leaving us with 596007124. Not good.
After trolling through forums and newsgroups, I found this thread which helped us get to an answer:
On the third day, the Lord said, "If you want to spool numeric data to a CSV file and have Excel treat it as text, you must qualify it with an equal sign (=) and wrap it in quotes!"
So our file ends up with some entries that look like this:
="0596007124", Head First Design Patterns, O'Reilly
="0735621721", Practical Guidelines and Best Practices, Microsoft Press
And the data's accuracy was kept intact, and it was good.
No data was lost or harmed in the writing of this post...
Posts
1 comments:
good information...but how do I go about appending my number with =" as a prefix and " as a suffix?
Post a Comment