Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP to Text Files. Odd Character at File End Expand / Collapse
Author
Message
Posted Thursday, October 14, 2010 1:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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 ?



Post #1004731
Posted Monday, October 18, 2010 12:10 PM This worked for the OP Answer marked as solution


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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.



Post #1006476
Posted Wednesday, February 23, 2011 1:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 11:17 PM
Points: 2, Visits: 6
Hi

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

Thanks,
Bindu
Post #1068102
Posted Wednesday, February 23, 2011 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 11:17 PM
Points: 2, Visits: 6
Please post the solution

Regards,
Bindu
Post #1068207
Posted Thursday, February 24, 2011 6:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
He already did post his solution--read the second post.
Post #1068874
Posted Friday, March 28, 2014 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:32 AM
Points: 1, Visits: 5
Thank you homebrew. If not for your posting, it would have taken so much longer to resolve this issue.
Post #1555896
Posted Wednesday, April 30, 2014 11:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:32 AM
Points: 10, Visits: 31
Thanks for posting this. Can you be more specific with where you insert '/b' in the COPY script?
Post #1566530
Posted Wednesday, April 30, 2014 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:32 AM
Points: 10, Visits: 31
Never mind. Some trial and error, I placed it at the end of the command string and it worked.
Post #1566535
Posted Thursday, May 1, 2014 8:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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'



Post #1566724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse