0

Is there a way to create a CSV file from an Excel one, but with a delimiter other than comma?

I tried changing Windows parameters. In Windows Control Panel -> Click, Language & Region -> formats parameters panel, I set the delimiter I wanted and then created the CSV file. That didn't work.

Specifically, I'm looking for a way to force the French version (Excel 2007) to export CSV with comma delimiter instead of semicolon.

Do you have any ideas?

yo_haha
  • 103
  • 1
  • 3
  • 2
    possible duplicate of [Excel does not honor the delimiter setting for reading or writing CSV files](http://superuser.com/questions/832786/excel-does-not-honor-the-delimiter-setting-for-reading-or-writing-csv-files) – Ƭᴇcʜιᴇ007 Nov 26 '14 at 15:51

1 Answers1

2

List separator set in Language & Region settings will not work if you have Decimal Separator set to same character.

So if you have , set as decimal separator set there - you cannot have , as list separator and Excel will use default ;.

You have to use . as decimal separator or write some macro that will save your document in desired format.

Kamil
  • 2,656
  • 1
  • 19
  • 28
  • I need the decimal separator to stay comma, because there are some decimals in the CSV. So ideally, I need the CSV to be generated with a comma delimiter and with all columns being surrounded by " " (to escape the decimals separator which is comma). I believe a macro is the quickest way to do this. The problem is: I've never created a macro before ... ;) – yo_haha Nov 26 '14 at 16:11
  • @yo_haha So you need a macro that will build whole CSV content. – Kamil Nov 26 '14 at 18:04
  • 1
    @yo_haha If you don't want to mess up with macros - maybe save your file with default settings and modify it by some external tool (there are many CSV converters). – Kamil Nov 26 '14 at 18:59
  • I'll try CSV converters before the macros :) Thanks – yo_haha Nov 27 '14 at 08:11
  • Do you've got in mind the name of some converter that allows delimiter changing? – yo_haha Nov 27 '14 at 08:20
  • Nope. It might be good idea for another question. – Kamil Nov 27 '14 at 12:33
  • I found a very nice macro that works `Sub saveCSV() File = Application.GetSaveAsFilename(InitialFileName:="file.csv", FileFilter:="CSV (*.csv), *.csv") ActiveWorkbook.SaveAs Filename:=File, FileFormat:=xlCSV, CreateBackup:=False, local:=False End Sub` – yo_haha Nov 28 '14 at 13:13