column header - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

column header

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 07:44:19

The below proc will get us the export for any table in the database, however could you please help me in including the column header for the table out put file,

Regards,

ALTER PROCEDURE SP_GET_DETAILS 
( @DatabaseName nvarchar(200)
 ,@TableName nvarchar(200) 
 ,@Path nvarchar(300)
)
AS

BEGIN

DECLARE	 @OutputFile	NVARCHAR(4000)
		,@FilePath		NVARCHAR(1000)
		,@bcpCommand	NVARCHAR(4000)

	EXECUTE sp_configure 'show advanced options', 1
	RECONFIGURE WITH OVERRIDE

	EXECUTE sp_configure 'xp_cmdshell', '1'
	RECONFIGURE WITH OVERRIDE
	
		SET @bcpCommand = 'bcp "SELECT * FROM [' + @DatabaseName + '].dbo.' + '['+@TableName+']' + '" queryout "'

		SET @FilePath = @Path
		SET @OutputFile = @TableName + '.txt'
		SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|"'

		EXEC master..xp_cmdshell @bcpCommand

	EXECUTE sp_configure 'xp_cmdshell', '0'
	RECONFIGURE WITH OVERRIDE

	EXECUTE sp_configure 'show advanced options', 0
	RECONFIGURE WITH OVERRIDE

END

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 08:14:02

Easiest is to bcp out the column names then the data then concatenate. Saves having to convert everything to character in a union statement.

so
bcp information_schema.columns to tablename.txt
bcp data to bcpwk.txt

then
master..xp_cmdshell 'bcpwk.txt >> tablename.txt'
master..xp_cmdshell 'del bcpwk.txt'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 08:35:13

I guess,

Any how we are passing the table name which can be used to get the column names, using the information_schema.columns,

SET @bcpCommand = 'bcp "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '+@TableName+ '" queryout "'

how to concat?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 09:22:13

will this helps.. how to place the output of this using bcp on the top row( as this is giving me the column header) pipe separated.

declare @name varchar(2000)
select @name=coalesce(@name,'') + COLUMN_NAME + '|'
from INFORMATION_SCHEMA.COLUMNS where table_name =@table_name
select @name as column_name

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 10:12:35

then
master..xp_cmdshell 'bcpwk.txt >> tablename.txt'
master..xp_cmdshell 'del bcpwk.txt'

Is the concat bit. just substitute the file names.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 10:28:23

I have added the below code, however I am not getting able to put the column headers in txt file so that I can concat it later with the data.

NR can you help me in this?


		SELECT @ColName=COALESCE(@ColName,'') + COLUMN_NAME + '|'
		FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_name =@TableName
		
		SET @ColName = LEFT(@ColName,LEN(@ColName)-1) 		
	    PRINT @colName
		
		SET @bcpCommand = 'bcp "SELECT @ColName " queryout "'
		PRINT	@bcpCommand
		--SET @bcpCommand = 'bcp "SELECT * FROM [' + @DatabaseName + '].dbo.' + '['+@TableName+']' + '" queryout "'

		SET @FilePath	= @Path
		SET @OutputFile = @TableName + '.txt'
		SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '-c -F -T -t'
    	--SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|"'

		EXEC master..xp_cmdshell @bcpCommand

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 10:36:16

I am not able to get the column headers in the file, fact file itself is not getting generated.. below is the error message...

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 10:50:29

SET @bcpCommand = 'bcp "SELECT * FROM [' + @DatabaseName + '].information_schema.columns where table_name = ''' + @TableName+ '''" queryout "'

SET @FilePath = @Path
SET @OutputFile = @TableName + 'wk.txt'
SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|"'

EXEC master..xp_cmdshell @bcpCommand

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 11:02:13

thanks NR, however we need only column names from first file and entire data in second file and finally merge them into one file.. am I right?

SET @bcpCommand = 'bcp "SELECT column_name FROM [' + @DatabaseName + '].information_schema.columns where table_name = ''' + @TableName+ '''" queryout "'

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:06:55

That's write - as in my first post.

bcp out column names
bcp out data
concatenate data file to column names file
delete data file

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 11:16:34

Column names are coming one below the other, it should come as col1|col2.... |coln.

Thats the reason I was trying out this..

--		SELECT @ColName=COALESCE(@ColName,'') + COLUMN_NAME + '|'
--		FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_name =@TableName
--		SET @ColName = LEFT(@ColName,LEN(@ColName)-1) 		
--		SET @bcpCommand = 'bcp "SELECT @ColName " queryout "'

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:21:21

oops
try with

SET @bcpCommand = 'bcp "SELECT * FROM [' + @DatabaseName + '].information_schema.columns where table_name = ''' + @TableName+ '''" queryout "'

SET @FilePath = @Path
SET @OutputFile = @TableName + 'wk.txt'
SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|" -r"|"'

EXEC master..xp_cmdshell @bcpCommand

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 11:49:18

Thanks NR,

Now I am able to generate two out put file one for columns and other for data
I have 2 issues

1. column file generated at the end has | ( which has to be removed and placed with line terminator this we can see later also first I need to get the concat of two file)
2. concating two files data and column( can you help in correcting the below code)

SET @bcpCommand = 'bcp "SELECT COLUMN_NAME FROM [' + @DatabaseName + '].information_schema.columns where table_name = ''' + @TableName+ '''" queryout "'
SET @FilePath = @Path
SET @OutputFile = 'ColName.txt'
SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|" -r"|"'
EXEC master..xp_cmdshell @bcpCommand

SET @bcpCommand = ''
SET @OutputFile = ''
SET @bcpCommand = 'bcp "SELECT * FROM [' + @DatabaseName + '].dbo.' + '['+@TableName+']' + '" queryout "'
SET @FilePath = @Path
SET @OutputFile = @TableName + '.txt'
SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|"'
EXEC master..xp_cmdshell @bcpCommand

--EXEC master..xp_cmdshell 'ColName.txt >> ' @OutputFile

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:55:20

Oops - missed the type command
select @bcpCommand = 'type ' + @FilePath+ @TableName + '.txt >> ' + @FilePath+ 'ColName.txt'
EXEC master..xp_cmdshell @bcpCommand

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 12:29:33

It working thanks NR,

Need to look into two issues.
1. Removing | character in the last of column file
2. deleting the column file after merging.

Data is as below

FX_KEY|FX_IND1|FX_DT_COMPL|FX_DIVN|FX_CCCD|FX_LOCN|FX_ARNO|FX_ARDT|FX_ACCD|FX_SUB_CD|FX_JVNO|FX_JVDT|FX_PVNO|FX_PVDT|FX_DCAT|FX_LWIN|FX_QTY|FX_COST|FX_DEP_RATE|FX_CUR_DEP|FX_CUM_DEP|FX_WDV|FX_SOLD|FX_DESC|Source_file|EA1754 |B|2003-02-26 00:00:00.000||P12|P12|1019 |2002-03-20 00:00:00.000|130300|CAPF|1T3JV1100007 |2003-03-31 00:00:00.000||||||11331.00|5.28|||||REBOILER FOR DA 1751|NMAdd_Unit1Add

source_file is the last column name after which we have | and data should follow from next line.

I am very very thankful to you NR for all the help you have done..

Best Regards,
aakcse

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 12:34:50

2 is easy
select @bcpCommand = 'del ' + @FilePath+ 'ColName.txt'
EXEC master..xp_cmdshell @bcpCommand

I'll think about getting rid of the last |.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 12:50:43

Think this should do it

create table #a (s varchar(max))
declare @cmd varchar(max)
select @cmd = 'declare @s varchar(max) select @s = coalesce(@s + ''|'','''') + COLUMN_NAME
FROM [' + @DatabaseName + '].information_schema.columns where table_name = ''' + @TableName+ '''
insert #a select @s'
exec (@cmd)
select @cmd = s from #a
select @bcpCommand = 'echo ' + @cmd + ' > ' + @FilePath+ 'ColName.txt'
exec master..xp_cmdshell @bcpCommand

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 14:35:02

If I run the below code it is giving me err.. as below
'FX_IND1' is not recognized as an internal or external command,
operable program or batch file.
NULL


		SET @FilePath	= @Path
		SET @OutputFile = @TableName +'With_Col_header.txt'
		CREATE TABLE #a (s VARCHAR(MAX))
		DECLARE @cmd VARCHAR(MAX) 
		SELECT @cmd = 'DECLARE @s VARCHAR(max) 
						SELECT @s = COALESCE(@s + ''|'','''') + COLUMN_NAME
							FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''' + @TableName+ '''
							INSERT INTO #a SELECT @s'
		
		EXEC (@cmd)
		SELECT @cmd = s FROM #a
		SELECT @bcpCommand = 'echo ' + @cmd + ' > ' + @OutputFile
		EXEC master..xp_cmdshell @bcpCommand

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 02:54:43

Refer point 5 too
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources