How to Fix Excel CSV Imports Misreading Decimal Separators
Question details
Users experience incorrect decimal value formatting when importing CSV or TXT files into Excel, leading to inaccurate data and corrupted scientific notation.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Importing data from CSV or TXT files into a spreadsheet for accurate calculations and charting.
- Observed behavior
- Decimal values are altered during import (e.g., -1.93121 becomes -19312) despite correct Windows regional settings, preventing accurate analysis.
Ensure you know the exact delimiter (such as a comma, tab, or semicolon) used in your source CSV or TXT file before beginning the import process.
Import Data Using the From Text/CSV Feature via Power Query
Use Excel's built-in Data Import tool to explicitly define the decimal locale, overriding automatic formatting errors upon opening.
Opening a CSV file directly by double-clicking it forces Excel to guess the data format based on system defaults, which often causes decimal errors. Using the 'From Text/CSV' import method allows you to strictly define the number formats.
Open a blank workbook in Excel, navigate to the Data tab on the ribbon, and click 'From Text/CSV'.
Locate your target CSV or TXT file in the file explorer, select it, and click 'Import'.
In the import preview window that appears, verify the delimiter is correct, then click 'Transform Data' to open the Power Query Editor.
Right-click the column header containing the problematic decimal values, hover over 'Change Type', and select 'Using Locale...' from the dropdown menu.
In the dialog box, set the Data Type to 'Decimal Number' and choose a Locale that natively uses a period as a decimal separator (such as English (United States)), then click OK.
Click the 'Close & Load' button in the top left corner of the Power Query Editor to bring the accurately formatted data into your spreadsheet.
Temporarily Override Excel's System Separator Settings
Manually configure Excel to use a specific decimal separator regardless of Windows system settings.
Seamlessly Import CSV Data with WPS Office Spreadsheets
WPS Office Spreadsheet provides an intuitive Text Import Wizard that easily handles CSV decimal separators and custom delimiters, preventing formatting errors right from the start.
- 1. Open WPS Spreadsheets: Launch WPS Office, open a new Spreadsheet, and navigate to the Data tab on the top ribbon.
- 2. Launch the Import Wizard: Click on 'Import Data' and select the 'Import Data' option from the dropdown menu to open the wizard.
- 3. Select Your Data Source: Click 'Select Data Source', browse for your CSV or TXT file, and click Open to begin configuring.
- 4. Specify Delimiters and Separators: Choose 'Delimited', select your exact delimiter, and click on 'Advanced' to explicitly define your decimal and thousands separators before clicking Finish.
Frequently Asked Questions
Why does Excel change my decimal points to commas when opening a CSV?
This usually occurs when Excel's language settings or the Windows regional settings conflict with the formatting used in the CSV file. If your system is set to a European locale where commas are used for decimals, Excel will misinterpret periods as thousands separators when you open the file directly.
Can I stop Excel from automatically formatting numbers in CSV files?
Yes. Instead of double-clicking the CSV file to open it directly from your file explorer, open a blank Excel workbook and use the 'Data > From Text/CSV' import feature. This allows you to define column data types as Text or specific numeric locales before Excel applies its automatic formatting rules.
How do I prevent scientific notation from altering large numbers during CSV import?
When importing your CSV using Power Query or the Text Import Wizard, select the column containing the large numbers and format the data type explicitly as 'Text' rather than 'General' or 'Number'. This forces Excel to read the exact characters rather than calculating a scientific notation shortcut.
