Create Multiple csv files using BCP

Question

Create Multiple csv files using BCP

ehlinger

Hall of Fame

Points: 3585
More actions
February 12, 2009 at 8:44 am

#130933

I have a need to, based on a query, produce multiple csv files. I've created a sample table that respresents in a very simple way what I need to do. Here is the code to create the table:
IF OBJECT_ID('tempdb..#csvout') IS NOT NULL
DROP TABLE #csvout
CREATE TABLE #csvout
(UserID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
level_id char(2),
qty int)
INSERT into #csvout (level_id, qty)
VALUES ('A1', 4)
INSERT into #csvout (level_id, qty)
VALUES ('A1', 6)
INSERT into #csvout (level_id, qty)
VALUES ('A2', 8)
INSERT into #csvout (level_id, qty)
VALUES ('B2', 1)
INSERT into #csvout (level_id, qty)
VALUES ('B1', 3)
INSERT into #csvout (level_id, qty)
VALUES ('B1', 5)
INSERT into #csvout (level_id, qty)
VALUES ('C1', 9)
INSERT into #csvout (level_id, qty)
VALUES ('C1', 10)
INSERT into #csvout (level_id, qty)
VALUES ('C1', 2)
INSERT into #csvout (level_id, qty)
VALUES ('D1', 15)
What I want to do is uses BCP with xp_cmdshell to produce multiple csv files out of this table. Ideally, I would use a query and would put all three fields from the table into the csv files. The files would be segregated by level_id. For example, all of the A1 level_ids would be in a csv file called A1, all the B1 level_ids would be in a csv file called B1, etc.
I found an example of sending an entire table to a csv file but can't seem to get it to work. I've tried a lot of different things and haven't been successfull so thought I'd post to see if anyone has any ideas or can provide direction.
Here is the sample that I've found that I can't seem to get to work:
declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Is there a specific reason why you want to use bcp, and not use the export wizard from SSIS?

In SQL Server Management Studio, right-click on the database that holds your data, click on Tasks then Export Data, then step through the wizard. You can save your SSIS package, then edit it. Or you can combine the saved SSIS packages into one job.

[/font]

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

I've used SSIS quite a bit and yes, that would do what we need but because of the application it would be much quicker to use this since our analysts could run it in Management Studio along with other things they have to run in a process.

Because of our process, if we could figure out the bcp it would help streamline the process and allow us to do what we need from one application rather than going back and forth between apps.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

ehlinger (2/12/2009)
I have a need to, based on a query, produce multiple csv files.
[...]
What I want to do is uses BCP with xp_cmdshell to produce multiple csv files out of this table. Ideally, I would use a query and would put all three fields from the table into the csv files. The files would be segregated by level_id. For example, all of the A1 level_ids would be in a csv file called A1, all the B1 level_ids would be in a csv file called B1, etc.
declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

[font="Verdana"]One issue is that your temporary table will unlikely be in the same context as the bcp, so bcp won't be able to see it. Change it to a global temporary table (## in front). Or even better, just create a table in tempdb. Then try something like this:

select ...

into tempdb.dbo.CSVOut;

set @sql = 'bcp "select * from tempdb.dbo.CSVOut where level_id = ''A1'';" out c:\bcp\A1.txt -c -t, -T -S' + @@servername;

exec master.dbo.xp_cmdshell @sql;

set @sql = 'bcp "select * from tempdb.dbo.CSVOut where level_id = ''A2'';" out c:\bcp\A2.txt -c -t, -T -S' + @@servername;

exec master.dbo.xp_cmdshell @sql;

set @sql = 'bcp "select * from tempdb.dbo.CSVOut where level_id = ''A3'';" out c:\bcp\A3.txt -c -t, -T -S' + @@servername;

exec master.dbo.xp_cmdshell @sql;

drop table tempdb.dbo.CSVOut;

(Note the double quotes around the query for bcp, and the two single quotes to insert a single quote into the string. They look remarkably similar.)

[/font]

ehlinger Hall of Fame Points: 3585 More actions · Answer 4

Thanks. I've got it exporting to a file now.

I need to make this more dynamic because in practice, the records equivilant to my level_id field in my example could be different each time we run it. For example, some of the values may not exist so in that instance we don't want to produce a csv file.

So, ideally I don't want to hardcode each value in a separate query because I want it to be dynamic enough to know that it just has to create csv files for the distinct values in level_id.

Thanks.