How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by M. Deschene » How to Dynamically and Iteratively Populate...

59 posts, Page 2 of 6

««1 2 3 4 5 »»»

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

mdeschen1

Posted Wednesday, January 09, 2008 11:50 AM

Grasshopper

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

Philippe Cand

Posted Wednesday, January 09, 2008 12:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:28 AM
Points: 46, Visits: 262

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

mdeschen1

Posted Wednesday, January 09, 2008 12:31 PM

Grasshopper

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

Aseda

Posted Wednesday, January 09, 2008 1:37 PM

Forum 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

mdeschen1

Posted Wednesday, January 09, 2008 1:57 PM

Grasshopper

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. Smile

Post #440903

imaddaou

Posted Wednesday, January 09, 2008 4:46 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 31, 2008 9:20 AM
Points: 10, Visits: 24

SQL Server 2005

Post #440944

Philippe Cand

Posted Wednesday, January 09, 2008 5:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:28 AM
Points: 46, Visits: 262

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

rdobrich

Posted Thursday, January 10, 2008 2:22 AM

Grasshopper

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

imadaou

Posted Thursday, January 10, 2008 4:16 AM

Forum 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

rdobrich

Posted Thursday, January 10, 2008 5:05 AM

Grasshopper

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 »

59 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions