How to Import German Decimal Numbers Correctly into Excel
Question details
The user needs to import a CSV file containing German decimal formats into an English-configured Excel application without losing the decimal precision.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing a CSV file with European or German number formats (using commas as decimals) on a computer configured with English regional settings.
- Observed behavior
- Excel ignores the comma and interprets values like 123,45 as whole numbers (12345), resulting in incorrect data representation.
Ensure you have the original, unmodified CSV file saved on your computer. Opening the file directly by double-clicking it may cause Excel to immediately alter the formatting, so it is best to start from a blank workbook.
Import the CSV File Using Power Query with Locale Settings
Use Power Query to detect and convert data types based on the German locale, preserving the comma as a decimal separator without altering the source file.
Power Query provides a reliable workflow for importing dates and decimal numbers from different locales, ensuring data integrity during the import process.
Open a blank Excel workbook, go to the Data tab on the ribbon, click 'Get Data', select 'From File', and then choose 'From Text/CSV'.
Locate and select your German-formatted CSV file from your computer and click 'Import'.
In the preview window that appears, do not click Load. Instead, click 'Transform Data' to open the Power Query Editor.
Right-click the column header containing the decimal numbers, hover over 'Change Type', and select 'Using Locale...' from the bottom of the menu.
In the dialog box, set the Data Type to 'Decimal Number' and the Locale to 'German (Germany)'. Click 'OK', then click 'Close & Load' to bring the correctly formatted data into Excel.
Process Existing Worksheet Data Using Power Query
If your data is already pasted into an Excel worksheet as raw text, you can still use Power Query to transform it.
Easily Import German CSVs with WPS Spreadsheet
WPS Office provides a highly intuitive Text Import Wizard that allows you to manually specify decimal and thousands separators during the import process, completely bypassing regional setting conflicts.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the 'Data' tab on the top menu ribbon.
- 2. Start the Import Process: Click on 'Import Data', choose 'Select Data Source', and open your target CSV file.
- 3. Navigate the Text Import Wizard: Select 'Delimited' in the first step of the wizard, click Next, set your delimiters, and proceed to Step 3.
- 4. Configure Advanced Settings: Click the 'Advanced' button. Set the 'Decimal separator' to a comma (,) and the 'Thousands separator' to a period (.), then click 'OK'.
- 5. Finish Import: Click 'Finish' to successfully load the CSV data with the correct German decimal formatting applied.
Frequently Asked Questions
Why does Excel change 123,45 to 12345 when I double-click a CSV file?
When your computer's regional settings are configured for English, Excel expects a period (.) as the decimal separator. It interprets the comma (,) in 123,45 as a thousands separator, which it hides by default, converting the value to the whole number 12345.
Can I use the Text to Columns feature to fix the numbers?
No, if you have already opened the CSV directly in Excel, the application has likely already stripped the comma separator from the raw data. The file must be imported correctly from the very beginning using Power Query or an import wizard.
Do I need to change my Windows regional settings to open this file?
No, changing your entire system's regional settings is unnecessary and may disrupt how other applications display dates and numbers. Using Power Query to specify the locale during import is a safer, file-specific solution.
