Forum Discussion

MartyB1506's avatar
MartyB1506
Copper Contributor
Jan 30, 2023

Date in CSV File - converting when saving

Everytime I save my file as a csv the date converts and is incorrect. I have to have the date formatted a specific way to load it into a software program. I have tried converting it to a txt file and...
excel
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Jan 31, 2023

MartyB1506  wrote:  ``Everytime I save my file as a csv the date converts and is incorrect``

 

 

Your perception of the problem is incorrect.  Consequently, your description is unclear.

 

First, there is no conversion of data when we save as CSV.  What you see in cells is exactly what you get in the CSV file (with one exception, AFAIK).  Confirm by opening the saved file in Notepad.

 

So, if dates must be ``formatted a specific way to load it into a software program``, you must format them that way in Excel before you save as CSV.

 

The simplest way is to format the cells that way.  Alternatively, you can write a macro that formats the data as needed before saving, perhaps in a temporary worksheet.

 

-----

 

Second, I suspect that you are trying to confirm how the third-party application will see the data by opening the CSV in Excel again.  You refer to ``converting it to a txt file and using the text to column``.

 

That method of confirmation is incorrect.

 

Beware that if you open a CSV (or txt) file directly in Excel, Excel (but perhaps not the third-party application) might "convert" or (mis)interpret the data in a default manner that is consistent with your system configuration.

 

The behavior is exactly the same as if you had typed the data manually exactly as it appears in the CSV file into a cell that is formatted as General.

 

So, for example, if the dates in the CSV file are purposely formatted differently from the default date form for your Excel, your Excel might not interpret some data as dates, and it might misinterpret and incorrectly display other dates.

 

Again, the "conversion" is happening when you open the CSV file in your Excel -- which might be different from the behavior of the third-party application.

 

So, it might be your attempt to confirm the data that is flawed, not your saving as CSV.

 

-----

 

Again, confirm the form of the data in the saved CSV file by opening the file in Notepad, not in Excel.

 

If the data looks wrong in Notepad, change the format of the Excel data before saving as CSV.

Resources