I'm arguing with BCP

Question

Post reply

I'm arguing with BCP

BenWard

SSCertifiable

Points: 5903
More actions
December 22, 2014 at 8:13 am

#397800

Hi Guys,
BCP is telling me that I have an invalid character value for a cast specification...
#@ Row 1, Column 2: Invalid character value for cast specification @#
A000000A22000WRN2014/11/0500.01.0069.0065.093.9121.4833.00Zzzzz
This comes from a CSV file - I have tried this encoded in both ascii and utf-8 (which I have anonymised);
"Policy","Broker Number","Transaction Type","Start Date","Start Time","End Date","Premium inc IPT","Premium ex IPT","IPT","Commission","Commission %","Name","Date","Sequence"
"A000000A","22000W","RN","2014/11/05","00.01.00","2015/11/05","69.00","65.09","3.91","21.48","33.00","Zzzzz","2014/11/03","72559"
Which I am loading via a 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="0" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="6"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="2"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="10"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="10"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="8"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="50"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="10"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR='\"\r' MAX_LENGTH="10"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="e_policy_no" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="start_agent_code" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="3" NAME="transaction_status" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="4" NAME="istart_date" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="5" NAME="istart_time" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="PremiumIncIPT" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="8" NAME="PremiumExIPT" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="IPT" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="commission" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="11" NAME="commissionPerc" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="12" NAME="sname" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>
The length 1 field at the start is there to lose the opening text qualifier (")
The applicable columns in the database table are defined as;
e_policy_no char(10)
start_agent_code char(50)
transaction_status char(50)
istart_date char(50)
istart_time char(50)
PremiumIncIPT money
PremiumExIPT nchar(10)
IPT nchar(10)
commission money
commissionPerc nchar(10)
sname char(50)
I am calling the bcp utility using -F2 to remove the header row.
So I have absolutely no idea what it is complaining about. I'm not certain what it doesn't like about 'column 2', I'm not even certain which column it is referring to when it says '2' either.
I really don't want to have to use SSIS - for loading a fairly simple csv file into a database table it seems like extreme overkill.
Would someone point me in the right direction please?
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Viewing 8 posts - 1 through 7 (of 7 total)

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

BenWard SSCertifiable Points: 5903 More actions · Answer 1

OK I seem to have identified the source of this issue...

My destination table has more rows than my flat file and they are in a different order.

I thought bcp was supposed to work when there was a different number of rows but it would appear that is only one sided...

I have worked around this by implementing a staging table.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

That shouldn't be a problem, but I'm not sure if your format file is correct.

Here's one example that I use, it might help you out.

<?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="0" xsi:type="CharTerm" TERMINATOR='"'

MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR='","'

MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR='","'

MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="3" xsi:type="CharTerm" TERMINATOR='","'

MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="4" xsi:type="CharTerm" TERMINATOR='",'

MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=','

MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="6" xsi:type="CharTerm" TERMINATOR=','

MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="7" xsi:type="CharTerm" TERMINATOR=',"'

MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="8" xsi:type="CharTerm" TERMINATOR='",'

MAX_LENGTH="26" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="9" xsi:type="CharTerm" TERMINATOR=','

MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="10" xsi:type="CharTerm" TERMINATOR=',"'

MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="11" xsi:type="CharTerm" TERMINATOR='","'

MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="12" xsi:type="CharTerm" TERMINATOR='",'

MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\r"

MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="DAT_EXCHANGE" xsi:type="SQLCHAR" LENGTH="10"/>

<COLUMN SOURCE="2" NAME="COD_FCC" xsi:type="SQLCHAR" LENGTH="3"/>

<COLUMN SOURCE="3" NAME="FLG_FCCB3" xsi:type="SQLCHAR" LENGTH="1"/>

<COLUMN SOURCE="4" NAME="FLG_QUOTATION" xsi:type="SQLCHAR" LENGTH="1"/>

<COLUMN SOURCE="5" NAME="FIXRATE" xsi:type="SQLDECIMAL" PRECISION="9" SCALE="5"/>

<COLUMN SOURCE="6" NAME="BIDRATE" xsi:type="SQLDECIMAL" PRECISION="9" SCALE="5"/>

<COLUMN SOURCE="7" NAME="OFFERRATE" xsi:type="SQLDECIMAL" PRECISION="9" SCALE="5"/>

<COLUMN SOURCE="9" NAME="ENT_LASTMOD" xsi:type="SQLDECIMAL" PRECISION="4" SCALE="0"/>

<COLUMN SOURCE="10" NAME="BRN_LASTMOD" xsi:type="SQLDECIMAL" PRECISION="4" SCALE="0"/>

<COLUMN SOURCE="11" NAME="LASTMODTRM" xsi:type="SQLCHAR" LENGTH="4"/>

<COLUMN SOURCE="12" NAME="LASTMODUSER" xsi:type="SQLCHAR" LENGTH="8"/>

</ROW>

</BCPFORMAT>

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

BenWard SSCertifiable Points: 5903 More actions · Answer 3

Thanks Luis

I'll have a look at that.

the \" in the field terminator looks like a holdover from the pre-xml format file days. It seems to work fine when I'm using a staging table so I'd venture that although escaping a quote is perhaps no longer necessary it's still accepted by the system.

I'll post here if the results say otherwise.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

BenWard (12/22/2014)
OK I seem to have identified the source of this issue...
My destination table has more rows than my flat file and they are in a different order.
I thought bcp was supposed to work when there was a different number of rows but it would appear that is only one sided...
I have worked around this by implementing a staging table.

Do you mean "columns" rather than "rows"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BenWard SSCertifiable Points: 5903 More actions · Answer 5

Ah yes I did mean columns.

silly mistake there!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

BenWard (12/29/2014)
Ah yes I did mean columns.
silly mistake there!

In that case, BCP will still work. You need to list the columns in your format file and give them the column position of "0", empty delimiters, and an input length of "0".

The other thing you might try is to BCP into a view that refers to the table. Of course, the view can be setup to have the columns in the same order as the file and you only need to list the columns that will be in a file. This won't, of course, work if you need to skip any columns that are in the file itself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BenWard SSCertifiable Points: 5903 More actions · Answer 7

Oooh clever solution!

Thanks for your help 🙂

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------