none
How to read Hyperlinks in SSIS

    Question

  • Hi There,

       I found a strange (at least to me)  requirement  today. I've an excel 2007 file. Which has a field XYZ...

    Here it looks like

    ABC                      XYZ

    Company1            WebSite
    Company2            Website2
    ...........                 ................



    The contents of XYZ fields are Hyper linked .  So if we click on that website in XYZ filed, it will direct to company1's Website (www.company1.com). If I click on website2, it will direct me to the company2's website (www.comapny2.com).

    When I'm loading data in to my sql server table, This data is loading as follows.


    ABC                       XYZ
    COmpany1            website
    Company2             website1


    Instead I need the data should be like this

    ABC                       XYZ
    Company1            www.company1.com
    Company2            www.company2.com


    How can I get this without altering my excel file. I can't alter the excel file manually every day.

    So I'm wondering,Is there a way in SSIS we can get this one...???

    Any help would be greatly appreciated also, if you didn't understand the question let me know I'll try to explain in more detail.
    Unknown
    Thursday, December 03, 2009 4:51 PM

Answers

  • I do understand now - and I think you have your work cut out for you.

    Unfortunately, I don't think that the Excel Provider will be able to read that information from the Excel worksheet.  I can't say I've ever tried - so I can't say it's not possible - but the provider is intended to read the contents (value) of the cell, and the hyperlink isn't it.

    What you will probably need to do is some hackery involving the Excel COM API, or macros.  In fact, since you should stay away from using the Office COM API in SSIS, here's my first hopeful recommendation using macros:

    1. Add an "on save" macro - or even an "on change" macro to that column in your worksheet.
    2. Add a column to the right of the XYZ column - it can be to the "far" right - doesn't have to be right beside it.
    3. Hide that new column if you need to
    4. Have your macro "copy" the XYZ column - and "paste special: links" into that new column.  Here's the code from a quick "record macro" session:

        Range("A1").Select
        Selection.Copy
        Range("B1").Select
        ActiveSheet.Paste Link:=True
    

    Now you've got a column you can import.
    Todd McDermid's Blog
    • Marked as answer by Roger Binny Wednesday, June 02, 2010 9:22 PM
    Thursday, December 03, 2009 9:32 PM
    Moderator

All replies

  • please type in your connection string? how are you reading the excel file? are you using IMEX= 1 ?
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Thursday, December 03, 2009 5:24 PM
  • How can I get this without altering my excel file. I can't alter the excel file manually every day.
    can you give more details on that , thanks
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Thursday, December 03, 2009 5:26 PM
  • If it's as "simple" as you describe, then all you need is a derived column component with an expression something like this:

    "www." + [ABC] + ".com"

    However, I'm not sure that's exactly what you really mean.  The above expression just slams a "www." and ".com" around the company's name - the value in ABC - which is exactly what you literally have said you want - but it completely ignores the design of your spreadsheet (and common sense).  I would think that you'll probably have some data in your spreadsheet that looks like this:

    ABC           XYZ
    Coca Cola  coca-cola.com

    And you can't just slam a "www." and ".com" around a company with a space in the name.  So you probably want to make the string found in XYZ be "fully qualified" - such that if it already has a ".com" on it, you just put a "www." on the front and no more.

    I'm not sure exactly how to do this - but if I had your problem, this is what I'd do:

    1. I know that SSIS has no built-in components that can "cleanse" your data
    2. This means I will look to implement your cleansing (more like "marking up") in a Script component
    3. That means I have to figure out a way in .Net to make the URL "fully qualified".

    1 and 2 are "easy" in SSIS - but I don't know how to do #3.  So I would:

    1. Read through the MSDN library on the System.Web or System.Net namespace for anything that looks good
    2. Post in a web-oriented MSDN forum like this: Network Class Library (System.Net)
    Todd McDermid's Blog
    Thursday, December 03, 2009 5:39 PM
    Moderator
  • When I'm loading data in to my sql server table, This data is loading as follows.

    ABC                       XYZ
    COmpany1            website
    Company2             website1

    Instead I need the data should be like this

    ABC                       XYZ
    Company1            www.company1.com
    Company2            www.company2.com
    Hi Roger
     correct me if i am wrong you mentioned that ..........

    Instead I need the data should be like this
    ABC                       XYZ
    Company1            www.company1.com
    Company2            www.company2.com

    .........   But the data truns out to be ..........

    ABC                       XYZ
    COmpany1            website
    Company2             website1

    ..............    so i dont need to import anything from column XYZ ? is that right ? becuase you want the Companies name to be like WWW.Company1.com am i right? if so then todd is right and try his suggestion

    BUT
    if the source file is

    ABC                       XYZ
    Company1            www.yahoo.com
    Company2            www.hotmail.com

    and you are getting

    ABC                       XYZ
    Company1            www.company1.com
    Company2            www.company2.com

    or

    ABC                       XYZ
    Company1            company1
    Company2            company2

    then some other thing is wrong and you have to be more specific



    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Thursday, December 03, 2009 6:02 PM
  • Thank you all for your responses.

    Nik3,

          My Source is like this

    ABC                     XYZ

    Company1           This page is  Yahoo
    COmpany2           click on this one to access hotmail
    ........                      ..........


    Now I need the Full Path of the HYPER LINKED Text to be loaded in my target.

    Like

    ABC              XYZ
    Company1     www.yahoo.com
    Company2     www.hotmail.com

    but, currently If map xyz to the destination XYZ then my result is looking like

    ABC               XYZ
    Company1      This Page is Yahoo
    Company2      Click on this one to access hotmail














    Unknown
    Thursday, December 03, 2009 6:48 PM
  • @ Todd,


      You are always there if some one needs help. I really can't stop admiring you on this.

    This is how my data looks like in Excel file.

    ABC                        XYZ
    Coca Cola                Here You find Coca Cola website
    Pepsi                       This is Pepsi's products website
    ....                             ...................

    when I try to load this one to some database table, it is simply loading as follows


    ABC                    XYZ
    Coca Cola            Here you find Coca Cola Website
    Pepsi                    This is Pepsi's Products website

    but my requirement is, I need

    ABC                                XYZ
    Coca Cola                     http://www.coca-cola.com/index.jsp
    Pepsi                            http://www.pepsi.compepsi_redirect.php?theurl=www.pepsiproductfacts.com


    I need the path of the Hyperlinked text in the destination not the hyper linked text....!


    I hope you understand ...! :)

    Unknown
    Thursday, December 03, 2009 6:57 PM
  • I do understand now - and I think you have your work cut out for you.

    Unfortunately, I don't think that the Excel Provider will be able to read that information from the Excel worksheet.  I can't say I've ever tried - so I can't say it's not possible - but the provider is intended to read the contents (value) of the cell, and the hyperlink isn't it.

    What you will probably need to do is some hackery involving the Excel COM API, or macros.  In fact, since you should stay away from using the Office COM API in SSIS, here's my first hopeful recommendation using macros:

    1. Add an "on save" macro - or even an "on change" macro to that column in your worksheet.
    2. Add a column to the right of the XYZ column - it can be to the "far" right - doesn't have to be right beside it.
    3. Hide that new column if you need to
    4. Have your macro "copy" the XYZ column - and "paste special: links" into that new column.  Here's the code from a quick "record macro" session:

        Range("A1").Select
        Selection.Copy
        Range("B1").Select
        ActiveSheet.Paste Link:=True
    

    Now you've got a column you can import.
    Todd McDermid's Blog
    • Marked as answer by Roger Binny Wednesday, June 02, 2010 9:22 PM
    Thursday, December 03, 2009 9:32 PM
    Moderator
  • @ Todd,


      You are always there if some one needs help. I really can't stop admiring you on this.
    I completely agree. MS owes Todd a big vacation to Hawaii ;)

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Friday, December 04, 2009 2:06 AM
  • I don't know about "owe" - but I'd take it :)
    Todd McDermid's Blog
    Friday, December 04, 2009 2:57 AM
    Moderator
  • Thanks Todd,

    I'm working on Adding a Macro....I'll get back to you as soon as I find something...
    Unknown
    Friday, December 04, 2009 2:35 PM
  • I don't know about "owe" - but I'd take it :)
    Todd McDermid's Blog

    Nah, instead they gave you a trip to Seattle in February...  ;)
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, December 04, 2009 3:02 PM
    Moderator