SQl Server 2014 BCP BulkCopy remove final blank line

Question

Please can anyone advise if there is a method which can be used to stop SQL Server's BCP bulk copy command creating a final carriage return and hence a blank line at the end of the file?

I have been Googling but cannot hit on a suitable answer.

The code I have used in my stored procedure is below. The query and file path have been omitted, but the SQL variables remain:

    /* Prepare the command */
    DECLARE @Command VARCHAR(4000);
    SELECT  @Command = 
                'bcp "'             /* Bulk Copy command */
    +           @Query              /* The query to output */
    +           '" queryout '       /* Output the results of the query */
    +           @FullFilePath       /* The file path to write to */
    +           ' -c -t"|" -T -S'   /* Switches (See below) */
    +           @@servername;       /* The server name */


    /*
        BCP Command Swtitches
            -c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
            -t override the field terminator with "|"
            -T use a trusted connection. Note that U –P may be used for username/password
            -S connect to this server to execute the command
    */

    /* Execute the command */
    exec master..xp_cmdshell @Command;

Thank you.

score 1 · Answer 1 · answered Dec 14 '15 at 20:15

1

I was battling the same thing and the only way I could fix this is NOT USE custom -t/t column separator in the BCP query in the first place.

So my original BCP export syntax:

bcp "select 1 from [myDB].[dbo].[db_status] WHERE db_idx = 0" queryout c:\db_migration_log.txt -c -t/t -S127.0.0.1,2044 -Uuser -Ppassword

had to change to:

bcp "select 1 from [myDB].[dbo].[db_status] WHERE db_idx = 0" queryout c:\db_migration_log.txt -c -S127.0.0.1,2044 -Uuser -Ppassword

Then my import query worked fine (ignoring the last line) as follows:

SET QUOTED IDENTIFIER OFF
SET NOCOUNT ON
BULK INSERT [myDB].[dbo].[mayTable] FROM 'c:\myfile.csv'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

answered Dec 14 '15 at 20:15

Milan

3,209
1
35
46

Excellent, looks promising. If i ever get to revisit that project i will bear your solution in mind! Thank you. – Dib Dec 15 '15 at 04:27
1

..also, I've noticed that for some files with binary data the above fails so on that case I use export/import with the -n parameter and -t/t for those cases EOL error shows up. – Milan Dec 15 '15 at 20:17
Thank you for the update. I will bear that in mind also. – Dib Dec 15 '15 at 20:18

SQl Server 2014 BCP BulkCopy remove final blank line

1 Answers1