Excel

disable excel autoformat when viewing csv

In every migration/ETL project we're doing, we usually generate reports so that the users and it's in CSV or text format. Most of the time, it is in CSV because non-techie users can just click on the file and it opens in excel immediately. But there lies the problem. As most of us know, Excel auto-formats the data and only significant figures are displayed for numbers. Unfortunately, most of the codes are in numbers so zeroes gets dropped left and right. 

Consider the example below. Billing Account and Transaction Id are supposed to be text. The zeroes at the end of Billing Account actually mean something just as with the zeroes in front of the Transation Id. Of course for real numbers (e.g, amount), those are insignificant so Excel drops them in the display.

disable excel auto-format sample 1

When opened in Excel, it is displayed like this. Take note of the numbers highlighted in yellow. Some leading and traling zeroes are missing. Column C(Amount) correctly shows the the number sans formatting.

disable auto formatting in excel

So how then can we force excel to show the complete numbers? There are several ways to do it. But on my case, it's important that the users do not need to do complicated steps. As much as possible, they'll just double-click the file and viola, the csv file will open up in Excel with the expected format.

Fortunately, there's a trick to do this. When writing the file, the format would be ="number text value". The equal sign is important because all the while, I've been enclosing the values with double-quote to no avail. That equal sign was missing!

So the report should look like this.

disabling csv auto format in excel

which will result to:

disable auto format of csv in excel

If I'm not very clear, trailing and leading zeroes now appear for both Billing Account and TransactionId

By the way, I got this idea from the the link below.
http://superuser.com/questions/330291/how-to-stop-excel-from-auto-formatting-and-making-it-work-like-a-number-crunchin