BCP to Text Files. Odd Character at File End

Question

BCP to Text Files. Odd Character at File End

homebrew01

SSC Guru

Points: 55548
More actions
October 14, 2010 at 1:07 pm

#239822

I'm trying to streamline a manual process here by using BCP to create some text files. I want the test files to include column headers. I saw some ideas of using views with the column names and union to the data, but I's prefer it to be more dynamic in case a new column is added at some point. We have clients that sometimes dictate changes to us, so the less maintenance the better.
There are a variable # of tables that needed to get put to .txt files, and they are fed from another table, so the BCP steps will be run multiple times, once for each table.
I use syscolumns to get the column names into a temp table, and run a BCP to put them to a HEADER.txt file.
SET @FileHdrName = 'Output_Header'
SET @Cmd = 'BCP "SELECT * FROM ' + DB_NAME() + '.dbo.Table_Columns" queryout "' + @Folder + @FileHdrName + @Extension + '" /c /T '
EXEC master..xp_cmdshell @Cmd
Then I use BCP to put the data w/ pipe delimiters into DETAIL.txt file.
SET @FileDataName = 'Output_Data'
SET @Cmd = 'BCP "SELECT * FROM ' + DB_NAME() + '.dbo.Table_Data" queryout "' + @Folder + @FileDataName + @Extension + '" /c /T /t "|"' -- pipe delimited
PRINT ' Executing xp_cmdshell: ' + CONVERT(varchar(1000), @Cmd)
EXEC master..xp_cmdshell @Cmd
So far so good.
Then I use DOS to merge the HDR & DTL together into the final file COMBINED.txt.
eg: copy file1.txt + file2.txt file3.txt
set @CMD = 'copy ' + @Folder + @FileHdrName + @Extension + ' + ' + @Folder + @FileDataName + @Extension + ' ' + @Folder + @FileName + @Extension copy myfile1.txt+myfile2.txt'
EXEC master..xp_cmdshell @Cmd
However, there seems to be a hex value of some sort showing as a square on a new line at the end of the new text file that does not exist in either the header or detail txt files.
There's probably a completely different approach that's better, so I'm open to suggestions. Could I use SSIS and pass it variables for the name of the .txt file to be created ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Solution found. Posting it in the off chance anyone cares.

This didn't really end up being relevant to SQL, but I needed a "/b" in the COPY to prevent an end of file marker from being created.

himabindumlh SSC Journeyman Points: 92 More actions · Answer 2

Hi

Could you please let me know what's the exact solution to get rid of square character in output text file

Thanks,

Bindu

himabindumlh SSC Journeyman Points: 92 More actions · Answer 3

himabindumlh

SSC Journeyman

Points: 92

February 23, 2011 at 6:45 am

#1290350

Please post the solution

Regards,

Bindu

paul.knibbs SSCoach Points: 15320 More actions · Answer 4

He already did post his solution--read the second post.

rossetti_marie Valued Member Points: 63 More actions · Answer 5

Thank you homebrew. If not for your posting, it would have taken so much longer to resolve this issue.

scronin 98371 Valued Member Points: 70 More actions · Answer 6

Thanks for posting this. Can you be more specific with where you insert '/b' in the COPY script?

scronin 98371 Valued Member Points: 70 More actions · Answer 7

Never mind. Some trial and error, I placed it at the end of the command string and it worked.

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

scronin 98371 (4/30/2014)
Never mind. Some trial and error, I placed it at the end of the command string and it worked.

Yes, that is what I did too.

set @CMD = 'copy ' + @Folder + @FileHdrName + @Extension + ' + ' + @Folder + @FileDataName + @Extension + ' ' + @Folder + @FileName + @Extension + ' /b'

s.j.iyyappan SSC Journeyman Points: 77 More actions · Answer 9

/B is not working, if you have large number of data. It corrupts file 🙁 Is there any other way to get rid off this?