.XLSX save as CSV - Date/Time format lost (as expected)

[ladyck3]

We have an Excel file which needs to be saved as a CSV file for upload to a SQL Database.
The problem is this, there are 2 date fields that are in the following format:

2010-01-01 00:00:00

But when the file is saved as a CSV file the format changes to:

1/1/2010 0:00

Now I understand what happens, the file is basically being saved as a text file so the data is converted. That's part of the "some features are not compatible with this format" when saving.

Is there any way that someone may be aware of to maintain the format yet have the file be a csv file?
I'm thinking no, but I said I'd check.... with the GURUS

Thanks...
Laurie




ladyck3
aka: Laurie

 

[mintjulep]

We have an Excel file which needs to be saved as a CSV file for upload to a SQL Database.

Why? There are other (better) ways to get data from Excel to SQL.

http://support.microsoft.com/kb/321686

 

[SkipVought]

Copy your data to a new sheet. Use the TEXT() function to convert the date/time value to TEXT in the form you want.

But I agree with Mintjulep.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I have no idea, it could be the author of the database, I'm not an SQL person. I suggested XML (which I have no clue about either) but was told it "HAS" to be CSV and it "HAS" to be that time format.

The use of the Text() function after its been copied? hmmm, will play with that.
Thanks in advance if I get it, if not, see ya later LOL...
You know me

ladyck3
aka: Laurie

 

[SkipVought]

The use of the Text() function after its been copied?

Use the function in the copied date column, but reference the original sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Thanks for the tips guys... I did it a bit easier even, once I saw your responses I thought WAIT A MINUTE...
I copied the data from the original and pasted as VALUES only and saved the file as CSV. I then highlighted the 2 columns with changed format and did a Format Cells on it choosing yyyy-mm-dd hh:mm:ss.

Works swimmingly.

ladyck3
aka: Laurie