September 29, 2010 at 6:43 am
I have an excel workbook that is basically an invoice layout. This also has a couple charts, graphs...
I need to take data FROM SQL, using a stored procedure and filling the form with data.
so as an example
Cell A1 Might have the text: 'Company:' in it as a heading
In cell A2 I want to insert data from SQL to say 'ABC Company'
Cell F20 a heading of 'Invoice Total:'
Cell F21 I want to insert a SQL value, say '$234.54'
Does anyone know how I could accomplish this?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 7:53 am
Looking into your job-title I can recommend hiring some programmer with SQL and/or SSIS skils.
This work can be done in Excel using Excel VBA or any other programming language which can talk to SQL and Excel (eg. VBSript, VB, any .NET).
Also, it is possible to do it in "pure" T-SQL calling Excel COM objects...
September 29, 2010 at 8:13 am
Right....
I am looking for some recommendations into doing this natively through Excel. Not sure how my job title plays into this however any recommendations on the coding FROM a SP would be great.
I can insert into Excel from SQL now, save the workbook, zip it, email it; all from SQL SP.
I need to accomplish this same task however I now need to 'fill in the blanks' rather than a simple row of data.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 8:43 am
Jeffery Williams (9/29/2010)
Right.......Not sure how my job title plays into this however any recommendations on the coding FROM a SP would be great.
...
Sorry mate, your job-title has quite a play in your request...
Althought it is theoretically (and may be even - practically) posible to accomplish what you ask within SQL Server stored proc (you have to call Excel COM objects to do so from sp and I've mentioned it in my first post), however SQL Server is not the right tool for this task. You better be writing small app (in any of mentioned languages, including Excel VBA).
Therefore your job-title will play a role in insisting that it should be done in sp 😉
September 29, 2010 at 8:51 am
I suppose I see your point to some extent. HOWEVER.
1. I am not a application developer however I do code in SQL. For this reason SQL is my resource to get this done.
2. I am a 'working' manager so I get in the trenches as it were..... I like to code.
3. Departmental budgets do not allow for me to hire another resource for this task.
Yes the application is pretty small, not heavy lifting / impact-ful.
We live in times of wearing multiple hats and we do not always have the luxury of just spinning work off to others. At times we need to resourceful and pull some magic out of our hats; that is what I am trying to accomplish here.
I am aware and agree that doing this by way of a SP is not a preferred. It is however what I have available as a tool to get the job done. Under other circumstances I would not approach this challenge in this manner. A .net application would be perfect for this, no question.
All of this said I still have this challenge in front of me. I will do some more searching on the topic, and play with it on my own a bit. I know I will get it done; I just thought someone here would have some insight, perhaps they have done it already and could share some code.
Sorry to bother; apparently my question was somewhat out of line.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 9:19 am
Jeffrey,
If you're doing this from a SP, then I assume that you're using the OpenDataSource xp to put the data in?
If so, then you will need to specify the column/row that you want to use in the "table" designator of that function:
Sheet1$:A2
Sheet1$:F21
(I think this is the right syntax)
You can also set up named ranges on those cells, and then use just that for the table name:
NamedRangeA2
NamedRangeF21
(Note the lack of the trailing $ - the $ denotes a sheet name, the lack thereof denotes a named range.)
Post back if you have any other questions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 29, 2010 at 9:32 am
there's a great article on SimpleTalk on the subject.
it's got examples of doing it two ways, via opendatasource and also Excel Automation via sp_OACreate and stuff;
this article is doing it all in SQL:
SQL Server Excel Workbench[/url]
as a 2007 article oriented towards SQL 2000 (it uses JET drivers and sp_makewebtask) you might need to extrapolate and use the new ACE drivers for newer versions of office .
Lowell
September 29, 2010 at 9:40 am
Wayne,
Thank you so much for your reply. Ok I am not using the OpenDataSource method... Here is the code I am using to generate a simple spreadsheet now:
USE [eDirect]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GenerateExcel
(
@db_namevarchar(100),
@table_namevarchar(100),
@file_namevarchar(100)
)
as
--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\test.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -t -c'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -t -c'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'" '''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+' -t '''
exec(@sql)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
To that end the above only create header row and then data rows. Works great for its intended use.
NOW... A simple way to accomplish what I need here (just thinking outloud) would be to insert again a simple row of data into a workbook, without formatting. Now in this case I would need to have a template workbook if you will in place that has the first sheet as the invoice layout and a second sheet that I would insert data into.
The first sheet can point to the second sheet cells to populate itself.
All that said still not sure how to work with an existing workbook, AND insert the data into the second sheet.
I will read up on the OpenDataSource method you mentioned. I just don't have any experience working with Excel from SQL... I will have soon however 🙂
You have been a great information source now and in the past Wayne, thank you.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 9:41 am
Lowell,
Thank you for the link. I will be reading this at lunch as well.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 9:53 am
Jeffery Williams (9/29/2010)
Right....I am looking for some recommendations into doing this natively through Excel. Not sure how my job title plays into this however any recommendations on the coding FROM a SP would be great.
I can insert into Excel from SQL now, save the workbook, zip it, email it; all from SQL SP.
I need to accomplish this same task however I now need to 'fill in the blanks' rather than a simple row of data.
The discussion is going back and forth on a tangental subject; what's the goal, to do this user-on-demand from within Excel, or due to whatever triggers the SQL stored procedure?
User on demand: Excel VBA macro
All within SQL SP: what exactly are you missing (because this narrative is not clear), the mapping of each individual value to a specific cell?
September 29, 2010 at 10:41 am
Yes.
Right now I know how to create a NEW workbook and insert a row, or multiple rows of data.
In this example I need to open an EXISTING workbook that contains a layout. Has headers arranged around the workbook to form a nice looking invoice.
I need to be able to open the workbook (access it) from SQL, and insert values from my tables into specified cells on the workbook.
Then I will save the workbook as a unique name (I will be accessing a template workbook that will be reused each month).
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 11:09 am
Jeffery Williams (9/29/2010)
Wayne,Thank you so much for your reply. Ok I am not using the OpenDataSource method...
NOW... A simple way to accomplish what I need here (just thinking outloud) would be to insert again a simple row of data into a workbook, without formatting. Now in this case I would need to have a template workbook if you will in place that has the first sheet as the invoice layout and a second sheet that I would insert data into.
The first sheet can point to the second sheet cells to populate itself.
All that said still not sure how to work with an existing workbook, AND insert the data into the second sheet.
I will read up on the OpenDataSource method you mentioned. I just don't have any experience working with Excel from SQL... I will have soon however 🙂
This little bit of code I use to update an Excel spreadsheet. Should be pretty obvious how to make it perform an insert. You'll need to do a "CREATE TABLE" first to make the worksheet.
update xl
set xl.[New SQL Name] = #temp.[New SQL Name]
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\Documents and Settings\WayneS\Desktop\Application Document Class Properties.xls";Extended Properties=Excel 8.0')...[Sheet1$] xl
INNER JOIN #temp ON xl.[Original Order] = #temp.[Original Order]
To insert into the second sheet, use [Sheet2$] - or whatever you name the sheet. HOWEVER - do not put spaces in that name (I've never been able to get anything to work if the worksheet name has spaces).
You have been a great information source now and in the past Wayne, thank you.
:blush: Glad to help, and more glad that you think so.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 29, 2010 at 11:14 am
No really it is greatly appreciated.
I found some things on the link given in this thread by another user. I am having a problem with that code and I think I will have the same issue with yours.
I think the problem lies in: Microsoft.Jet.OLEDB.4.0'
I am getting the following error which I am about to start to google (Unless of course you already know 🙂 )
Msg 50000, Level 16, State 1, Procedure spExecute_ADODB_SQL, Line 53
Error whilst Opening Connection to XLS, for file Create or Append, Provider cannot be found. It may not be properly installed.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 11:16 am
Ok I just read that there is not a 64 bit version of this ( Microsoft.Jet.OLEDB.4.0 ) and I am on a 64 bit machine.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 29, 2010 at 11:46 am
You'll probably need to play around with the Access 2010 64-bit driver then, which seems to be the Jet driver replacement.
I haven't had a chance to work with this myself, though it is on my list of things to investigate and play with. Specifically, I don't know if it is backwards compatible with the 97-2003 Excel spreadsheets.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply