Post reply

Read an Excel column''s properties

  • December 26, 2006 at 2:17 pm

    #115844

    Hi,

    I have a requirement were I need to read an excel column's backcolor and based on it, insert an integer value into the destination table's column.

    I used the Data flow task, in which source is Excel source object and destination is SQL Server destination.

    I tried to add a script component to do some column reading but couldnt proceed on how to do the same.

    Any kinds of suggestion is appreciated.

    Thanks

     

  • December 27, 2006 at 11:35 am

    #680152

     

    Hello

    A simple way to solve this problem would be to add a user defined function to your Excel sheet that returns the Integer you want and then read the result in your query.

    Would be very surprised if you can access the cell properties through your query.

    Good Luck.

     

     

     

     

     

     

     

     

     

     

  • December 27, 2006 at 1:25 pm

    #680168

    Hi,

    Thanks for the reply. It will need one to write a macro in the excel sheet.

    But that does not solve the problem since I dont own those sheets.

    This is something I have to handle at the time of import.

    Any other suggestions???

    Thanks

  • December 28, 2006 at 11:39 am

    #680332

    Hello,

    I was going to say no but,  perhaps you could get around it by writing the macro in an Excel Workbook that you do have access to.

    Sounds a bit long winded - but just put in a QueryTable on a worksheet read the data and then get the properties for the cells in the referenced workbook and add the integer values to your result set. Then add your workbook to your transform. 

    Easier to ask the owner of the sheet to add the info you need to the sheet though...

     

     

     

     

     

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

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