Missing digit in CSV File Currency Values
I searched all over and did not find the right answer so I played around until I had found it myself and now I’ll share it with you.
I had picked up a VBA coding project from a contractor who was leaving.
The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.
The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.
One of the issues the customer was reporting was that they had an expectation that currency value in exported CSV files should retain the 2 digits after the decimal point.
The application was sending the output result in a CSV file but the currency value were only retaining one digit after the decimal if the last decimal was a zero.
A value of 91.20 is coming out as 91.2, missing the 2nd digit.
This happens consistently while using Excel with CSV files but not with XLS. It drops the zero every time.
How to Save currency values in a CSV File using Microsoft Excel
- Save your CSV file in MS Excel format with a .xlsx or .xls extension.
- Update the “Format Cells” to display the column as Currency. I set the option not to display a $ for currency and 2 digits after the decimal point.
- Save this file with a .CSV extension. Your currency values with the 2 digits after the decimal point are preserved during this conversion.
That is it!
To validate it, open the CSV file with a text editor like Notepad or Notepad++ and you will see the number format with 2 digits after the decimal was retained.
Watch out! If you open the CSV file with MS Excel and save it again as CSV, Excel will wipe out your currency formatting.
Hope this helps somebody.
