Solved

Best way to script pulling values from SQL db, place in specific cells in spreadsheet, then get calculated values back out of excel into SQL

Posted on 2011-03-07
9
431 Views
Last Modified: 2012-05-11
I've been tasked with a project to get data from a web form (done), place said data into an excel spreadsheet with extremely complex formulas set up (either directly or after being placed into a SQL database -- not done), get the resulting information from the excel spreadsheet back into another Table (not done), and then spit the result back from the SQL table into PHP (not done, but not a problem). This all needs to be done w/o intervention on the server.

Server is a Win 2k3 server running IIS 6, and will have Excel installed. Test machine is my Win7 laptop with IIS 7 and Excel installed. I have the connection to the DB set up and working properly.

I'm looking for code examples that could be used to accomplish this task. I'm not asking you to write my code for me (how would I learn from that?), but would love to see some snippets to get from A to C (SQL to Excel to SQL).

I'm not looking for Macros inside of Excel necessarily, but if this is the only way to get what I need, and it can all be scripted to launch automatically, I'm ok with it.
0
Comment
Question by:TimBare
  • 5
  • 3
9 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
ID: 35059767
You'll want to use SQL Server Integration Services

Its actually extremely easy to use. The linked article has a simple example of how to import data from Excel into SQL Server:

http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

The reverse if essentially the same process.
0
 
LVL 2

Author Comment

by:TimBare
ID: 35060144
reading through the article, and i'm having some issues:

1) setting up a test spreadsheet with simple values as a proof of concept, when i set the .xls as the data source, I'm not seeing how to select a specific cell to export to the value to the DB. (all I get is columns F1 and F2).

2) i don't quite see how this is automated.

Any other documentation that you can point me to? I know I could Google it and sift through the mounds of results, but do you know of any specific articles that could help me out a bit more?

Thanks,
T
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 35060650
This should help - once set as linked server you can run distributed queries against it:

306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries

http://support.microsoft.com/kb/306397
0
 
LVL 2

Author Comment

by:TimBare
ID: 35061781
because my brain hurts...
In the Provider list, click Microsoft Jet 4.0 OLE DB Provider

Open in new window


I don't have that in my list. I do have the .dll in my syswow64 folder.

'regsvr32 msjet40.dll' gave me an error: "the module msjet40.dll failed to load..."

any thoughts? I really don't want to have to re-create all of the formulas in this spreadsheet (that tend to change, and i won't be the one that has to maintain them...) in php functions... I'll do it if I have to, but the boss really wants to see it work w/ the excel files...
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 39

Expert Comment

by:lcohan
ID: 35072741
So you have SQL 2005 installed on Win 64 bit OS? Sorry that wasn't explicitely mentioned and the solution works with "Excel spreadsheets - > Microsoft.Jet.OLEDB.4.0 (32-bit only)"
I think you may need to install that version and should be able to use it as documented.

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/d5b29496-d6a1-4ecf-b1a4-5550d80b84b6

http://stackoverflow.com/questions/1991643/microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine
0
 
LVL 2

Author Comment

by:TimBare
ID: 35072838
Sorry about that. I didn't realize that 32 vs 64 bit OS made a difference for the question. I'll be more explicit from here on out.

To re-iterate what I think i'm seeing on those links, If i'm using Win7 64 bit, and office 2007, I'm out of luck. But, if I install Office 2010 and use Microsoft Access Database Engine 2010 Redistributable, I should be able to accomplish what I'm trying to do?

Again, I need to be able to pull a row from a SQL DB, and place values into specific rows on a spreadsheet, then pull different cells back out from said spreadsheet.

Thanks for the links!
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35072996
Although I do not have the environment to test that at the moment with SQL 2005 that should do it. The only issue you were having was the provider that is not available for 64 bit but this workaround should provide you the suppost for your linked server. After that the queries should be the same as in the first link example except for the provider name of course but should serve your purpose. Please use a test environment first as I don't think you want to install anything directly in a production environment.

Also,

<< I need to be able to pull a row from a SQL DB, and place values into specific rows on a spreadsheet, then pull different cells back out from said spreadsheet.>>

therefor can I as why those calculations (I suppose macros in excel) cant be moved inside SQL Stored procedures if data is already in SQL? It is much more easier and faster to perform those operations plus it would save you a lots of headaches for now and the future - that's my opinion.
0
 
LVL 2

Author Comment

by:TimBare
ID: 35073096
therefor can I as why those calculations (I suppose macros in excel) cant be moved inside SQL Stored procedures if data is already in SQL? It is much more easier and faster to perform those operations plus it would save you a lots of headaches for now and the future - that's my opinion.

I the calculations aren't in the SQL database. Basically, my boss has a very complex quoting system in an excel spreadsheet with more formulas than i care to reproduce. He's already got it set up you enter a few pieces of information, and it does all of the calculations on the spreadsheet, and offers the quote.

As of now, he's sending this spreadsheet out to specific customers, allowing them to get their own quotes. However, we don't know how many times they're quoting their own projects, and therefore, we don't know which jobs we lose.

He wants to make this available online, and store the information that the client fills out in a database, so we can see which jobs we get, and which we don't.

So, the process is Web Form -> Temp SQL database -> Excel Quoter -> SQL -> webpage displaying the quote to the customer.

I know it's not the best way to do this, but we were hoping that it was something that we could throw together relatively quickly (relative to reproducing the entire quote engine in PHP) so that we could start tracking the quotes.

I suppose I could find a 32 bit machine to do this testing on, but I like my laptop as I tend to work on it from home at night, so this is the most convenient machine for development.
0
 
LVL 2

Author Comment

by:TimBare
ID: 35084798
I suppose I could find a 32 bit machine to do this testing on, but I like my laptop as I tend to work on it from home at night, so this is the most convenient machine for development.

So, I'm trying this on my 32 bit Win 7 desktop w/ Office 07 installed. I've installed Sql Server 05, but I still don't see the Jet OLEDB 4.0 provider.

Any thoughts on getting this up and going?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now