How to Preserve Seconds When Importing CSV Date and Time in Excel
Question details
Users need to maintain the exact seconds in date and time values when importing or opening CSV files in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing CSV files containing detailed date and timestamp data for analysis or reporting.
- Observed behavior
- Excel automatically applies default system date formatting when opening CSV files, which often hides the seconds. If the file is saved in this state, the seconds data is permanently lost.
Verify that your original CSV file actually contains the seconds data by opening it in a plain text editor like Notepad before importing it into Excel.
Import CSV using Power Query
Use Excel's Power Query feature to safely import the data without triggering automatic formatting, allowing you to explicitly define the date-time format.
Power Query prevents Excel from automatically interpreting and stripping formatting from your raw CSV text. By setting the data types and custom formats during the import process, you guarantee no data loss occurs.
In Excel, navigate to the 'Data' tab on the ribbon. Click on 'Get Data', select 'From File', and then click 'From Text/CSV'.
Locate and select the CSV file you want to import, then click 'Import'. When the preview window appears, click the 'Transform Data' button.
In the Power Query Editor, select the column containing your date and time values. Click the data type icon in the column header and change it to 'Date/Time'.
Click 'Close & Load' in the top left corner to import the data into your spreadsheet safely.
Highlight the newly imported date-time column in Excel, press 'Ctrl + 1' to open Format Cells, select 'Custom', and enter 'yyyy-mm-dd hh:mm:ss' to ensure the seconds are visible.
Adjust System Regional Settings
Change your computer's default regional time format to include seconds so Excel defaults to showing them automatically.
Import and Format CSV Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers an intuitive text import wizard and precise cell formatting capabilities, making it easy to handle complex CSV data without losing important details like timestamps.
- 1. Open Data Import: Launch WPS Spreadsheet, navigate to the 'Data' tab, and click on 'Import Data' to select your CSV file.
- 2. Use the Text Import Wizard: Follow the prompts in the Text Import Wizard, ensuring you select 'Text' for your date/time columns so the system doesn't truncate the seconds.
- 3. Apply Custom Formatting: Select the imported cells, right-click and choose 'Format Cells'. Go to 'Custom' and type 'yyyy-mm-dd hh:mm:ss' to display the exact timestamps.
Frequently Asked Questions
Why does Excel hide seconds in CSV files by default?
Excel automatically parses recognized date and time formats based on your system's regional settings. Since default system settings typically only display hours and minutes to save space, Excel mimics this behavior and hides the seconds visually.
Can I recover the seconds if I already saved the CSV in Excel?
If you opened the CSV directly in Excel, the seconds were hidden, and you then hit 'Save', Excel overwrites the file with the truncated data. The seconds are permanently lost unless you have a backup of the original CSV file.
Does this issue happen with XLSX files too?
No. When you work with standard Excel workbooks (XLSX), the underlying data retains the seconds even if they are hidden visually by cell formatting. The data loss issue is specific to saving plain-text CSV files while the seconds are hidden.
