Camillia
asked on
BCP command into text file works but why is there tab between words?
Related question to this:
https://www.experts-exchange.com/questions/29201868/Can-BCP-command-place-data-in-specific-positions-in-text-file.html
Step by step
1.
2. I run this
3. File is generated but it looks like this ( I've also attached it)test-20201203131532.txt
CMP INVENTORY 128472237880 ZZ0086792100326T YAMAHA
4. I need to look like this that's why I have specified the "max-length" in the XML. I need specific spaces between each work. Attached how I want it to look like.txt
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
https://www.experts-exchange.com/questions/29201868/Can-BCP-command-place-data-in-specific-positions-in-text-file.html
Step by step
1.
<?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="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="FileType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="DummyCol" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SendReceiveId" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="5" NAME="Company" xsi:type="SQLVARYCHAR" />
</ROW>
</BCPFORMAT>
2. I run this
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 -w -T' -- PRINT @bcpCommand EXEC master..xp_cmdshell @bcpCommand
3. File is generated but it looks like this ( I've also attached it)test-20201203131532.txt
CMP INVENTORY 128472237880 ZZ0086792100326T YAMAHA
4. I need to look like this that's why I have specified the "max-length" in the XML. I need specific spaces between each work. Attached how I want it to look like.txt
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah, default is tab. I was just reading another link about all this. That's what "-T" means then.
you can try removing MAX_LENGTH="n" and use LENGTH ="n"
see the parameters here...
see the parameters here...
Attributes of the <FIELD> Element
Attributes of the <COLUMN> Element
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/xml-format-files-sql-server?view=sql-server-ver15ASKER
"-t" is not correct. Got an error. Going to try "Terminator"
ASKER
Thanks, Hain. We posted at the same time. Let me try "terminator" and "max_length"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
got it. I had replaced "-T" with "-t" but I needed to do "-t -T"
"-t" seems to be working but going to look at the other options you have as well, Hain.
"-t" seems to be working but going to look at the other options you have as well, Hain.
you need -T for trusted connection
and -t to set the field terminator
it is not like just use one, they are separate/independent things...
and -t to set the field terminator
it is not like just use one, they are separate/independent things...
ASKER
Yes. I understand it now.
ASKER
I have one more question. Let me see if I can figure it out. I'll post back if I can't.
ASKER
This is my last question (if I need to open another question, please let me know)
I tried several things and I can't get this last part done.
1. I want the text to be like this. You see the space after "INVENTORY"
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
2. I get it like this - no space
CMPINVENTORY128472237880ZZ0086792100326T YAMAHA
I tried the XML:
1.
2. I tried length of 9 and 10
How do add that one space? how did I get the space before the word "YAMAHA"?
This is the latest XML Format File I have.
I tried several things and I can't get this last part done.
1. I want the text to be like this. You see the space after "INVENTORY"
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
2. I get it like this - no space
CMPINVENTORY128472237880ZZ0086792100326T YAMAHA
I tried the XML:
1.
<FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="1" TERMINATOR=" " MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
2. I tried length of 9 and 10
<FIELD ID="2" xsi:type="NCharPrefix" TERMINATOR=" " LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
How do add that one space? how did I get the space before the word "YAMAHA"?
This is the latest XML Format File I have.
<?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" TERMINATOR=" " LENGTH="10" 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="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="FileType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="DummyCol" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SendReceiveId" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="5" NAME="Company" xsi:type="SQLVARYCHAR" />
</ROW>
</BCPFORMAT>
try NCharFixed + LENGTH to see ıf ıt works...
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR=" " LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR=" " LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
ASKER
Thanks. I'll post back.
ASKER
It doesn't take the Terminator.
1. I don't think it's taking the Terminator because I tried this and I don't see it in the file.
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR="-" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
Going thru this
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15
1. I don't think it's taking the Terminator because I tried this and I don't see it in the file.
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR="-" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
Going thru this
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15
ASKER
Or, maybe I need to do something like this if Terminator isn't working:
https://stackoverflow.com/questions/58863557/append-white-space-to-file-during-bcp-export
Looks like this is the same issue as mine? https://stackoverflow.com/questions/19816883/bcp-how-to-specify-an-empty-field-terminator
https://stackoverflow.com/questions/58863557/append-white-space-to-file-during-bcp-export
Looks like this is the same issue as mine? https://stackoverflow.com/questions/19816883/bcp-how-to-specify-an-empty-field-terminator
what is your bcp command like
bcp .... -t0x20 -r \n -T
bcp .... -t' ' -r \n -T
bcp .... -t0x20 -r \n -T
bcp .... -t' ' -r \n -T
ASKER
It's bcp..... -w -t -T
ASKER
I ran some tests:
1. -w -t0x20 -r \n -T
This with field
CMP䤠一嘀䔀一吀伀刀夀 128472237880娠娀 㠀㘀㜀㤀㈀ ㌀㈀㘀吀 YAMAHA
2. -w -t '' -r \n -T --> note the 2 single quotes after -t (as you have above)
with Field
CMP'INVENTORY'128472237880'ZZ0086792100326T'YAMAHA
What is this all telling me?
I can do this but defeats the purpose of the File Format
SELECT CMP,FileType + ' ',DummyCol,SendReceiveId,Company FROM lesaint..firstrow
1. -w -t0x20 -r \n -T
This with field
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR="--" LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
gives me thisCMP䤠一嘀䔀一吀伀刀夀 128472237880娠娀 㠀㘀㜀㤀㈀ ㌀㈀㘀吀 YAMAHA
2. -w -t '' -r \n -T --> note the 2 single quotes after -t (as you have above)
with Field
<FIELD ID="2" xsi:type="NCharFixed" TERMINATOR="--" LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
Gives me this. Note the single quotesCMP'INVENTORY'128472237880'ZZ0086792100326T'YAMAHA
What is this all telling me?
I can do this but defeats the purpose of the File Format
SELECT CMP,FileType + ' ',DummyCol,SendReceiveId,Company FROM lesaint..firstrow
ASKER
Getting closer. This adds space but adds it to every column
-w -t " " -r \n -T
CMP INVENTORY 128472237880 ZZ0086792100326T YAMAHA
I want it to be like this. I want to be able to control the spaces between each column
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
Looks like this doesn't work at all. The Terminator has to be in the BCP command which means it will apply to all the columns
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
-w -t " " -r \n -T
CMP INVENTORY 128472237880 ZZ0086792100326T YAMAHA
I want it to be like this. I want to be able to control the spaces between each column
CMPINVENTORY 128472237880ZZ0086792100326T YAMAHA
Looks like this doesn't work at all. The Terminator has to be in the BCP command which means it will apply to all the columns
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
need to read whole documentation again and do lots of experiment...
some options disables other options
or some options cannot be used in specific modes...
some options disables other options
or some options cannot be used in specific modes...
ASKER
Yeah, it has lots of options. I'll close this question and research more. For now, I'll do the spacing in sql to get this project going.
instead of xml format, you can check the plain text format as well...
I used text format in past many times without any issue...
I used text format in past many times without any issue...
ASKER
Good idea. I'll try that as well.
ASKER
FYI that plain text Format file didn't work either. I'll just do the spacing in SQL
14.0
5
1 SQLNVARCHAR 0 3 "," 1 CMP ""
2 SQLNVARCHAR 0 8 "-" 2 FileType SQL_Latin1_General_CP1_CI_AS
3 SQLNVARCHAR 0 12 "" 3 DummyCol SQL_Latin1_General_CP1_CI_AS
4 SQLNVARCHAR 0 16 "" 4 SendReceiveId SQL_Latin1_General_CP1_CI_AS
5 SQLNVARCHAR 0 26 "" 5 Company SQL_Latin1_General_CP1_CI_AS
14.0
5
1 SQLNVARCHAR 0 3 "," 1 CMP ""
2 SQLNVARCHAR 0 8 "-" 2 FileType SQL_Latin1_General_CP1_CI_AS
3 SQLNVARCHAR 0 12 "" 3 DummyCol SQL_Latin1_General_CP1_CI_AS
4 SQLNVARCHAR 0 16 "" 4 SendReceiveId SQL_Latin1_General_CP1_CI_AS
5 SQLNVARCHAR 0 26 "" 5 Company SQL_Latin1_General_CP1_CI_AS
try SQLCHAR instead of SQLNVARCHAR and use "" as delimiter
Understanding Non-XML Format Files
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms191479(v=sql.90)?redirectedfrom=MSDNASKER
Thanks but I still need specific number of spaces between the columns.
BCP with flat file and XML works but doesn't take the Terminators.
It's ok. I'll just do it in SQL.
BCP with flat file and XML works but doesn't take the Terminators.
It's ok. I'll just do it in SQL.
ASKER
I'll do it this way. Hain - you had this solution in another thread I had.
Not ideal to count spaces and column characters but need to get this done.
I'll take a look at Format File again when I have time. Have to get this section of the project out the door.
SET @bcpCommand = 'bcp ' +
'"SELECT CMP,left(concat(filetype, REPLICATE('' '',10)),10),DummyCol,left(concat(SendReceiveId, REPLICATE('' '',30)),30),Company FROM firstrow " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -f -w -t -T'
Not ideal to count spaces and column characters but need to get this done.
I'll take a look at Format File again when I have time. Have to get this section of the project out the door.
SET @bcpCommand = 'bcp ' +
'"SELECT CMP,left(concat(filetype, REPLICATE('' '',10)),10),DummyCol,left(concat(SendReceiveId, REPLICATE('' '',30)),30),Company FROM firstrow " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -f -w -t -T'
I would still use the CAST I suggested in the other question. Single function over three.
ASKER
Thanks. Cast is probably easier to understand instead of 3 functions.
Open in new window
you can use "" or " " or ","...default is TAB