2

I work for a company in the United States. One of my clients is in Montreal. Whenever the client tries to download a spreadsheet from our network, she ends up with a bunch of data bunched into Column A, instead of spread out properly over multiple columns:

all data in column A

When I download the spreadsheet from the same source, I end up with the correct format:

correct format

When the client emails her downloaded spreadsheet to me, and I open it, I get a third data layout with semicolons:

client's spreadsheet, opened on my computer

Ultimately, the client needs to be able to make changes to her spreadsheet, then re-upload it to our network. All other clients are able to do this, but this client's downloaded CSV' s always reformat the data so she's unable to re-upload them.

Importantly, when the client is instructed to transform the data back to UTF-8 in Excel, the data reformats into the correct layout, but she is still unable to upload it to the network (the CSV is rejected and the error says the format is incorrect).
However, when I take her spreadsheet and transform the data by the exact same process, I'm able to upload it to the network just fine.

Question

Why is my client's CSV data laid out incorrectly when she downloads the spreadsheet?

Context information

  • My version of Excel: Office 365 (16.0.14026.20304) 32-bit

  • Client's version of Excel: Office 365 (16.0.14026.20202) 64-bit

Saaru Lindestøkke
  • 5,515
  • 8
  • 30
  • 48
user16202646
  • 23
  • 3
  • have her send you the file WITHOUT opening it on her end, and see if its still "a third view" or if its identical to yours. I'm going to guess its the later. – Frank Thomas Jun 30 '21 at 22:10
  • She is probably **OPEN**ing the file. In that case, the separators are dependent on her windows regional settings. Better to **IMPORT** the file, using either the legacy wizard or Power Query, and ensure the field separators (as well as the decimal, thousands separators, and date format) are set to the US settings that are in the CSV file. And you should do the same thing when she sends it back. Or you could just store it as an XLS type file (unless you need CSV for another application). – Ron Rosenfeld Jul 01 '21 at 01:14
  • Why aren't you using XLS format? Formatting is included. Their install may be the Canadian French Language pack for Office. Not sure if that would have an impact. – StainlessSteelRat Jul 01 '21 at 01:50
  • Check 2 options, 1. under PC Region settings. 2. open Excel go to File- Options- Advanced. make sure the Decimal separator options are set to the same for 2 PC – Lee Jul 01 '21 at 09:12

1 Answers1

4

When CSV files are opened in Excel, columns are derived from the list separator, i.e. a , or a ;.

Which character Excel automatically recognizes as the column separator is dependent on the regional settings of the system.

To maximize compatibility try saving your CSV (with proper columns) as an Excel file (.xlsx or .xls) and send that to your client. Those filetypes do not depend on the list separator character.

Saaru Lindestøkke
  • 5,515
  • 8
  • 30
  • 48