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.

  • How are the files being opened? And are all users on English versions of Excel? – cybernetic.nomad Mar 13 '18 at 15:36
  • possible duplicate of superuser.com/questions/238944; have a look particularly at superuser.com/a/1222081 – AntoineL Mar 13 '18 at 15:50
  • The user has no option to save the file. When selecting Export, it automatically opens Excel. At that point the User see version 1 or 2 above. So far all feedback has been from English versions of Excel. – bobzorno Mar 13 '18 at 16:01
  • I did not see question 238944 listed above, but the recommendation is to open from a saved location as text which we are unable to do since the export process opens the file in excel with no option to save first. – bobzorno Mar 13 '18 at 16:03
  • Was able to change the export to have a save option. Now the solution in superuser.com/questions/238944 will work albeit a little difficult for the average user. – bobzorno Mar 13 '18 at 16:30
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.
  • @bobzorno : Glad I could help. If you feel the question has been answered, you can mark the answer as accepted. This way this Q&A becomes easier to find for other users, and I earn some reputation points :) thx – Wouter Apr 27 '18 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.