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 ««12345»»»

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server Expand / Collapse
Author
Message
Posted Wednesday, January 09, 2008 11:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 30, 2009 8:07 PM
Points: 15, Visits: 40
SQL Server Version = 2005

Not sure about pushing to a sharepoint doc. Can you give me some more details? You want to push to a specific type of doc? Or is it that you want to push TO the sharepoint?
Post #440841
Posted Wednesday, January 09, 2008 12:04 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:24 AM
Points: 48, Visits: 273
That would be creating on the fly a new Excel in SharePoint doc library with a path similar to this
http://smportal.onsemi.com/C10/Reporting%20Projects/Development%20Process%20and%20Templates/MyWorkbook.xls


BI Guy
Post #440850
Posted Wednesday, January 09, 2008 12:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 30, 2009 8:07 PM
Points: 15, Visits: 40
to create the file on the sharepoint site I would try to treat it just as a file address. That means in your variable (see the article for specifics) I would specify the path:
SHAREPOINT_FILESYSTEM_ADDRESS\20Templates/"+(DT_STR.25.1254)@[User::YOUR_VARIABLE]+"xls"

Ok, sharepoint has a real, physical filesystem. Push the file to there. Make the directory/folder always visible on sharepoint OR overwrite with the same file name each time and just have the file visible (this was done at one of the sites I was at last year).

Let me know how it goes.
Post #440863
Posted Wednesday, January 09, 2008 1:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 8:48 AM
Points: 4, Visits: 245
--SQL Server and call xp_cmdshell to modify/re-massage/rename/move/.. your xls file.
how do you do this? i am using xp_cmshell to output data from a sql table to different tabs in excel. I only get the last set of data in the cursor in the first tab
Post #440893
Posted Wednesday, January 09, 2008 1:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 30, 2009 8:07 PM
Points: 15, Visits: 40
Forum Newbie: Is the last question aimed at this article? The article does not use:xp_cmshell

But I can say that if you populate the tab cursor from the SQL Execute Task before the Foreach Loop you will definitely get a different tab for each value. Now, to populate each tab with the correct values you need a variable set up for the data pull (cols etc etc) that is also part of the solution.

IF you want to use xp_cmshell I am afraid I cannot help you.
Post #440903
Posted Wednesday, January 09, 2008 4:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 31, 2008 9:20 AM
Points: 10, Visits: 24
SQL Server 2005
Post #440944
Posted Wednesday, January 09, 2008 5:31 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:24 AM
Points: 48, Visits: 273
Great, It works perfectly.
I just have to put together the mechanism with the variables.
so instead of an URL not supported by JET, use an UNC
In my example I simply have to use variables or configurations to build the path.
instead of the original
http://smportal.onsemi.com/C10/Reporting%20Projects/Development%20Process%20and%20Templates/CalendarWeeks.xls
I use this
\\smportal.onsemi.com\C10\Reporting Projects\Development Process and Templates\CalendarWeeks.xls


BI Guy
Post #440956
Posted Thursday, January 10, 2008 2:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 14, 2011 2:11 AM
Points: 24, Visits: 43
Hi,
I finaly did it and it works realy great.

There is one thing I had to change.
I had a problem (or I didn't get it right) with Execute task (to create new TAB within same exec file).
When I changed variable ExcelTab to something like this

"CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)"

works perfectly

Thanks for the article. I realy find a lot of interesting stuff in it.

radovan



Post #441047
Posted Thursday, January 10, 2008 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 20, 2009 11:12 AM
Points: 1, Visits: 9
Can you please list all variables you used?

I was able as well to make the package work by using "CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)" before adding the Dataflow task. When I added the dataflow task and assigned the variable to the Excel Destination it didn't alow me. what did you use for the variable ‘Subgroups’ ?

Thanks in advance
Post #441087
Posted Thursday, January 10, 2008 5:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 14, 2011 2:11 AM
Points: 24, Visits: 43
Hi,
I little change sample, so I use this variables:

1)
FilePath='c:\temp\sample.xls'
(but, I can dynamicly create file name through expression properties, like
"c:\\temp\\test_" + substring((DT_STR, 50,1250) GETDATE(),1,10)+".xls"
if I need file name
c:\temp\test_2008-01-10.xls

EvaluateAsExpression property needs to be 'True'

2)
In Excel connection manager in Expression Property I set 'excelFilePath' to @[User::FilePath]

3)
ExcelTab Variable is set throuh Expression to
(EvaluateAsExpression property needs to be 'True')

"CREATE TABLE `" + @[User::region] + "` (
`my_field` VARCHAR(50)
)"

4) In Execute SQL Task, I Set
Connection Type to 'Excel'
Connection to existing Excel connection manager (created in step 2)
SQl source type to 'Variable'
SourceVariable to 'User::ExcelTab' (created in step 3)

Execute workws perfectly if excel file with @[User::region] doesn't exists.
It will fail if it exists.

radovan







Post #441111
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse