Read dynamic columns from an excel into SSIS

  • Hi

    I have an excel file which has dynamic columns

    i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

    My problem is to Unpivot the data

    Date 8/2/2013 8/9/2013 8/16/2013

    Stock 1,561 1,661 1,761

    i.e. the abobe table should become as

    Date Stock

    8/2/2013 1561

    8/9/2013 1661

    8/16/2013 1,761

    How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

    Thanks for your help in advance

  • duggal.priyanka06 (11/11/2014)


    Hi

    I have an excel file which has dynamic columns

    i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

    My problem is to Unpivot the data

    Date 8/2/2013 8/9/2013 8/16/2013

    Stock 1,561 1,661 1,761

    i.e. the abobe table should become as

    Date Stock

    8/2/2013 1561

    8/9/2013 1661

    8/16/2013 1,761

    How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

    Thanks for your help in advance

    Quick questions:

    1) Is there any option of changing this to a row based increments rather than columnar? That would by itself solve your problem.

    2) Are there only two data rows in the spreadsheet? (Date & Stock)

    A possible solution would be to import the full rows without parsing into columns and then use a splitter function in the database to chop it into columns, straight forward and good for few hundred colums.

    😎

Viewing 2 posts - 1 through 1 (of 1 total)

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