Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Access Excel Hyperlinks from SQL Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:33 AM
Points: 14, 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.
Post #1405469
Posted Thursday, January 10, 2013 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
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
Post #1405481
Posted Thursday, January 10, 2013 9:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #1405518
Posted Thursday, January 10, 2013 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
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
Post #1405521
Posted Friday, January 11, 2013 1:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:33 AM
Points: 14, 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.

Post #1405797
Posted Friday, January 11, 2013 7:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #1406032
Posted Friday, January 11, 2013 8:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
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
Post #1406106
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse