SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access Excel Hyperlinks from SQL


Access Excel Hyperlinks from SQL

Author
Message
Philip-1144230
Philip-1144230
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 50
Valuable input is always welcome.

Seems I'll have to complete some SQL courses in the near future along with a lot of practical examples before I take on another SQL based project.

That said, I'll still have to solve this one.

Thanks again for all the help, appreciate it.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19441 Visits: 14398
Anytime, good luck. If you have a moment to circle back, I would be interested to hear about where you landed, and how you got there. Of course if you hit a snag post back, or make a new thread. Here to help.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2635 Visits: 2490
Philip-1144230 (1/10/2013)the other two would require me to manually go through roughly 12000 excel files with a average of 15 worksheets each to extract the hyperlinks


Philip, it sounds like you have already resolved to make some changes regarding future requests, but that you still have to solve this problem. VBA is very powerful, and from it you can do any kind of file handling. It would be relatively easy to loop through your files, flip through the pages, and expose the links. Granted, it would probably take a little while for the script to run. This would be a good option, *if* the links are *always* in the *exact* same location. If you want to go this route, there are lots of resources out there to help get you started.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19441 Visits: 14398
Greg Snidow (1/10/2013)
Philip-1144230 (1/10/2013)the other two would require me to manually go through roughly 12000 excel files with a average of 15 worksheets each to extract the hyperlinks


Philip, it sounds like you have already resolved to make some changes regarding future requests, but that you still have to solve this problem. VBA is very powerful, and from it you can do any kind of file handling. It would be relatively easy to loop through your files, flip through the pages, and expose the links. Granted, it would probably take a little while for the script to run. This would be a good option, *if* the links are *always* in the *exact* same location. If you want to go this route, there are lots of resources out there to help get you started.

The C# code I provided does exactly that (flips through all cells in a Worksheet). And you are right, it is painfully slow. If you know the column and defend against the possibility of there not being a hyperlink on the cell it's not tough code to write and could perform acceptably if the scope were limited. The question of where to host the code though, that's the big decision on this one in my opinion.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Philip-1144230
Philip-1144230
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 50
Hi Guys,

I've gone back to the client with regards to it specifically being a sql procedure that needs to do the import.

Waiting on their response now before I go any further with this.

Thanks for all the responses up till now.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2635 Visits: 2490
opc.three
The C# code I provided does exactly that


Ah, my bad then. I'm C# illiterate (and all flavors of C for that matter). Now that you pointed that out, I can kind of see how it works. So I'm using SQL Server 2012 express (my company does not use SQL Server, I just use it for handling my data). Is it possible for me to do things like you have done above? Or do I need a paid version to be able to use CLR's and such?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19441 Visits: 14398
Greg Snidow (1/11/2013)
opc.three
The C# code I provided does exactly that


Ah, my bad then. I'm C# illiterate (and all flavors of C for that matter). Now that you pointed that out, I can kind of see how it works. So I'm using SQL Server 2012 express (my company does not use SQL Server, I just use it for handling my data). Is it possible for me to do things like you have done above? Or do I need a paid version to be able to use CLR's and such?

No worries. I get that C# is not read as cleanly (or sometimes at all) on this site as T-SQL ;-)

I would not recommend using the code I showed in a SQLCLR object. If you must something like this that deals with hyperlinks in Excel, I would look to put the code in an SSIS package Script Task or Transformation Component, or into a stand-alone C# app, and run it on an application server, i.e. not the server running SQL Server hosting the databases.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search