Adding a Qualifier inside a format file .xml

Question

Post reply

Adding a Qualifier inside a format file .xml

BI_Developer

SSC Eights!

Points: 908
More actions
June 27, 2018 at 4:07 pm

#355801

Hello,
I'm not sure if this may have been posted before but after searching and not finding it, here it goes; I need to BCP a .csv file into SQL table, but when I run the BCP command it returns error msg: "Invalid character value for cast specification"
I believe what's happening is that column 5 in the .csv file is "EMP_NAME" which has a comma between the person's last and first name, so as a result, i.e. Doe, John in NAME column is getting split up, hence, John is pushing all other columns to the right, and the next colum is a CHAR trying to insert the data into a INT field. So, I need to figure out how to include the double quote " qualifier inside the format file, so it doesn't split up this kind of information, which BTW, the last two columns also have commas inside them. Is this possble? Below is my format file code if it helps.
<?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="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="EMPLOYEE_TRANSFER_HRS" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="2" NAME="EMPLOYEE_LOCATION_ID" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="EMPLOYEE_DEPT_ID" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="EMPLOYEE_ID" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="EMP_NAME" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="6" NAME="EMPLOYEE_EARN_CD" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="7" NAME="EMPLOYEE_TOTAL_WORK_HRS" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/>
<COLUMN SOURCE="8" NAME="EMPLOYEE_TOTAL_PAY_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/>
<COLUMN SOURCE="9" NAME="EMPLOYEE_PAY_CD" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="10" NAME="EMPLOYEE_HOURLY_RATE_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/>
<COLUMN SOURCE="11" NAME="WEEK_END_DT" xsi:type="SQLDATE"/>
<COLUMN SOURCE="12" NAME="PAY_END_PERIOD_DT" xsi:type="SQLDATE"/>
<COLUMN SOURCE="13" NAME="EMPLOYEE_JOB_CD" xsi:type="SQLINT"/>
<COLUMN SOURCE="14" NAME="EMPLOYEE_TITLE_DESC" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="15" NAME="SUPERVISOR_NM" xsi:type="SQLNCHAR"/>
</ROW>
</BCPFORMAT>

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Does column 5 make consistent use of being wrapped in double quotes?

--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)

BI_Developer SSC Eights! Points: 908 More actions · Answer 2

Jeff Moden - Thursday, June 28, 2018 12:47 PM
Does column 5 make consistent use of being wrapped in double quotes?

the answer is yes.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Ok... first of all, your original BCP format file had what I believe to be 2 errors in it. If you look at fileds 5 and 14 in the file field definition part of the XML, you had single quotes around the terminators instead of double quotes.

In the following, I've added to the terminator for fields 4 and 5 to include a /" at the end of field 4 (which is the beginning of field 5) and at the end of field 5. I don't know if /" works in the XML because I don't use XML format files (they're too damned long and unnecessarily complex for me). If /" doesn't work in the XML, we can convert this to a standard BCP Format file and I know it works there.

Basically, I'm adding double quotes to the field 5 delimiters to encapsulate field 5 in the double quotes that you said would be there. This will ignore the commas in field 5 because they no longer match the "next" terminator.

Here's the reworked XML 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="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=",/"" MAX_LENGTH="12"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="/"," MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/> <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/> <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="EMPLOYEE_TRANSFER_HRS" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="2" NAME="EMPLOYEE_LOCATION_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="3" NAME="EMPLOYEE_DEPT_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="4" NAME="EMPLOYEE_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="5" NAME="EMP_NAME" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="6" NAME="EMPLOYEE_EARN_CD" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="7" NAME="EMPLOYEE_TOTAL_WORK_HRS" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="8" NAME="EMPLOYEE_TOTAL_PAY_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="9" NAME="EMPLOYEE_PAY_CD" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="10" NAME="EMPLOYEE_HOURLY_RATE_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="11" NAME="WEEK_END_DT" xsi:type="SQLDATE"/> <COLUMN SOURCE="12" NAME="PAY_END_PERIOD_DT" xsi:type="SQLDATE"/> <COLUMN SOURCE="13" NAME="EMPLOYEE_JOB_CD" xsi:type="SQLINT"/> <COLUMN SOURCE="14" NAME="EMPLOYEE_TITLE_DESC" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="15" NAME="SUPERVISOR_NM" xsi:type="SQLNCHAR"/> </ROW> </BCPFORMAT>

--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)

BI_Developer SSC Eights! Points: 908 More actions · Answer 4

Jeff Moden - Friday, June 29, 2018 10:05 PM
Ok... first of all, your original BCP format file had what I believe to be 2 errors in it. If you look at fileds 5 and 14 in the file field definition part of the XML, you had single quotes around the terminators instead of double quotes.
In the following, I've added to the terminator for fields 4 and 5 to include a /" at the end of field 4 (which is the beginning of field 5) and at the end of field 5. I don't know if /" works in the XML because I don't use XML format files (they're too damned long and unnecessarily complex for me). If /" doesn't work in the XML, we can convert this to a standard BCP Format file and I know it works there.
Basically, I'm adding double quotes to the field 5 delimiters to encapsulate field 5 in the double quotes that you said would be there. This will ignore the commas in field 5 because they no longer match the "next" terminator.
Here's the reworked XML 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="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=",/"" MAX_LENGTH="12"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="/"," MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/> <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/> <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="11"/> <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="120" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="EMPLOYEE_TRANSFER_HRS" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="2" NAME="EMPLOYEE_LOCATION_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="3" NAME="EMPLOYEE_DEPT_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="4" NAME="EMPLOYEE_ID" xsi:type="SQLINT"/> <COLUMN SOURCE="5" NAME="EMP_NAME" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="6" NAME="EMPLOYEE_EARN_CD" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="7" NAME="EMPLOYEE_TOTAL_WORK_HRS" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="8" NAME="EMPLOYEE_TOTAL_PAY_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="9" NAME="EMPLOYEE_PAY_CD" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="10" NAME="EMPLOYEE_HOURLY_RATE_AMT" xsi:type="SQLNUMERIC" PRECISION="10" SCALE="2"/> <COLUMN SOURCE="11" NAME="WEEK_END_DT" xsi:type="SQLDATE"/> <COLUMN SOURCE="12" NAME="PAY_END_PERIOD_DT" xsi:type="SQLDATE"/> <COLUMN SOURCE="13" NAME="EMPLOYEE_JOB_CD" xsi:type="SQLINT"/> <COLUMN SOURCE="14" NAME="EMPLOYEE_TITLE_DESC" xsi:type="SQLNCHAR"/> <COLUMN SOURCE="15" NAME="SUPERVISOR_NM" xsi:type="SQLNCHAR"/> </ROW> </BCPFORMAT>

Thanks Jeff,
I went with a DFT, but I will test thru BCP with your specifications and will provide feedback on results.