SQL Server BCP export to CSV
All the searches and posts I've come across use the following to export data to a CSV file, how ever I can't get it to work.
Here's a wrinkle, the file doesn't exist, the export has to create it.
How do I get BCP export to create the file and write data ???
I've created a #temp table with the records and now I need to use a select statement to get a single record from the #temp and create and write the file. How do i do this??
This is being done from within a proc.
Create Table #Temp
(
[ ..]
fields..
)
Declare @CSVexport varchar(8000)
set @CSVexport = 'BCP select
[Fld 1]
,[Fld 2]
,[Fld 3]
,[Fld 4]
,[Fld 5]
,[Fld 6]
From #temp where [Fld 7] = @ClientDocNo out C:\Test -c -T -S SERVER'
Exec master..xp_cmdshell @CSVexport
Here's a wrinkle, the file doesn't exist, the export has to create it.
How do I get BCP export to create the file and write data ???
I've created a #temp table with the records and now I need to use a select statement to get a single record from the #temp and create and write the file. How do i do this??
This is being done from within a proc.
Create Table #Temp
(
[ ..]
fields..
)
Declare @CSVexport varchar(8000)
set @CSVexport = 'BCP select
[Fld 1]
,[Fld 2]
,[Fld 3]
,[Fld 4]
,[Fld 5]
,[Fld 6]
From #temp where [Fld 7] = @ClientDocNo out C:\Test -c -T -S SERVER'
Exec master..xp_cmdshell @CSVexport
ASKER
edtechdba:
No can do.
This has to be done in a proc because it's looping through a number of records and a CSV file is created per record. the file is named with the date including h.m.s
Eg:
While row <= rcdCnt
create CSV file, naming it 'ClientTrans_' + getdate()
If there are 25 records there will be 25 CSV files eached named by the client name and transaction along with the date and time.
I can not pre-name the files, as they will be different every second.
Any ideas on how to do this.
No can do.
This has to be done in a proc because it's looping through a number of records and a CSV file is created per record. the file is named with the date including h.m.s
Eg:
While row <= rcdCnt
create CSV file, naming it 'ClientTrans_' + getdate()
If there are 25 records there will be 25 CSV files eached named by the client name and transaction along with the date and time.
I can not pre-name the files, as they will be different every second.
Any ideas on how to do this.
Ah, OK. This site may be of interest to you, it describes how to run a similar process using BCP.
http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
ASKER
I'm actually using this sample from the site but i can't get it to work. the problem being the column names. everything is getting put in a single column and row.
Declare @ClientDocNo varchar(25)
Declare @FileName varchar(200)
Declare @BCPCommand varchar(8000)
set @ClientDocNo = (select top 1 [Client Number] from [GPT].[dbo].tmp_CSV_Export
set @FileName = replace('C:\Clients\'+@Cli
SET @bcpCommand = 'bcp "select ''Client Number'' as ''Client Number'' union all SELECT [Client Number] FROM [GPT].[dbo].tmp_Export where [Client Number] = ''074-210112''" queryout "'
Set @BCPCommand = @bcpCommand + @FileName + '" -N -r -T'
Exec [GPT]..xp_cmdshell @BCPCommand
The file is created, however, what's showing up in the file in the 2nd column is:
Client Number&074-210112
I can't get the columns and data to show up properly like:
Client Number
074-210112
Also another problem is syntax how do I replace the value in the where to use a variable
where [Client Number] = @ClientNo
Declare @ClientDocNo varchar(25)
Declare @FileName varchar(200)
Declare @BCPCommand varchar(8000)
set @ClientDocNo = (select top 1 [Client Number] from [GPT].[dbo].tmp_CSV_Export
set @FileName = replace('C:\Clients\'+@Cli
SET @bcpCommand = 'bcp "select ''Client Number'' as ''Client Number'' union all SELECT [Client Number] FROM [GPT].[dbo].tmp_Export where [Client Number] = ''074-210112''" queryout "'
Set @BCPCommand = @bcpCommand + @FileName + '" -N -r -T'
Exec [GPT]..xp_cmdshell @BCPCommand
The file is created, however, what's showing up in the file in the 2nd column is:
Client Number&074-210112
I can't get the columns and data to show up properly like:
Client Number
074-210112
Also another problem is syntax how do I replace the value in the where to use a variable
where [Client Number] = @ClientNo
ASKER
not forgotten, in progress.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The issue has been abandoned and there's no longer a need to explore this solution.
Also, have you thought about doing this?
1. Create a template file (proper file format - no records)
2. Copy your template file over to your export folder location
3. Export your data into your file in the export folder location
4. Perform any other steps that you need to complete the process
5. Then copy the template file back over to your export folder location to prep for the next export. I do this often with SSIS packages, but it may work with your method too.