How to preserve leading zeros when exporting reports from Bold BI to CSV and opening in Excel?
When exporting reports to CSV format from Bold BI, users may find that leading zeros are not preserved when the file is opened in Microsoft Excel. This issue is not due to the Bold BI application but rather a setting within Excel itself.
Detailed Explanation
Microsoft Excel has a default behavior of trying to interpret and format data, which can result in the removal of leading zeros when opening a CSV file. This is because Excel treats numbers with leading zeros as integers and automatically removes the zeros, assuming they are not necessary.
Steps to Retain Leading Zeros in Excel
To prevent Excel from removing leading zeros when opening a CSV file, follow these steps:
-
Open the Excel application and click on the
Fileoption in the application menu.
-
From the
Moretab, selectOptions.
-
Click on the
Dataoption and ensure that the box forEnsure automatic data conversionis checked.
Once you’ve completed these steps, you will receive a notification every time the Excel application automatically converts data.
By enabling this option, Excel will prompt you with a notification when opening a CSV file, asking if you want to remove the leading zeroes. To retain the leading zeros, select the Don't Convert option when prompted.
Ensuring Consistent Behavior for All Users
It is crucial to confirm that all users who need to preserve leading zeros are aware of the correct option to select when prompted by Excel. If a user selects Don't notify me about this choice, the pop-up notification will not appear in future instances, which may lead to the removal of leading zeros without the user’s knowledge.
Please note that the steps provided above are based on the latest available information (Excel v16.0) and may vary depending on the version of Excel in use. Always refer to the official Microsoft Office Support for the most up-to-date guidance.
Conclusion
The preservation of leading zeros in CSV files when opened in Excel is controlled by Excel’s settings and not by the Bold BI application. By following the steps provided, users can adjust their Excel settings to ensure that leading zeros are retained when opening CSV files exported from Bold BI.
