Enable primarily uses CSV (comma-separated value) files to facilitate a wide range of data imports and exports. However, there are some common issues which you might encounter when using Excel to manage any CSV files.
This article will guide you through:
- What is a CSV file?
- How data is reformatted in Excel
- When to look out for data reformatting
- How to load a CSV into Excel without data reformatting
- How to prevent eliminating leading zeroes
Looking for something else?
- Need help understanding data formats for imports? Learn more about managing data here.
- Alternatively, see our Help Center for more on trading programs.
What is a CSV file?
A CSV file contains values separated by commas. In Excel, these are processed and displayed in table format and so you would see:
If this same file was instead opened using a text editor (such as Notepad or Visual Studio Code) then these values are displayed as a list, separated by commas:
Tip: Viewing the CSV in a text-editor format is a great way to identify erroneous whitespace issues which are much more difficult to see in Excel.
How data is reformatted in Excel
Excel is a powerful visualisation tool for managing data tables and allowing users to efficiently add, edit, and delete data. However, automatic assignment of data types to cells based on cell values can occur, especially with dates. This is illustrated below where a date has been added to the example CSV:
When opened in Excel, the date has reformatted from YYYY-MM-DD format to DD/MM/YYYY format, with the former being accepted by Enable:
Once this is saved, this alters the original data. This is visible when reopened in a text editor:
This is because Excel has applied its default date format to the cell.
When to look out for data reformatting
Attention should be paid to any data in CSVs being imported to or exported from Enable. Particular care should be taken when:
- Uploading transactions: Transaction dates and interface dates can both be impacted by date reformatting.
- Dimension item or trading partner references have leading zeroes (e.g., 00001234): Number reformatting can remove these.
- A dimension item template with Populate with existing ticked is downloaded: Excel can apply alternative number or date formats to text references. Extra care should be taken here, as while incorrectly formatted dates in transaction files will be rejected, reformatted dimension references will be accepted and may result in duplicate dimension items.
Learn more about how to handle data for these scenarios in the article sections below.
How to load a CSV into Excel without data reformatting
Excel can load CSV files without editing the data. This is useful for dates or when codes/references begin with zeros, which need to be preserved in order to match references in the Enable channel.
Step 1: To import Text or CSV files into Excel, navigate to the Data tab and click Get Data (Power Query).
Step 2: Select Text/CSV from the data source options displayed.
Step 3: Select the relevant file in your documents and click Next.
Step 4: Select the Transform data button in the bottom right of the screen.
Step 5: Click on the symbol next to the header of each column to display the current data type. From this drop-down, select Text to ensure that all data is processed as text. While this article focuses on the Date column, this logic can be applied to all columns to ensure no reformatting of references.
Step 6: Click on the Close & load button in the top left to display your data in Excel.
How to prevent eliminating leading zeroes
Step 1: Review the references for the dimension (e.g. products) - do any numerical references not begin with zeroes that should? For example, the below image displays a Product Reference as 1, whereas it should be displayed as 00001.
Step 2: Select the column(s) and/or cell(s) where the leading zeroes should be reinstated. Right-click on the selection and select Format Cells from the drop-down menu. The box below will be displayed.
Step 3: Under Category, select Custom.
Step 4: Under Type, select 0 from the list.
Step 5: In the cell between Type and the list, type in as many zeroes as there are digits in the desired reference. For example, the reference described above is 00001, therefore 00000 should be entered into the cell. This ensures the reference is five digits, replacing the last digits with the relevant integers present in the reference.
Step 6: In the Format Cells box, click OK. You will be returned to the spreadsheet where the reference will now have been correctly reformatted.
What’s next?
Ready to import your data into Enable? Learn more about managing data here.