Business Central Simple Tip #4: Exporting Leading Zeroes to Excel and then CSV- It works fine!

by Steve Endow

This may seem obvious to some, but I've learned through many painful lessons that unless I actually test a scenario and see for myself that it works exactly as desired, it can be dangerous to make assumptions about software functionality and offer promises to customers...

ERP users regularly need to export data to CSV files, and then send or use those CSV files somewhere else. 

A common problem with working with CSV files is that leading zeroes can sometimes be removed by Excel.  This typically occurs when a user opens an existing CSV file, makes a change, and then saves the CSV file.  During that CSV editing process, Excel will typically remove all leading zeroes from field values.

A customer recently asked us to create a custom file export to send to their bank.  They need a list of payments created in Business Central, as well as all of the purchasing invoices that were paid by that payment--essentially a remittance file.

To keep things simple at first, we're looking to create a custom Business Central List Page that has all of the fields the customer needs for their bank.  A user can then just click on the Open in Excel action from the list page to save the data to a file.

Open in Excel from a List Page

The user can then open the Excel file and Save As to a CSV file.  It's a few manual steps for now, but we can always automate the process in the future if necessary.

When we considered the idea of using Open in Excel, and then Save As CSV, one immediate concern I had was retaining leading zeroes on field values.  

Those Pesky Leading Zeroes

Sure enough, the customer had Vendor Numbers and Vendor Invoice Numbers with leading zeroes.  And US ZIP Codes can also have leading zeroes.  So I wanted to make sure that the Open in Excel -> Save to XLSX -> Open Excel file -> Save as CSV process retained all of the leading zeroes.

In short:  Yes, all of the leading zeroes are retained when you use Business Central Open in Excel.

I created a test Vendor Number "00010" with a ZIP Code of "01122".  From the Business Central Vendors list page, I clicked Open in Excel, then saved the XLSX file.

When I open the XLSX file in Excel, the leading zeroes are retained.

Leading Zeroes Are Retained in the XLSX File

You can see the small triangle indicators in the upper left corner of the No. and ZIP Code cells indicating a formatting warning.

The warning confirms that the column contains "Number Stored as Text", and Excel is eagerly wanting to convert those columns to numbers.  Thankfully, it does keep the values as Text, thus retaining the leading zeroes.

But what happens if we do Save As CSV?  Will Excel behave and properly output the values as text?

Will The Zeroes Save???

Thankfully, yes, Excel does faithfully save the leading zeroes to the CSV file.

Leading Zeroes!

After performing this test, I was happy to confirm for myself that leading zeroes survived the journey from Open in Excel, all the way through to Save as CSV.

Despite this good news, we will still need to warn the customer that they cannot open the resulting CSV file in Excel to edit it, as that will remove the leading zeroes.  In this particular case, the customer should never be editing the file, so that should not be a concern.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube, or through these links:  links.steveendow.com