How to Convert Period Decimal Values Correctly When Importing CSV to Excel
Question details
Users need to correctly import large CSV files containing numeric values with period decimal separators into Excel, ensuring the data is recognized as numbers rather than text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing a CSV file where the source data uses periods for decimals, but regional settings or default Excel imports fail to recognize the format.
- Observed behavior
- Excel imports numeric values from the CSV as text strings because it does not automatically recognize the period as a valid decimal separator based on the system's current regional configuration.
Before proceeding, determine which decimal separator your computer's regional settings currently use (such as a comma), and ensure you are working on a copy of your CSV file to prevent accidental data corruption.
Use Power Query Locale Settings During Import
The most reliable method for handling large datasets with foreign decimal formats is to import via Power Query and apply a specific locale that uses periods.
Power Query allows you to transform data before it officially loads into your Excel worksheet. By using the 'Using Locale' feature, you tell Excel exactly how to interpret the punctuation in your source file, avoiding the performance issues of manual find-and-replace on millions of rows.
Navigate to the Data tab on the Excel ribbon, click 'From Text/CSV', select your target file, and click 'Transform Data' to open the Power Query Editor.
Locate the column containing your numeric data that has been incorrectly formatted as text, and click its header to select it.
Right-click the selected column header, hover over 'Change Type' in the context menu, and click on 'Using Locale...'.
In the dialog box, set the Data Type to 'Decimal Number'. For the Locale dropdown, select a region that utilizes a period as the decimal separator, such as 'English (United States)', then click OK.
Click the 'Close & Load' button located in the Home tab of the Power Query Editor to insert the correctly formatted numbers into your Excel workbook.
Modify Advanced Settings in the Legacy Text Import Wizard
If you prefer not to use Power Query, you can use Excel's legacy Text Import Wizard to manually define decimal separators during the import process.
Easily Import and Format CSV Decimals with WPS Office
WPS Spreadsheet features an intuitive, built-in Data Import wizard that makes it incredibly simple to handle complex CSV files. You can effortlessly map custom decimal separators directly during import to ensure your numeric data is recognized instantly.
- 1. Launch the Import Data Tool: Open WPS Spreadsheet, go to the 'Data' tab on the top ribbon, and click 'Import Data' to select your source CSV file.
- 2. Choose the Delimited Option: In the Text Import Wizard window, select 'Delimited' so the software knows your file separates data by specific characters, then click Next.
- 3. Configure Advanced Number Settings: Proceed to the final step of the wizard and click the 'Advanced...' button. Input a period (.) in the 'Decimal separator' field and confirm by clicking OK.
- 4. Complete the Import: Click 'Finish' to successfully load your dataset. WPS Spreadsheet will automatically convert the period-separated values into workable numbers.
Frequently Asked Questions
Why does Excel convert my CSV numbers into text?
This happens when the CSV file originates from a system using a different decimal separator (like a period) than your computer's regional settings (which might expect a comma). When Excel encounters unexpected punctuation, it defaults to formatting the cell as text.
Can I simply use Find and Replace to change the periods?
While Find and Replace works for small datasets, it can be extremely slow and risky for large CSV files. It may accidentally alter other text strings or dates that contain periods. Power Query is much safer as it precisely targets data types.
Will changing my Windows regional settings fix the import issue?
Yes, going to your Windows Control Panel and changing your system's default decimal symbol to a period will fix the Excel import. However, this system-wide change will affect how numbers, dates, and times are displayed in all other applications on your computer.
Why is the Power Query 'Change Type' option not working?
If you simply choose 'Decimal Number' without using the 'Using Locale' option, Power Query will still rely on your local system settings and may return errors. Always choose 'Change Type > Using Locale' to explicitly define the source format.
