.. title: Excel CSV Export Localization
.. slug: excel-csv-export-localization
.. date: 2017-05-04 22:01:22 UTC+02:00
.. tags: csv, excel, python
.. category: 
.. link: 
.. description: 
.. type: text

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:

.. code-block:: python

    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')