DT_DBTIMESTAMP Not Displaying Properly in .CSV Destination File

Question

DT_DBTIMESTAMP Not Displaying Properly in .CSV Destination File

MostInterestingMan

SSCertifiable

Points: 6291
More actions
August 10, 2010 at 1:46 pm

#222414

Hello,
I have a date column with data type DT_DBTIMESTAMP that I am exporting to .csv. In the Flat Connection Manager the data type is correct and I can see it formatted like a TIMESTAMP in the Data Viewer as "2003-02-17 15:05:31.840000000". However in the .csv file the value is "05:31.8" (2/17/2003 3:05:32 PM). When I export to .txt I get the correct DATETIMESTAMP. Is there is reason why .csv can't deplay the TIMESTAMP?
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

What are you using to view the CSV?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 2

Excel

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

Excel is helpfully (:crazy:) doing the conversion for you. Try opening it in Notepad and you'll see that it's just the same as the .txt.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 4

yep, that's what I figured. It's formatted properly in notepad. Thanks for taking a look.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Baskar B.V SSCrazy Points: 2823 More actions · Answer 5

MostInterestingMan (8/10/2010)
Hello,
I have a date column with data type DT_DBTIMESTAMP that I am exporting to .csv. In the Flat Connection Manager the data type is correct and I can see it formatted like a TIMESTAMP in the Data Viewer as "2003-02-17 15:05:31.840000000". However in the .csv file the value is "05:31.8" (2/17/2003 3:05:32 PM). When I export to .txt I get the correct DATETIMESTAMP. Is there is reason why .csv can't deplay the TIMESTAMP?
Thanks,

You can avoid this by creating new excel sheet in the destination task instead of selecting predefined excel sheet. In your excel destination which you would have selected as data flow destination and click on the "new" button next to the "Name of the excel sheet". This would import the data as it is in your target.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

Phil Parkin SSC Guru Points: 246963 More actions · Answer 6

Baskar B.V (8/10/2010)
MostInterestingMan (8/10/2010)
Hello,
I have a date column with data type DT_DBTIMESTAMP that I am exporting to .csv. In the Flat Connection Manager the data type is correct and I can see it formatted like a TIMESTAMP in the Data Viewer as "2003-02-17 15:05:31.840000000". However in the .csv file the value is "05:31.8" (2/17/2003 3:05:32 PM). When I export to .txt I get the correct DATETIMESTAMP. Is there is reason why .csv can't deplay the TIMESTAMP?
Thanks,
You can avoid this by creating new excel sheet in the destination task instead of selecting predefined excel sheet. In your excel destination which you would have selected as data flow destination and click on the "new" button next to the "Name of the excel sheet". This would import the data as it is in your target.

It's a CSV export, not Excel.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Phil Parkin (8/10/2010)
Excel is helpfully (:crazy:) doing the conversion for you. Try opening it in Notepad and you'll see that it's just the same as the .txt.

Excel always screws things up helpfully 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 8

da-zero (8/11/2010)
Phil Parkin (8/10/2010)
Excel is helpfully (:crazy:) doing the conversion for you. Try opening it in Notepad and you'll see that it's just the same as the .txt.
Excel always screws things up helpfully 😀

So we decided to have the users format those trouble fields (datetime) when they open the CSV file in Excel. I have explained to them how Excel, in all honesty, is just trying to interpret the CSV format.

Thanks for all your inputs again.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Phil Parkin SSC Guru Points: 246963 More actions · Answer 9

Sounds like a plan.

If you export from Excel to CSV after performing this reformatting, is the formatting of the field the same as was originally output?

If not, what happens if you re-import the (Excel version) CSV - does the formatting work correctly this time with no user input?

If so, you should be able to change the formatting of your original output to match what Excel likes - then you'll have happy users 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin