SQL / Excel question

[BigV]

I am trying to import data from an Excel worksheet into a SQL database, using DTS. The column in the worksheet that I need the most is filled with hyperlinks, ala =HYPERLINK(linklocation, friendlyname). When I import the data, all I get is the friendlyname. :grr:

Here's the question. Does anyone know of a DTS transformation to read the contents of the Excel cell and extract the linklocation? Or, failing that, does anyone know of an Excel function (macro, formula, etc non-manual way) that will do the same thing, take the contents of the cell containing the hyperlink and only display the linklocation part?

MUCH thanks in advance. I will pay a bounty to the tip jar in the name of the puzzle solver for an answer today.

[BigV]

:crickets chirping:

[Clodfobble]

I tried to help, BigV--I know nothing about SQL but am usually pretty handy with Excel, and thought I could find a way to copy/paste the information you want into a new set of cells... but I can't actually even create a cell in Excel with the =HYPERLINK(linklocation, friendlyname) format. It keeps giving me an unspecified error... Can you perhaps paste here the exact contents of one cell?

[BigV]

I have to jet off to a meeting with my boss in five minutes. I will answer your question upon my return.

Thanks for the help.

[MaggieL]

He's talking about a cell like this:
=HYPERLINK("http://www.google.com","bigSearchEngine")

It would be easier if his data was like:

-+---------A------------------------ B-------------------------C
1| =HYPERLINK($B$1,$C$1) | "http://www.google.com" | "bigSearchEngine"


In fact, it would be trivial...his desired reference would be =B1. As it is, I'd be tempted to export the
formulas from column to a flat file and massage them with a text editor.

Gwennie gets paid to answer Office usage questions like this at PCHelps.

[SteveDallas]

Originally Posted by MaggieL

As it is, I'd be tempted to export the
formulas from column to a flat file and massage them with a text editor.

If I were doing it I'd export to text, then use perl. Something like

($garbage, $url,$comma,$name,$paren) = split (/\"/);

You could then output the values of $url and $name as you liked. (Nothing magic about that, just that I know perl better than other alternatives at hand.)

[mbpark]

BigV,

I'd create another cell, and use the function from here:

http://www.ozgrid.com/VBA/HyperlinkAddress.htm

and import the calculated cell instead of just the hyperlink. DTS should recognize calculated cells just fine .

[BigV]

MaggieL:

Very good explanation of my situation. It is incomplete, though. While the book reference you describe is accurate, the contents of the problem cells on my worksheets achieve the same result with the Insert/Edit Hyperlink wizard. While the wizard is very good and helpful, it hides the linklocation data *somewhere* I can't find yet.

For example:

=HYPERLINK("http://www.google.com","bigSearchEngine")
is not the same as (see attached image).

Unfortunately, when I inspect the contents of the cell with the hyperlink created with the wizard, all I see is the friendlyname. When I manually recreate the same link using the =hyperlink(linklocation, friendlyname) syntax, it behaves the same way except the contents of the cell is "=hyperlink(linklocation, friendlyname). Now I have a string I can possibly work with.

I'm stuck at finding a way to get at the wizard created hyperlinks' linklocation data. I can edit the hyperlink and manually extract/copy the information, but that is tedious and filled with potential operator error, especially in light of the fact that there are thousands of rows.

Originally Posted by MaggieL

It would be easier if his data was like:

-+---------A------------------------ B-------------------------C
1| =HYPERLINK($B$1,$C$1) | "http://www.google.com" | "bigSearchEngine"

Excellent example, and correct, but my worksheet's not there yet. I don't have a way to get all of the data inside the parentheses out yet.

My offer to hit the tip jar stands. I would happily pay the cellar on Gwennie's behalf for an answer.

[BigV]

mbpark:

Thank you! That is very very promising, but my VBA skills are weak. I followed the directions, and I get an interactive display of the result, but the cell does not contain/display the result, by which I mean the linklocation. The link location does show up in the interactive gadget created by the user defined function, but I don't know how to get that substring out and into a cell somewhere.

The =GetAddress function performs the getting of the address, but only while the function is running. I'm looking for some way for the function to *evaluate* to the linklocation.

[mbpark]

BigV,

I just did this:

If you create the other cell (in my case B1), and set the cell value to =GetAddress(A1), and do a .CSV export, it will save the evaluated link location.

You can then import the .CSV via DTS.

[BigV]

Not for me.

For the cells that I *manually* create a hyperlink using the =hyperlink(linklocation, friendlyname), the =getaddress works ok.






scratch that.


NOW, having tried it again, it's behaving as expected with the wizard hyperlinked created cells. ooook, now I have to see if I can duplicate the effect on the live file and not my test file. Be Right back.

[BigV]

mbpark--

I hit the tip jar in your name (no, it didn't clank. Folding money doesn't clank). THANK YOU very much for your help. That solved problem. Well, at least that made the way clear to the next problem.

Next week--fun with DTS!

[MaggieL]

Damn...I tried GetAddress() too. I just didn't try to export it.

Nice.

[MaggieL]

Originally Posted by SteveDallas

If I were doing it I'd export to text, then use perl.

I guess I'm spoiled; the editor I use most often (When I'm not using IDEA, anyway) understands regexes.
And Beanshell too...:-)

[BigV]

In the interest of completeness, I haven't gotten to the DTS/export stage. I hope I don't have to export it at all.

I intend to use the DTS package to scan the Excel file and extract portions of rows where the date meets some as yet undefined criterion. Once I have that subset of rows in the temporary table in SQL, I need to send an email to some users. In that email, I want the \\server\directory\filename to appear. =GetAddress() displayed that text in another cell in the same row in the Excel file. I reckon I can pick that up in the DTS package.

One problem =GetAddress() revealed was the sloppy condition of the data in the linklocation part of the cell. I have...inconsistent representations of the locations and names of the files. Specifically, spaces and %20 are freely interchanged. =GetAddress() chokes on the first %20, displaying only what follows the %20, ignoring the leading part (where the server directory and partial filename exist).

I set the owner of the file on the task of normalizing the filenames. We'll see next week how things are looking up.

Thanks to all for your help and interest.