I often use CSV format to transport data, largely because its text nature makes the data extremely portable across different platforms and applications. Yesterday when a colleague opened a CSV file from me using Excel, one of the fields I provided was displayed incorrectly. Two examples of the troubles Excel are listed below.
| Value Provided in CSV | Shown by Excel |
| 0E36 | 0 |
| 0055 | 55 |
In the first case, Excel thought the value was a scientific notion; in the second case, Excel treated it as a number. In both cases, they were both wrong.
This issue also happens frequently when displaying ZIP codes for addresses in the United States, eg. Excel showing the intended “04001” as “4001”.
Because this is an Excel-specific behavior, I consider the following fix to be a work-around rather than a permanent solution; nevertheless, it works well for me. Instead of a simple CSV format as I had originally:
“aaa”,”bbb”,”ccc”,“0055”,”ddd”
I inserted an equal sign in front of it. This forces Excel to think that I am writing a formula that outputs a string, thus solving the Excel issue.
“aaa”,”bbb”,”ccc”,=”0055″,”ddd”