Today I had the problem that I needed to create a CSV file from an Excel Sheet at work. The problem was that I needed an actual CSV, a Comma Separated List, and Excel writes out a localized version of a CSV.

So in my case, on a german system, Excel writes out a CSV file that uses semicolons ; instead of commas , as delimiter and decimal numbers use , instead of . for the decimal point. To my surprise there is no way to configure the output of Excel at all.

After a bit of googling the "best" solution I found was to change the system language to English, export the CSV and switch back. So I decided to write a small python script that translates my german CSVs to standard English.

Enough text, here the script:

import csv
import re

def translateValue(value):
    match = re.fullmatch('\\d+,\\d+', value)
    if match != None:
        return value.replace(',', '.')
    return value

def translateCSV(inName, outName):
    with open(inName, newline='') as inCSV:
        reader = csv.reader(inCSV, delimiter=';', quotechar='"')
        with open(outName, 'w', newline='') as outCSV:
            writer = csv.writer(outCSV, delimiter=',', escapechar='\\', doublequote = False)
            for row in reader:
                writer.writerow([translateValue(v) for v in row])

translateCSV('de.csv', 'en.csv')

My first post! Awesome!