Excel: Importing CSV Files Created in Different Locale Settings

When you work with a locale that is different from yours, something simply as importing CSV files can become a major problem. For example, in US, you would use the comma as the field separator and the period as the decimal separator. But this is not universal. In Europe, the comma is used as the decimal separator, semi-colon as field separator, and period as thousand separator. You can replace these characters to match your locale but Excel can help you do this with a simple macro.

There are two settings:

     Application.DecimalSeparator

     Application.UseSystemSeparators

Importing CSV files can be adjusted based on these two setting. Here's a general idea to follow:

1. Save the current settings to variables
2. Ask what DecimalSeparator ought to be or determine it from the input file
3. Set UseSystemSeparators to False after changing DecimalSeparator
4. Import CSV files using ActiveSheet.QueryTables.Add function while setting .TextFileTabDelimiter, TextFileSemicolonDelimiter,TextFileCommaDelimiter, TextFileSpaceDelimiter, and/or TextFileOtherDelimiter. If the delimiter is not any of Tab, Semicolon, Comma, or Space, set TextFileOtherDelimiter to the separator
5. Put DecimalSeparator to the original value and UseSystemSeparators to True

Excel should magically replaces commas to period and period to commas, e.g. 2.450,23 becomes 2,450.23.