How to write results of a sp/query to multiple .csv/xls files

9 Sep 2005 5:05 PM

26point2er

I skimmed trhrough this group for possible solutions for my problem.

I have a stored procedure that accepts 1 parameter.

What would best way to run this SP 400+ times each time write the results to
a separate .csv/.xls file?

In otherwords I need 400+ files.

9 Sep 2005 5:15 PM

ML

Please post some DDL. Anyway, if something needs to ne done several times,
then the word 'loop' comes to mind...

ML

9 Sep 2005 5:42 PM

26point2er

Thanks for the response, but how? Let me explain further.

here is my stored procedure(SP)

sp_get_office_info(@office)

there are 400 plus offices and each office needs to have
its information stored in a file(.csv/.xls).

This way the 400 plus files can be emailed out the respective office managers.

Show quote

"ML" wrote:

> Please post some DDL. Anyway, if something needs to ne done several times,
> then the word 'loop' comes to mind...
>
>
> ML

9 Sep 2005 5:55 PM

ML

We really, really need to see some DDL. Please follow instructions here:
http://www.aspfaq.com/etiquette.asp?id=5006

Then we'll all see what exactly it is that your procedure does and suggest
how to make it do all that 400 times if need be.

ML

9 Sep 2005 6:04 PM

26point2er

ML,

Thanks and sorry.

DDL

--
CREATE TABLE [t_final] (
    [pctincrease] [tinyint] NULL ,
    [PoolAmount] [numeric](9, 2) NOT NULL ,
    [Manager] [varchar] (112) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pc] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [lname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Title] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FTorPT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HireDt] [datetime] NULL ,
    [HourlyRate] [decimal](16, 6) NULL ,
    [AvgHrs] [decimal](19, 2) NULL ,
    [EmpNo] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AnnSalary] [numeric](24, 6) NULL ,
    [ProjIncrease] [numeric](31, 8) NULL ,
    [LastPerfRvDt] [datetime] NULL ,
    [PerfRating] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [NextSalRvDt] [datetime] NULL
) ON [PRIMARY]
GO

My query would be

Query
--
Select * from t_final
where pc = @parameter
--
There are over 400 PCs.
The results of each query run must go to .csv/.xls files.

Thanks

Show quote

"ML" wrote:

> We really, really need to see some DDL. Please follow instructions here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> Then we'll all see what exactly it is that your procedure does and suggest
> how to make it do all that 400 times if need be.
>
>
> ML

9 Sep 2005 7:55 PM

ML

Perhaps something like this:

declare @pcList table(ListID int identity (1, 1), pc varchar(4))
declare @firstId int
declare @lastId int
declare @currentPc varchar(4)

insert @pcList
         (
         pc
         )
         select pc
                  from t_final
                  group by pc

select @firstId = min(ListID)
         ,@lastId = max(ListID)
         from @pcList

while (@firstId <= @lastId)
begin
    select @currentPc = pc
         from @pcList
         where (ListID = @firstId)

         -- use @currentPc here to BCP-out your data

    set @firstId = @firstId + 1
end

I hope you get the general idea. The script should go through all the pc
values and execute a subprocess (check out BCP in Books Online) to write the
data from your query to a file.

ML

9 Sep 2005 7:56 PM

Uday

I tried the following bcp utility to extract 1900 files every iteration.....
Modify it as per your requirements.....

this runs at command prompt, using ms-dos 'for' command. (please refer to
for /? at command prompt for more info, I'll try to put here as much as I
can, though)

Explanation:
----%A IN (1,2,3,4,5,6,7,8,9)---
1 ----- use %% if run in a batch mode
2 ------ put all the 400 pc-ids in the ( ) if not put them is a file and
read it here...
(refer to for /? on how to do that, its easy and efficient)
3 ------ after bcp command write your query that spits comma delimited records
4 ------ queryout C:\Uday\pharmacy\bcp\test\%A.txt
files are saved with the same name as pc-id (%A)

