This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have to use a csv file to import data into a database. The database has a strict date/time format of dd-mm-yyyy h:mm
My data is currently stored in an excel spreadsheet. The date/time is set to a Custom cell format of dd-mm-yyyy h:mm (same as the database format required).
When I save the excel spreadsheet as a csv file it coverts the date to a date/time format of dd/mm/yyyy h:mm so I have to customise it back to the dd-mm-yyyy h:mm to match the database date/time format.
I then save the file and close it as I can't have it open to import into the database. I try importing the csv file but get errors from the database saying the date format is incorrect. When I open the csv file again the date/time format has reverted back to the previous file format of dd/mm/yyyy h:mm.
No matter how many times I try & save the file it keeps reverting to the incorrect date/time format.
Is there any way to fix this?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
I solved this by using the TEXT() function to turn my date into text in a new column, then I copy and pasted that column as text and ended up with my correct date format while being labeled "general" format instead of date.
Yes I can reproduce that
Okay, now do the same with your original file.
Open the Excel file
Save as CSV
Open the CSV file using Notepad!!!
Is the date format intact, just like with the new file?
Andreas.
There are, but before we struggle with the details how all this works:
Make a new file
A1: Date
A2: =NOW()
Format A2 with your date/time format
Save as CSV
Open the CSV file using Notepad!!!
Can you reproduce that on your system?
Andreas.
Hello Pink,
I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.
This is a common issue when working with dates in CSV files and Excel. When you save an Excel file as a CSV, it may change the date format to the default date format configured for your system. One solution to this problem is to change the extension of the CSV file from .csv to .txt to avoid the problem.
Best Regards, IBHADIGHI
HI Andreas that worked as well. The formats are all in tact :)
This means that everything is fine with Excel.
Dighi's tip just aims to do the same thing, opening the export with Notepad. This has no relevance for the exported data, this is also a rumor that has been coming up again and again for decades.
Aeons ago it was decided to associate the CSV file type with Excel and when opening a CSV, Excel has to interpret the content and show it due to your Region settings of your OS, hence the different date format.
This article shows how to import CSV into Excel correctly, but it has nothing to do with your problem.
Power Query - How to import a CSV file that does not match your locale - Microsoft Community
If you still have difficulties reading the CSV into your database, then the problem is not with the CSV file, but with the interface of your database.
Andreas.