When importing data through a CSV file, the user finds that leading 0s are being dropped in the integration, causing dimension codes or values to be incomplete in the Data Warehouse.
Always preview data prior to running the integration. If the leading 0s do not appear in data preview, follow the below steps to add the leading 0s.
- Data can be updated via Excel, as Excel does have functionality for easier maintenance and formatting.
- Create the CSV file with data in Excel
- Save the file in .xlsx format
- Cast leading 0 codes as TEXT format
- Save the file again as .csv (Comma delimited) format
Upon closing out the Excel file, a prompt will ask to save the file again. Do not hit save and simply exit Excel. If the file is saved twice, the leading 0s will be lost.
Important: Do not re-open the CSV file in Excel to prevent the leading 0s from being lost. If necessary, verify changes by opening the CSV file via a text editor (i.e. Notepad).
Following the above steps, you should see leading 0s in the source data preview in integration mapping.