------------------ start ----------------------------------
FOR %A IN (1,2,3,4,5,6,7,8,9) DO bcp "select convert(varchar,pctincrease) +
',' + convert(varchar, PoolAmount) + ',' + Manager from t_final where pc
=%A" queryout C:\Uday\pharmacy\bcp\test\%A.txt -c -T
------------------ end ----------------------------------

This technique saved me a lot of time.....

Do modify it as per your needs,

Hope this helps....
_Uday

9 Sep 2005 10:00 PM

26point2er

ML and Uday thanks to both of you.

I prefer ML's as I can compile the driver SQL script as a stored procedure.

Show quote

"Uday" wrote:

> I tried the following bcp utility to extract 1900 files every iteration.....
> Modify it as per your requirements.....
>
> this runs at command prompt, using ms-dos 'for' command. (please refer to
> for /? at command prompt for more info, I'll try to put here as much as I
> can, though)
>
> Explanation:
> ----%A IN (1,2,3,4,5,6,7,8,9)---
> 1 ----- use %% if run in a batch mode
> 2 ------ put all the 400 pc-ids in the ( ) if not put them is a file and
> read it here...
> (refer to for /? on how to do that, its easy and efficient)
> 3 ------ after bcp command write your query that spits comma delimited records
> 4 ------ queryout C:\Uday\pharmacy\bcp\test\%A.txt
> files are saved with the same name as pc-id (%A)
>
> ------------------ start ----------------------------------
> FOR %A IN (1,2,3,4,5,6,7,8,9) DO bcp "select convert(varchar,pctincrease) +
> ',' + convert(varchar, PoolAmount) + ',' + Manager from t_final where pc
> =%A" queryout C:\Uday\pharmacy\bcp\test\%A.txt -c -T
> ------------------ end ----------------------------------
>
> This technique saved me a lot of time.....
>
> Do modify it as per your needs,
>
> Hope this helps....
> _Uday

10 Sep 2005 1:35 AM

Alejandro Mesa

Here is another possible solution. Just replace "your_db" with your db's name
and execute it in QA or put this code in a sp. Basically what it does is to
use the extended procedure xp_cmdshell to call the utility "bcp.exe" to
create a batch file that contain multiple bcp calls, one for each [pc] value.
The last command will execute the batch.

