1

In many EU countries a comma ',' is used as the decimal separator, whereas in the US a dot '.' is used.

CSV (Comma Separated Values) files are supposed to use the comma to separate cell values. However, often a tab '\t' or other characters are used instead.

What's interesting, Excel if you save a .csv file using Microsoft Excel in a EU country using the comma as a decimal separator, the value it uses to separate cell values is not an escaped comma, but a semicolon ';'. Looking on the net it seems that, if you are in the US, Excel will save .csv files using a proper comma (I can't verify this).

I'm trying to find a way to create a csv file that can be recognized by Excel without any user action, both in the EU and the US.

Here's an example using Excel with an Italian locale

Excel cell test

The above, saved as .csv (MS-DOS), translates to

foo;foo bar;
foo'bar;"foo""bar";
foo,bar;foo.bar;
foo:bar;"foo;bar";
foo/bar;foo\bar;
"foo
bar";foo|bar;
foo;bar;foobar

this is to make the empty line appear

It may be possible that, depending on the local "list separator", this may not be recognized correctly.

I've read that the new Excel 2013 needs sep=; to be set as the first line in order to work correctly. This is an ugly hack, but it seems to also be working for Excel 2010 (except it gets overwritten on save)...

Does the above text work for you, if you save it as a csv?

Is there a less hacky way to tell Excel which character is the cell separator, without having the user to set things up?

Thanks.

Community
  • 1
  • 1
Agostino
  • 2,723
  • 9
  • 48
  • 65
  • Have you thought about using tab-delimited instead? But if there are are dates or decimals then excel's automatic reading is going to break things... Best approach might be to use "import text file" instead – Captain Nov 07 '14 at 13:30
  • You don't need to create your CSV files in any particular way. The problem is in how you're opening them with Excel. – Mr. Mascaro Nov 07 '14 at 13:54
  • I actually have a very simple file, there's no multiline, no dates and just a single decimal number I may decide to take away. – Agostino Nov 07 '14 at 13:59

1 Answers1

0

Time to head back to a time before visual anything, and grab a command from the past. It will involve you manually writing the file out with VBA, but it has the criteria you expect: Write

Open "c:\tmp\myfile.csv" for output as #1

for i=1 to 100
    write #1,range("A"&i),range("B"&i),range("C"&i)
next i

close #1

You will have to do a little manual work - it doesn't translate a single quote into a double quote, but the rest is as desired:

  • the Write # statement inserts commas between items and quotation marks around strings as they are written to the file
  • Numeric data is always written using the period as the decimal separator.
  • Dates are written as #yyyy-mm-dd hh:mm:ss#
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • 1
    So, you are providing a VBA script to convert an EU Excel cvs document into a US Excel csv? Not really what I needed. I need a formatting that can be read by both. – Agostino Nov 08 '14 at 12:34
  • use `input` to read what was written by write. works regardless of location. – SeanC Nov 09 '14 at 03:55
  • This would imply that I have access to the user's PC, or have a way to have them run the script. I'm asking if there's a csv _format_ that can be read immediately by US and EU Excel, without being transformed. If the answer is no, just say so. Thanks. – Agostino Nov 10 '14 at 12:17