Avoiding CSV import errors: Three practical tips for Excel uploads across regions

ayzoha

Author: Ayesha Zoha, Certified Master Anaplanner and Manager at Kearney.

One of the most common issues I have found during end user training or UAT is the difficulty in uploading data from an Excel csv. This usually arises because users need to upload small but detailed datasets from existing systems. Even with multiple integrations, data hubs and connectors set up, we still need the ability to upload smaller data sets through an Excel upload.

The issue comes up when users sitting in various geographical locations have different Excel settings (number formats, decimal formats, thousand formats, column separators etc). Because of these regional differences, CSV import actions must be configured correctly to avoid data loss or incorrect mappings.

Here are my top three tips for smooth, consistent uploads across regions, that architects and model builders can follow:

1. Structure of the file: It’s very important that before you create an action, the csv file needs to be in the right structure and format.
   1. Create an Excel file with Column headers and format the Columns if needed (in final format that’s needed).
   2. Save this as xlsx on a SharePoint or local system. This will be the export template which the users can download/refer for creating their file in the csv format.
2. Importing data (based on column separator)
   1. CSV file with comma (,) as column separator (Usually followed in India, UK, USA etc)
      1. Save the above file as csv with comma Delimiter, with data. Check the system settings of your desktop to confirm this. (Refer SS1)
         If you want to change the Excel format - Use Control Panel from the Start - Search for region - Additional settings - check the List operator with; or,
      2. Create an Import action to upload this data into the corresponding module using this file. Anaplan shows the screen below with the headers and column separators matched to the right format. (Refer SS2)
      3. If the column separator is incorrect (e.g., semicolon instead of comma), all data may appear in one column. This indicates a separator mismatch. (Refer SS3)
   2. CSV file with semicolon (; ) as column separator (Usually followed in EU)
      1. Save the above file as csv with semicolon delimiter, with data. Check the system settings of your desktop to confirm this. (Refer SS4)
      2. Create an Import action to upload this data into the corresponding module using this file. Anaplan shows the screen below with the headers and column separators matched to the right format. (Refer SS5)
      3. If data appears incorrectly mapped, it usually means the separator is set to comma instead of semicolon. (Refer SS6)
3. UX and end user training
   1. The above steps help you differentiate when building the imports for various formats. In case of multiple regions using the same data imports, create multiple actions and corresponding processes for the same and publish the multiple processes on the board with appropriate instructions.
   2. Have a training session for users from various regions to explain how the two processes work and what the implication is.
   3. The same format changes can be done for the decimal format, and multiple actions can be created
      

CSV imports may look simple, but regional Excel settings can easily lead to data issues if they’re not handled correctly. By standardizing file structures, configuring imports based on column separators, and creating region-specific actions where needed, model builders can ensure accurate and consistent uploads. Clear user instructions and targeted training complete the picture and help avoid errors at scale.

Questions? Leave a comment!

Tiffany.Rice

Thank you for sharing, the nuances of .csv files can certainly be frustrating! I'd like to share a couple more tips that I learned the hard way.

The file format can be a "gotcha" if your data contains text. Files saved in Excel with 'Comma Separated Values' format will use ISO encoding which is limited to Western European Alphabets (Latin based). If the file contains any special characters, this can cause the data to become inadvertently modified via the file encryption and non-supported characters become garbled. To avoid this issue, files can be saved with the CSV UTF-8 file format which supports non-English languages.

Another "gotcha" is the formatting updates that occur in Excel when a .csv file is opened and then resaved. Upon opening a file, Excel natively tries to curate the data for the user. A common example, is dropping leading zeros if Excel interprets the column as a Number format . When importing data into Anaplan, users should exercise caution when reopening and modifying .csv files to ensure the data remains in tact. Here is a helpful article which explains how .csv data can be viewed in https://support.microsoft.com/en-US/Excel/get-started/import-or-export-text-txt-or-csv-files

Cheers!