exec master..xp_cmdshell 'bcp "SELECT DISTINCT ''bcp """SELECT * FROM
your_db..t_final WHERE pc = '''''' + pc + ''''''"""'', ''queryout
"""c:\temp\pc_'' + pc + ''.csv""" -T -c -t"""'' + "'',''" + ''"""'' FROM
your_db..t_final" queryout "c:\temp\bcp.bat" -T -c -t" " && c:\temp\bcp.bat'

This is an example I ran in my pc. I inserted three rows, with values
'aaaa', 'bbbb', 'cccc' for column [pc]. This is the content of the file
bcp.bat

bcp "SELECT * FROM northwind..t_final WHERE pc = 'aaaa'" queryout
"c:\temp\pc_aaaa.csv" -T -c -t","
bcp "SELECT * FROM northwind..t_final WHERE pc = 'bbbb'" queryout
"c:\temp\pc_bbbb.csv" -T -c -t","
bcp "SELECT * FROM northwind..t_final WHERE pc = 'cccc'" queryout
"c:\temp\pc_cccc.csv" -T -c -t","

and this are the files created when the batch file was executed.

pc_aaaa.csv
pc_bbbb.csv
pc_cccc.csv

Here is a link to an article written by OJ, a member of this group, based on
a request similar to yours.

Xp_Execresultset
http://www.rac4sql.net/xp_execresultset.asp

AMB

Show quote

"26point2er" wrote:

> ML and Uday thanks to both of you.
>
> I prefer ML's as I can compile the driver SQL script as a stored procedure.
>
>
>
> "Uday" wrote:
>
> > I tried the following bcp utility to extract 1900 files every iteration.....
> > Modify it as per your requirements.....
> >
> > this runs at command prompt, using ms-dos 'for' command. (please refer to
> > for /? at command prompt for more info, I'll try to put here as much as I
> > can, though)
> >
> > Explanation:
> > ----%A IN (1,2,3,4,5,6,7,8,9)---
> > 1 ----- use %% if run in a batch mode
> > 2 ------ put all the 400 pc-ids in the ( ) if not put them is a file and
> > read it here...
> > (refer to for /? on how to do that, its easy and efficient)
> > 3 ------ after bcp command write your query that spits comma delimited records
> > 4 ------ queryout C:\Uday\pharmacy\bcp\test\%A.txt
> > files are saved with the same name as pc-id (%A)
> >
> > ------------------ start ----------------------------------
> > FOR %A IN (1,2,3,4,5,6,7,8,9) DO bcp "select convert(varchar,pctincrease) +
> > ',' + convert(varchar, PoolAmount) + ',' + Manager from t_final where pc
> > =%A" queryout C:\Uday\pharmacy\bcp\test\%A.txt -c -T
> > ------------------ end ----------------------------------
> >
> > This technique saved me a lot of time.....
> >
> > Do modify it as per your needs,
> >
> > Hope this helps....
> > _Uday

10 Sep 2005 4:25 PM

Uday

Comments:
My earlier post is excatly the same....
Instead of writing bcp command 400 times in a batch file... use for loop to
do that.....with parameters....!!!
_Uday
Show quote

> bcp.bat
>
> bcp "SELECT * FROM northwind..t_final WHERE pc = 'aaaa'" queryout
> "c:\temp\pc_aaaa.csv" -T -c -t","
> bcp "SELECT * FROM northwind..t_final WHERE pc = 'bbbb'" queryout
> "c:\temp\pc_bbbb.csv" -T -c -t","
> bcp "SELECT * FROM northwind..t_final WHERE pc = 'cccc'" queryout
> "c:\temp\pc_cccc.csv" -T -c -t","
>
> and this are the files created when the batch file was executed.
>
> pc_aaaa.csv
> pc_bbbb.csv
> pc_cccc.csv
>
>
> Here is a link to an article written by OJ, a member of this group, based on
> a request similar to yours.
>
> Xp_Execresultset
> http://www.rac4sql.net/xp_execresultset.asp
>

10 Sep 2005 11:54 PM

Alejandro Mesa

Uday,

> My earlier post is excatly the same....
> Instead of writing bcp command 400 times in a batch file... use for loop to
> do that.....with parameters....!!!

I noticed that, but the parameters in your code have to be maintained
manually and I do not know if that is convenient. If a new value is added to
the [pc] domain or is deleted, then you have to modify your batch. What about
if instead 400, he has one or two thousands, will be ease to write them
manually in the batch?, think about this. Anyway, I wrote "a possible
solution" and not "the only solution".

AMB

Show quote

"Uday" wrote:

> Comments:
> My earlier post is excatly the same....
> Instead of writing bcp command 400 times in a batch file... use for loop to
> do that.....with parameters....!!!
> _Uday
> > bcp.bat
> >
> > bcp "SELECT * FROM northwind..t_final WHERE pc = 'aaaa'" queryout
> > "c:\temp\pc_aaaa.csv" -T -c -t","
> > bcp "SELECT * FROM northwind..t_final WHERE pc = 'bbbb'" queryout
> > "c:\temp\pc_bbbb.csv" -T -c -t","
> > bcp "SELECT * FROM northwind..t_final WHERE pc = 'cccc'" queryout
> > "c:\temp\pc_cccc.csv" -T -c -t","
> >
> > and this are the files created when the batch file was executed.
> >
> > pc_aaaa.csv
> > pc_bbbb.csv
> > pc_cccc.csv
> >
> >
> > Here is a link to an article written by OJ, a member of this group, based on
> > a request similar to yours.
> >
> > Xp_Execresultset
> > http://www.rac4sql.net/xp_execresultset.asp
> >
>