BCP to Text Files. Odd Character at File End

  • 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 ?

  • 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.

  • Hi

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

    Thanks,

    Bindu

  • Please post the solution

    Regards,

    Bindu

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

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

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

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

  • 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'

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

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

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