'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Prevent Excel from Changing AWS Athena Timestamps in CSV
Question details
The user wants to stop Excel from automatically altering the 24-hour timestamp format when opening CSV files downloaded from AWS Athena.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Opening downloaded CSV files containing AWS Athena timestamps directly in Excel.
- Observed behavior
- Excel automatically converts original 24-hour timestamps (e.g., 2023-06-17 01:55:02.000) into a 12-hour custom display based on default system settings.
Close Microsoft Excel before making changes to your Windows regional settings to ensure the new time format is applied properly when you reopen your CSV file.
Adjust Windows Regional Time Settings
Since Excel relies on your system's regional settings to display dates and times, changing the default Windows time format to a 24-hour format prevents Excel from overriding your AWS Athena timestamps.
When you open a CSV file directly in Excel, the software automatically parses data based on your computer's local date and time preferences. Modifying this globally in Windows ensures the formatting remains intact without needing to manually reformat cells every time.
Press 'Windows key + I' on your keyboard to open the Windows Settings application.
Select 'Time & language' from the settings menu, and then click on 'Date & time'.
Scroll down and click on 'Date, time & regional formatting' (or 'Language and region' depending on your Windows version), then select 'Change data formats'.
Change both the 'Short time' and 'Long time' dropdowns to a 24-hour format option (such as 13:55 or 13:55:02). Reopen your CSV file in Excel to see the corrected formatting.
Import the CSV File as Text via Power Query
If you do not want to change your global Windows settings, you can import the CSV file and force Excel to treat the timestamp column strictly as text.
Open CSV Files without Losing Timestamp Formatting in WPS Office
WPS Spreadsheets provides an intuitive Text Import Wizard that allows you to specify column data types when opening CSV files. By setting your AWS Athena timestamp column as Text during import, you completely avoid unwanted automatic time format conversions.
- 1. Launch WPS Spreadsheets: Open WPS Office and create a new blank spreadsheet.
- 2. Initiate Import: Navigate to the 'Data' tab, click on 'Import Data', and select 'Import Data' again from the dropdown menu, then choose 'From Text'.
- 3. Select Your File: Browse your computer for the AWS Athena CSV file and open it.
- 4. Configure Delimiters: In the Text Import Wizard, select 'Delimited', check the box for 'Comma', and click 'Next' to proceed to the final step.
- 5. Format Column as Text: Click on your timestamp column in the preview window, choose 'Text' under the Column Data Format options, and click 'Finish'.
Frequently Asked Questions
Why does Excel automatically change my CSV timestamps?
By default, Excel parses recognized dates and times in CSV files according to your Windows regional settings. If your system clock is set to a 12-hour format, Excel will automatically convert any 24-hour timestamps to match your system preferences.
Can I fix the timestamp format without changing my Windows settings?
Yes. Instead of double-clicking the CSV file to open it directly, use the 'From Text/CSV' import feature located in the Data tab. This allows you to define the timestamp column as Text during the import process, completely preventing Excel from altering the format.
Does saving the CSV in Excel permanently alter the AWS Athena timestamps?
If you open the CSV directly and Excel applies its 12-hour formatting, saving and overwriting the file will permanently alter the timestamps to the new format. It is highly recommended to import the data safely or adjust regional settings before saving.
