none
Problem in Date Format While Exporting To Excel Destination From Flat File Source In SSIS

    Question

  • Hi,

    I have a flat file(.csv) which contains data(strings incuding date in yyyy-mm-dd format).This i am using in Flat File Source and want to export those data to an Excel Destination.Before exporting to Excel Destination i have created a Derived Column component where i am using an express to get day,month,year part and creating a date sting in dd/mm/yyyy format then conveting it to DT_DATE datatype and then exporting to Excel Component.But while exporting to Excel this date format is getting changed to mm/dd/yyyy format.

    The expression i used in derived column is

    (DT_DATE)(SUBSTRING(date,9,2) + "/" + SUBSTRING(date,6,2) + "/" + SUBSTRING(date,1,4))

    where date is a column from Flat File Source.

    Even I changed the Locale to English(United Kingdom) in Flat File Connection Manager for the above .csv file.But still it didnt work.

    I searched a lot in google and failed to get any solution on this.

    Can anyone help me in solving this issue ?

    Friday, May 13, 2011 2:00 PM

Answers

  • The problem lies entirely with Excel.  It's all about how it decides to format the data it receives, it's not about the data it actually receives.

    The step you're taking to "reformat" then cast as a date is entirely redundant.  dd/mm/yyyy format is a non-standard and easily misinterpreted format.  yyyy-mm-dd is unambiguous, and the standard ISO/SQL way of representing dates in strings.

    That said - I'm not sure how the Excel Destination communicates format information to Excel itself.  I would try some of what Arthur and Reza have suggested.  But do understand that "date" types in SQL and SSIS do not have a "format".  Only when a date is converted into a string type does it get a "format".

    My thought would be to send the data to Excel as a DT_DATE or other SSIS date type.  Excel should understand that the data is a date type, and allow the user to format it as desired - probably defaulting to the locale format, or whatever the Excel default is where the sheet is being created.

    Please let us know what ends up working.


    Todd McDermid's Blog Talk to me now on
    Friday, May 13, 2011 5:33 PM
    Moderator

All replies

  • I think the conversion to dt_date is a redundant step. Make it string, and Excel should pick it up nicely.

    In some cases setting its connection string with IMEX=1 option added helps (switches Excel to import mode).

    Besides, csv is a supported format for Excel or you have to have that field as date in Excel?


    Arthur My Blog
    By: TwitterButtons.com
    Friday, May 13, 2011 2:08 PM
    Moderator
  • I am agree with Arthur,

    if you don't convert string to date, it would insert into excel correctly.


    http://www.rad.pasfu.com
    Friday, May 13, 2011 2:19 PM
    Moderator
  • The problem lies entirely with Excel.  It's all about how it decides to format the data it receives, it's not about the data it actually receives.

    The step you're taking to "reformat" then cast as a date is entirely redundant.  dd/mm/yyyy format is a non-standard and easily misinterpreted format.  yyyy-mm-dd is unambiguous, and the standard ISO/SQL way of representing dates in strings.

    That said - I'm not sure how the Excel Destination communicates format information to Excel itself.  I would try some of what Arthur and Reza have suggested.  But do understand that "date" types in SQL and SSIS do not have a "format".  Only when a date is converted into a string type does it get a "format".

    My thought would be to send the data to Excel as a DT_DATE or other SSIS date type.  Excel should understand that the data is a date type, and allow the user to format it as desired - probably defaulting to the locale format, or whatever the Excel default is where the sheet is being created.

    Please let us know what ends up working.


    Todd McDermid's Blog Talk to me now on
    Friday, May 13, 2011 5:33 PM
    Moderator
  • Excel date settings will most likely be determined by the regional settings on the local client pc and cannot be overridden by SSIS without physically opening the excel file and interacting with it

    Best bet is to output as a string in  yyyy-mm-dd format and educate teh end users on interpreting it!


    Rgds Geoff
    Monday, May 16, 2011 3:45 AM
  • Hi ArthurZ,

    Many thanks for your valuable thought on this issue.

    In Derived Column component instead of formatting the date sting in dd/mm/yyyy format, I formated the date string in yyyy-mm-dd format then type casted it to DT_DATE type in expression and the data type specified for the new column is unicode string[DT_WSTR] and then exported to Excel Component and excel picked it up nicely and date got displayed in dd/mm/yyyy format.

    Thanks,

    Tripati


    Tripati
    Friday, May 20, 2011 8:00 AM
  • Hi Geoff,

    In Derived Column component formatting the date sting in yyyy-mm-dd format, then type casting it to DT_DATE type in expression and then specifying the datatype as unicode string[DT_WSTR] for the new column worked fine.And date got displayed in dd/mm/yyyy format.

    Thanks,

    Tripati


    Tripati
    Friday, May 20, 2011 8:06 AM