excel date formatting from CSV 1

[SQLScholar]

Hey all,

I have a CSV that comes straight out of an SQL database, which has a cell as such

17/11/2004 14:39:47

But in excel, by default, its formatting it as time. This means that i see

39:47.0

Now this seams just a bit of an annoyance, but considering this report is directly delivered to quite a few people with excel, i dont want

a) everyone to have to re-format this.

and

b) too have loads of people calling me saying "why is the date weird".

Anyone got any ideas why excel formats it like this, and how i can stop it?

TIA

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

Additional...

Sorry the output is actually

17/11/2004 14:39:47.000

but its still formatting it as above.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Zathras]

That form of date looks like SQL Server. Do you have control over the SQL that is used to extract the data? If so, you can use the Convert() function like this:

SELECT ...., Convert(Char(19),CURRENT_TIMESTAMP,20),... FROM ...

If your DBMS is not SQL Server, then find the equivalent conversion function for whatever DBMS you are using.

 

[xlbo]

If not, consider creating a template file (.XLT) that has the correct formatting - then load the csv into that template rather than a default xls workbook

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[SQLScholar]

I can understand where your going zathras but actually in the CSV itself is:

17/11/2004 14:39:47.000

which to me, looks like a perfectly valid datetime, and shouldnt be changed to time. Basically i just need it left alone. I do have control over the SQL, but i cant see how that will help, its excel thats being silly.

XLBO,

That would work, but as the CSV is emailed directly out via a 3rd party app - thats not really functional.

TIA,

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Zathras]

Dan, it may look like a perfectly valid datetime to you, but it doesn't to Excel.

Changing it the way I indicated will cause the string to be recognized by Excel as a valid datetime.

So, would you rather be right or would you rather be happy?

 

[SQLScholar]

Zathras,

I see what you mean.... and i am 99% there.

Unfortunately excels now formating

23/11/2004 09:54:41

as

dd/mm/yyyy hh:mm

when i want

dd/mm/yyyy hh:mm:ss.

You would have thought that if seconds are in a time, it would automatically format to show them - rather then hide them.

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Zathras]

Unfortunately, Excel thinks it knows what's best, and that is obviously not the case. I don't think there's any help for that other than to manually set the format for the column to show seconds. But since you're sending out the raw .csv file that option is not available.

You are rather limited in your options, I'm afraid. One thing is to build a string from three parts: Date + Time + "`" (for example). That way you could generate the rather ugly string
[tt]
17/11/2004 14:39:47`
[/tt]
which at least shows the seconds, and the "`" may go unnoticed. (On my keyboard, that symbol shares the key with the tilde "~")

One way to do that is with this expression:
[tt]
Convert(Char(12),current_timestamp,101) +
Convert(Char(8),current_timestamp,8) + '`'
[/tt]

BTW, do your users know how to select all and double-click between two column headings to automatically set all column widths to best fit? (Handy especially for .csv files.)

 

[corsair2]

I have a similar problem transfering dates into Excel.
I've a Delphi program which retrieves data from an Oracle Db and then transfers the data to Excel.
My problem is that Excel randomly transposes month and date values whilst simultaneously dropping time values.
So my (UK date format) dates appear to be US date formats (without times).
I've stepped through my Delphi program and confirmed the dates are in the correct format (as are many of the dates on the spreadsheet..).
I've even sent the dates as text strings, not dates, and the same thing happens!
Anyone got any ideas on this?

Regards

Steve