How to Import CSV Time and Decimal Values Correctly in Excel
Question details
The user needs to import a CSV file into Excel without the software automatically altering time formats or adding incorrect decimal places.
- Product
- Microsoft Excel
- Device & OS
- Mac and Windows
- Scenario
- Importing a CSV file containing specific time data and decimal numbers for data analysis or reporting.
- Observed behavior
- Excel automatically interprets the CSV data using incorrect units, locales, or number formats, such as converting minutes to seconds or adding extra decimal places to numbers ending in zero.
Ensure you know the original delimiter used in your CSV file (such as a comma or semicolon) and check your system's regional settings to match the file's locale.
Use the Text Import Wizard to Define Column Formats
Importing the CSV through the Text Import Wizard prevents Excel from automatically guessing and changing your data formats.
By default, double-clicking a CSV file allows Excel to automatically parse the data, which often leads to formatting errors. Using the Import tool gives you manual control over how each column is interpreted.
Open Excel, go to the 'File' menu, and select 'Import'. Choose 'CSV file' and locate your document.
In the Text Import Wizard, select 'Delimited' and click 'Next'. Choose the correct delimiter (usually a comma) that your CSV uses.
Click on the columns containing time or specific decimal data in the preview window. Change the 'Column data format' to 'Text' so Excel imports the exact values without altering them, then click 'Finish'.
Correct Misformatted Time and Decimal Data
If the data is already imported incorrectly, use Excel formulas and cell formatting to restore the correct values.
Import CSV Files Flawlessly with WPS Spreadsheet
WPS Spreadsheet offers a highly intuitive Text Import Wizard that gives you full control over delimiters, text qualifiers, and column data types, ensuring your time and decimal values are imported exactly as intended.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the Spreadsheet application.
- 2. Import Data: Go to the 'Data' tab and click on 'Import Data', then select 'Import Data' again to open your CSV file.
- 3. Configure Import Settings: Follow the prompt to select your delimiter and explicitly set your time and decimal columns to 'Text' format before completing the import.
Frequently Asked Questions
Why does Excel change my CSV time formats automatically?
Excel tries to be helpful by automatically detecting data types based on your system's regional settings. If the CSV's format differs from your system's default, Excel may misinterpret the data, converting minutes to seconds or changing date formats.
How do I stop Excel from removing leading zeros in my CSV?
To keep leading zeros, you must import the CSV using the Text Import Wizard or Get & Transform (Power Query) and set the specific column's data format to 'Text' before the data is loaded into the sheet.
Does changing the cell format change the actual data in Excel?
No, changing the cell format (like reducing decimal places via the ribbon) only changes how the data is displayed. The underlying stored value remains the same unless you use a function like ROUND.
