Fix Decimal Points Removed When Importing CSV Files in Power Query
Question details
The user needs to prevent decimal points from being removed and dates from formatting incorrectly when importing CSV files via Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing CSV data into Power Query where the source system and the local computer have different regional locale settings.
- Observed behavior
- Decimal values like 31.7 are incorrectly imported as 317, and date formats are altered because the decimal separator and date format in the CSV do not match the computer's locale.
Verify the original decimal separator (comma or period) used in your source CSV file by opening it in a basic text editor like Notepad. Ensure you know the exact region format the source data was generated in.
Change Data Type Using Locale in Power Query
Use the 'Using Locale' feature directly within Power Query to specify the origin region of the CSV file, ensuring decimals and dates are parsed correctly.
This is the safest method because it applies the correct regional parsing rules only to the specific column that needs it, without affecting the rest of your workbook or system settings.
Open Excel, go to the Data tab, and select 'From Text/CSV' to load your file into the Power Query Editor.
Right-click the header of the column where decimal points are missing or dates are formatted incorrectly.
Choose 'Change Type' from the context menu, then click 'Using Locale...' at the bottom of the list.
In the dialog box, set the Data Type to 'Decimal Number' (or 'Date' for date columns) and choose the Locale that matches the CSV source (e.g., English (United States) if the CSV uses periods for decimals). Click OK.
Adjust Power Query Regional Settings for the Workbook
Change the current workbook's regional settings in Power Query if all your imported files consistently come from a specific foreign locale.
Seamlessly Import CSV Files with Custom Separators in WPS Office
Skip complex Power Query configurations. WPS Spreadsheet provides an intuitive Text Import Wizard that allows you to manually set the decimal and thousand separators during the import process, ensuring your data is always perfectly formatted regardless of your system's regional settings.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
- 2. Start the Data Import: Go to the 'Data' tab on the ribbon, click 'Import Data', and select your CSV file from your computer.
- 3. Open Advanced Settings in Wizard: In the Text Import Wizard, choose 'Delimited' and proceed to the final step. Click the 'Advanced...' button.
- 4. Set the Separators: Specify the exact Decimal separator (e.g., a period) and Thousands separator used in your source file, then click OK and finish the import.
Frequently Asked Questions
Why do decimal points disappear when opening CSV files?
This happens when the CSV file uses a different decimal separator (like a period) than your computer's system regional settings (which might expect a comma). The spreadsheet software misinterprets the period as a thousand separator or ignores it entirely, changing a value like 31.7 to 317.
How can I check what separator my CSV file actually uses?
Right-click the CSV file on your computer and select 'Open with' > 'Notepad' (or another plain text editor). This allows you to view the raw text data and verify whether commas or periods are being used for decimals.
Does changing the locale in Power Query affect my overall system settings?
No. Changing the locale using the 'Using Locale' feature in Power Query or adjusting the workbook's regional settings in Query Options only affects how that specific data connection is parsed. It does not alter your Windows or macOS global regional settings.
How do I fix incorrect date formats imported from a CSV in Power Query?
Similar to fixing decimals, dates can be corrected by right-clicking the affected date column header in Power Query, selecting 'Change Type' > 'Using Locale...', setting the data type to 'Date', and picking the region that matches the CSV's date format (e.g., choosing a UK locale for DD/MM/YYYY formats).
