asked on

BCP with Format File works but why is it adding spaces?

Related question to this:
https://www.experts-exchange.com/questions/29201868/Can-BCP-command-place-data-in-specific-positions-in-text-file.html

I got my SQL script with the command working. But why is it adding spaces in between the letters?

Step by step:

1. the Format file

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="NativePrefix" PREFIX_LENGTH="1" MAX_LENGTH="3" />
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="1"  MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="1" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="1" MAX_LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="1" MAX_LENGTH="26" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="CMP" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="FileType" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="DummyCol" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="SendReceiveId" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Company" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Open in new window

2. The Script

DECLARE @filename VARCHAR(500)
DECLARE @bcpCommand varchar(2000)


SELECT      
   @FileName   =   '\\somewhere\Drop\TestingCamilla\test-' + replace(replace(replace(convert(nvarchar(19),getdate(),120),'-',''),':',''),' ','') + '.txt'


CREATE TABLE firstrow
(
 cmp VARCHAR(3),
 filetype VARCHAR(9),
 Dummycol VARCHAR(12),
 SendReceiveId VARCHAR(30),
 Company VARCHAR(26)
 )


INSERT INTO firstrow
(
    cmp,
    filetype,
    Dummycol,
    SendReceiveId,
    Company
)
VALUES
(   'CMP', -- cmp - varchar(3)
    'INVENTORY', -- filetype - varchar(9)
    '128472237880', -- Dummycol - varchar(12)
    'ZZ0086792100326T', -- SendReceiveId - varchar(30)
    'YAMAHA'  -- Company - varchar(26)
    )


--SELECT * FROM firstrow


   SET @bcpCommand = 'bcp ' +
     '"SELECT * FROM firstrow " queryout "'
    SET @bcpCommand = @bcpCommand + @FileName + '" -f \\somewhere\Drop\TestingCamilla\YamahaInventoryFirstLine.xml -T'


   -- PRINT @bcpCommand


    EXEC master..xp_cmdshell @bcpCommand

Open in new window

3. Output file -- you see the spaces and the boxes. What is that? I've also attached it

C M P I N V E N T O R Y 1 2 8 4 7 2 2 3 7 8 8 0 Z Z 0 0 8 6 7 9 2 1 0 0 3 2 6 T Y A M A H A

test-20201203112405.txt