2

I have an issue with a .csv file that is an export from a user intranet site. The user simply selects 'Export' from the site and the export opens Excel automatically. The issue is that Excel handles it two different ways depending on the user and what I assume is a default setting.

Version 1: File opens in Excel with all data in column A with comma delimiters. This is the expected process. A simple text to columns which allows the user to change one column to text (part numbers have leading zeroes) and the export to Excel is perfect.

Version 2: File opens in Excel but the system somehow is recognizing the comma delimiters and does the column split automatically. This is not preferred as the item number is not changed to text so the leading zeroes are truncated.

Any ideas how to change a setting that makes version 1 happen? The solution needs to be fairly simple because there are hundreds of external users accessing this data that is specific to them.

By the way - all the users tested so far are all on same version of Excel 2010. External users will likely be on anything 2007 and newer.

Thanks.

bobzorno
  • 21
  • 4

1 Answers1

2

The Windows Region and Language settings determine which symbol is used by default as a List separator. For users where the comma is selected as the List separator, Excel will parse the CSV file as such. Likewise for users where the semicolon is set up to be the default.

This is explained in detail in this answer: How to force Excel to open CSV files with data arranged in columns

Some solutions

  • One way to fix this on the users's end would be to set the List separator to ;. Yet this is probably not very feasible.
  • Another solution would be to use a separator that is highly uncommon as a default on users system. For example #. This would cause the data to appear in column A for all users.
  • If the intent is to provide the user with Excel data, consider providing an Excel file to the user instead of a CSV file. When opening CSV files with Excel, Excel doesn't only make assumptions about the separator, but also about the encoding, data types, encapsulation... This can mess up more things than you are currently anticipating. I've written (a rather lengthy) article about this here.
Wouter
  • 1,701