Outputting the Column Names in BCP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Outputting the Column Names in BCP

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-09 : 02:22:35

Hi,
I have set of values in the Excel Sheet.I have to read the values from the excel and extract the data From table and then create then create the seperate excel file for each row of data available in the Excel File.
(If the excel file contains the values for CustomerID,I have to read those values and create the seperate excel for each customer with CustomerID.xls).
I am using the xp_cmdshell and then Opendatasource for outputing the results.
I want the column names included in the output.
Currently I am creating the temp table ,inserting the columnnames into the temp table ,then inserting the actual data and bcp out into the file.
This has so many glitches.
Is there anyother better way to do it?

The following is the procedure I am using..Really clumsy code..but does the job partially.

CREATE procedure [dbo].[Sp_ColExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id varchar(255)
declare @commandstring nvarchar(4000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1$'
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
declare c scroll cursor for
select PartNumber from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * into ['+@id+ '] from Test.dbo.TableName where PartNumber ='''+ cast(@id as varchar(200))+''''
print @commandstring
declare @commandstring1 nvarchar(4000)
declare @columnNames varchar(4000)
SELECT @columnNames = COALESCE( @columnNames + ''',''', '''') + column_name
FROM Test.INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'
set @columnNames= @columnNames+''''
set @commandstring1='Insert into ['+ @id+ '] select ' + @columnNames
print @commandstring1
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
declare @commandstring3 nvarchar(4000)
set @commandstring3 = 'bcp.exe Test.dbo.['+@id+'] out '+'"'+ @targetfilename +'"'+' -T -c'
print @commandstring3
declare @commandstring4 nvarchar(4000)
set @commandstring4='drop table ['+ @id+']'
print @commandstring4
execute sp_executesql @commandstring
execute sp_executesql @commandstring1
execute xp_cmdshell @commandstring3
execute sp_executesql @commandstring4
set @columnNames=NULL
--print @commandstring
fetch next from c into @id
end
close c
deallocate c
end

Thanks
Raju

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 11:45:02

See if this helps
http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-09 : 12:41:40

this works also

http://weblogs.sqlteam.com/mladenp/archive/2006/07/26/10771.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-09 : 23:06:51

Thanks for your reference!
It did help..still searching for the efficient method.

Raju

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-10 : 12:47:39

quote:
Originally posted by spirit1

this works also
http://weblogs.sqlteam.com/mladenp/archive/2006/07/26/10771.aspx

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

How did I forget this?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-10 : 14:38:31