November 22, 2011 at 11:26 am
Please forgive me if this topic has been beaten to death. I am by no means an expert and I have a problem I'm trying to solve.
Currently we have an application that is written in Access, but we use a custom template in Excel to allow users to design their own reports. Access reads the spreadsheet and based on what information the user has indicated is to be loaded in a cell, Access calls a series of Stored Procedures on SQL Server, loops through the resulting data and loads it back into Excel using OLE Automation.
The problem is the speed... The stored procedures finish quickly but the Access program takes a long time to load the data back into the Excel file cell by cell.
It occurred to me that since SQL Server can store and manipulate XML data, that it should be possible to load the Excel file into a SQL Server table (formatting, formulas, and all), use T-SQL to read through the table and perform all the updates, and then export it back into an Excel file with all formatting and formulas in tack.
Is this possible? Can someone turn me on to how to do it?
Thanks,
JDR.
November 22, 2011 at 11:49 am
If the major issue is the export time into Excel you might look into changing this method before completely rewriting the code:
Do you have some sort of WHILE loops that update one cell at a time? If so, can you change it to update a row or a column at once?
Can you link the Excel file into ACCESS and use it as a "table"?
From my point of view, there's not enough information to provide a more detailed answer.
Regarding your question:
Yes, SQL Server can work with XML data. But I'm not sure if it's a good solution to send data manipulation statements via Excel cells.... That just doesn't sound right. But I might misunderstand the complexity...
November 22, 2011 at 1:05 pm
The data is indeed loaded into Excel in a loop..cell by cell. This is because each cell is the intersection of Column criteria and row criteria. After the needed data is complied in a normalized table by stored procedures, the loop reads each cell, determines which records from the normalized tables are to be summed and inserted in that cell.
I think the overhead is in the Access OLE automation to read each cell, then query SQL Server, and then use OLE Automation to update the spreadsheet. If everything were in the SQL Server database I think the time to process would be a fraction of what it currently is.
I saw in another thread someone said you can load the Excel XML file into a SQL table.. but no explaination of how...
Can someone point me to an article or give me a code sample?
November 22, 2011 at 1:29 pm
If performance is a concern, you should not set values cell by cell. Create an array and assign it to a range.
You may wish to check http://support.microsoft.com/kb/302084.
November 22, 2011 at 1:45 pm
Can you provide an example of what you're trying to accomplish?
(E.g. sample of the normalized table and the final Excel result including an explanation of the transformation rules).
To load an Excel XML file into SQL Server you could use a linked server, OPENROWSET or XML bulk load. Which one to use depends on the given scenario (that is still not described at a level where a recommendation can be made IMHO).
November 22, 2011 at 2:06 pm
I agree if we could do set-based updates it would be better. However, as long as its still in an Excel file that's not an option.
If I can get the data out of Excel and into SQL Server then I might be able to make some architectural changes.
November 22, 2011 at 2:19 pm
Technically, getting data from Excel to SQL Server or back is not biug deal -- that's what Active Docs are about, and there is a project type for that in Visual Studio.
The big problem with Active Docs is security.
Would you loike to update SQL database from an Excel workbook but you don't know why, or you do know how but just don't have a permission to do that?
November 22, 2011 at 3:27 pm
Here is a more detailed explaination of what I am doing...
I am using Excel as a modeling tools for a report writer. The actual business logic and data are in SQL Server.
Users layout the report in an Excel workbook specifying content by Row and Column.
SQL Server stored procedures collect the data in a set of normalized tables
Access reads the results, and maps the data into the cells of the spreadsheet.
An overly simple Example:
Row 1 - Values A,C,F
row 2 - Values Q, R, S
row 3 - Values P, M, K
Column A - Budget
Column B - YTD
Column C - Variance (formula)
We extract Budget for A-Z in Store Procedure to temp table
We compute YTD for A-Z in Stored Procedure to temp table
Loop through Excel workbook.. Query the temp tables by Row values & for each column and update the Excel workbook.
1A = sum Budget for A+C+F.
1B= sum YTD for A,C,F,
2A=sum budget for Q+R+S,
2B=sum YTD for Q+R+S
etc. etc.
I want to do this all in SQL Server tables and then write out the file retaining all the formatting, macros, and formulas, instead of the present solution which is a mixture of SQL, with Access using OLE Automation to update Excel.
Can someone point me to an article or white-paper with more info on reading Excel XLSX files into SQL Server, and writing them back out again preserving everything?
November 22, 2011 at 4:45 pm
To repeat what I stated before:
To load an Excel XML file into SQL Server you could use a linked server, OPENROWSET or XML bulk load. Which one to use depends on the given scenario (that is still not described at a level where a recommendation can be made IMHO).
What you posted still doesn't really help to fully understand what you're looking for:
How do you know what table/view to select from?
Where do you get the information from, what column the "Values A,C,F" refer to?
How do you know to calculate the sum for the budget and not the average or difference?
if you're trying to import the data as XML file, you'll need to save the xls file as an XML structure. Then you'd need to decide which method as mentioned above would fit best to load the data into SQL Server. Then you would need to shred the XML data and/or to manipulate it directly using XQuery. There are several methods.
All I can do is to point you to google using the keywords I mentioned above.
Please keep in mind: neither do we know the business case you're dealing with nor the current solution you're using. We can't look over your shoulder either. All we have is what you've posted so far. And that's rather vague so I can't point you to a specific solution.
November 22, 2011 at 5:20 pm
jdrintnt (11/22/2011)
. . .Can someone point me to an article or white-paper with more info on reading Excel XLSX files into SQL Server, and writing them back out again preserving everything?
There is no such feature. Sorry.
November 22, 2011 at 11:28 pm
Revenant (11/22/2011)
jdrintnt (11/22/2011)
. . .Can someone point me to an article or white-paper with more info on reading Excel XLSX files into SQL Server, and writing them back out again preserving everything?
There is no such feature. Sorry.
I tend to disagree: it should be possible to load the xls data (saved as xml format) into SQL server and either shred the xml, modify it and generate the new XML file or use XQuery directly against the XML (when loaded into SQL Server).
However, this is "slightly" beyond level 100 though...
But to prvide a solution we'd need a lot more detailed information together with some more descriptive sample data.
November 23, 2011 at 9:22 am
LutzM (11/22/2011)
Revenant (11/22/2011)
jdrintnt (11/22/2011)
. . .Can someone point me to an article or white-paper with more info on reading Excel XLSX files into SQL Server, and writing them back out again preserving everything?
There is no such feature. Sorry.
I tend to disagree: it should be possible to load the xls data (saved as xml format) into SQL server and either shred the xml, modify it and generate the new XML file or use XQuery directly against the XML (when loaded into SQL Server).
However, this is "slightly" beyond level 100 though...
But to prvide a solution we'd need a lot more detailed information together with some more descriptive sample data.
If you custom-parse Excel workbook, you are working against your tool. SQLS does not have native tools to handle, store and spit out Excel workbooks including formatting.
November 25, 2011 at 10:39 am
I just came across this link (Replicating Excel 2003 Formulas to SQL Server).
Might be worth looking into it.
November 25, 2011 at 9:17 pm
That looks like a great article, not exactly what I asked for, but it looks like it may be even better.
Thanks...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply