Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>