Hello,
Is anyone familiar with the Contribution Import Process? I have read through all of the Documentation, several times, and did reach out for help, but was unable to receive it. Below is how the import is currently configured, but when I try to run it to Review it, the error that it's out of sync comes up.
Does anyone have any insight for this? Are there any glaring issues that you see? Is there something wrong with the Terminator value?
Would be very open to any help that anyone is able to offer, I've been working on this for almost 2 months now with no luck.
Thank you,
<?xml version="1.0"?><!-- Contribution Import Utility XmlFormatFile - This file is used to define which columns will be used in the file import process. --><!-- Fields can be assigned in any order and fields may be omitted provided the minimum data requirements are met to create a --><!-- new constituent record within the system. Where possible, system defaults and configuration apply to the creation. --><!-- If you wish to omit certain columns from the import format specification, it is best to comment out the element --><!-- (using comment XML tags) over removing the element entirely.--><BCPFORMAT xmlns="">schemas.microsoft.com/.../format" xmlns:xsi="">www.w3.org/.../XMLSchema-instance"> <RECORD> <!--FIELD ID="import_account_id" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="import_ref_no" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="cust_type" xsi:type="CharTerm" TERMINATOR="\t"/> <FIELD ID="customer_no" xsi:type="CharTerm" TERMINATOR="\t"/> <!--FIELD ID="prefix" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="fname" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!--FIELD ID="mname" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="lname" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="55" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!--FIELD ID="suffix" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="prefix2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="fname2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="mname2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="lname2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="55" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="suffix2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="street1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="64" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="street2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="64" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="street3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="64" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="city" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="state" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="postal_code" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="country" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="phone1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="32" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!--FIELD ID="phone2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="32" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="eaddress" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="80" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="pmt_method" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!--FIELD ID="account_name" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="account_number" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="exp_date" xsi:type="CharTerm" TERMINATOR="\t" /--> <!--FIELD ID="check_number" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="campaign" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="fund" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="designation" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="source" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="channel" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="notes" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="cont_dt" xsi:type="CharTerm" TERMINATOR="\t"/> <!--FIELD ID="billing_type" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="billing_schedule" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="billing_start_dt" xsi:type="CharTerm" TERMINATOR="\t" /--> <!--FIELD ID="billing_end_dt" xsi:type="CharTerm" TERMINATOR="\t" /--> <!--FIELD ID="num_payments" xsi:type="CharTerm" TERMINATOR="\t" /--> <!--FIELD ID="decline_ben_ind" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="memb_level" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="creditee" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="creditee_type" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="custom_1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!--FIELD ID="custom_2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="custom_0" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <FIELD ID="cont_amt" xsi:type="CharTerm" TERMINATOR="\t"/> <FIELD ID="recd_amt" xsi:type="CharTerm" TERMINATOR="\t"/> <!--FIELD ID="local_use0" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--FIELD ID="local_use9" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/--> <!--The last line of the record node should have a TERMINATOR="\r\n" to indicate the end of the import row./--> </RECORD> <ROW> <!-- These columns are mapped using the SOURCE attribute value, not by order.--> <!-- The Source attribute value must match the "ID" attribute from the Record section above.--> <!--COLUMN SOURCE="import_account_id" NAME="import_account_id" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="import_ref_no" NAME="import_ref_no" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="cust_type" NAME="cust_type" xsi:type="SQLINT"/> <COLUMN SOURCE="customer_no" NAME="customer_no" xsi:type="SQLINT"/> <!--COLUMN SOURCE="prefix" NAME="prefix" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="fname" NAME="fname" xsi:type="SQLVARYCHAR"/> <!--COLUMN SOURCE="mname" NAME="mname" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="lname" NAME="lname" xsi:type="SQLVARYCHAR"/> <!--COLUMN SOURCE="suffix" NAME="suffix" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="prefix2" NAME="prefix2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="fname2" NAME="fname2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="mname2" NAME="mname2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="lname2" NAME="lname2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="suffix2" NAME="suffix2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="street1" NAME="street1" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="street2" NAME="street2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="street3" NAME="street3" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="city" NAME="city" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="state" NAME="state" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="postal_code" NAME="postal_code" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="country" NAME="country" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="phone1" NAME="phone1" xsi:type="SQLVARYCHAR"/> <!--COLUMN SOURCE="phone2" NAME="phone2" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="eaddress" NAME="eaddress" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="pmt_method" NAME="pmt_method" xsi:type="SQLVARYCHAR"/> <!--COLUMN SOURCE="account_name" NAME="account_name" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="account_number" NAME="account_number" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="exp_date" NAME="exp_date" xsi:type="SQLDATETIME"/--> <!--COLUMN SOURCE="check_number" NAME="check_number" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="campaign" NAME="campaign" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="fund" NAME="fund" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="designation" NAME="designation" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="source" NAME="source" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="channel" NAME="channel" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="notes" NAME="notes" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="cont_dt" NAME="cont_dt" xsi:type="SQLDATETIME"/> <!--COLUMN SOURCE="billing_type" NAME="billing_type" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="billing_schedule" NAME="billing_schedule" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="billing_start_dt" NAME="billing_start_dt" xsi:type="SQLDATETIME"/--> <!--COLUMN SOURCE="billing_end_dt" NAME="billing_end_dt" xsi:type="SQLDATETIME"/--> <!--COLUMN SOURCE="num_payments" NAME="num_payments" xsi:type="SQLINT"/--> <!--COLUMN SOURCE="decline_ben_ind" NAME="decline_ben_ind" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="memb_level" NAME="memb_level" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="creditee" NAME="creditee" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="creditee_type" NAME="creditee_type" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="custom_1" NAME="custom_1" xsi:type="SQLVARYCHAR"/> <!--COLUMN SOURCE="custom_2" NAME="custom_2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_3" NAME="custom_3" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_4" NAME="custom_4" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_5" NAME="custom_5" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_6" NAME="custom_6" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_7" NAME="custom_7" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_8" NAME="custom_8" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_9" NAME="custom_9" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="custom_0" NAME="custom_0" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="cont_amt" NAME="cont_amt" xsi:type="SQLMONEY"/> <COLUMN SOURCE="recd_amt" NAME="recd_amt" xsi:type="SQLMONEY"/> <!--COLUMN SOURCE="local_use0" NAME="local_use0" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use1" NAME="local_use1" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use2" NAME="local_use2" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use3" NAME="local_use3" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use4" NAME="local_use4" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use5" NAME="local_use5" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use6" NAME="local_use6" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use7" NAME="local_use7" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use8" NAME="local_use8" xsi:type="SQLVARYCHAR"/--> <!--COLUMN SOURCE="local_use9" NAME="local_use9" xsi:type="SQLVARYCHAR"/--> </ROW></BCPFORMAT>
Jolene,
This is from the documentation: "The terminator attribute sets what character (or set of characters) will be used to terminate the field. With the exception of the last field, all fields are terminated using a tab, defined by “\t”. The last field is terminated by indicating a carriage return and a new line, designated by “\r\n”. The terminator for fields can be changed, if desired. For example, it could be changed from a tab to a comma if the import data arrives in comma delimited format."
In this situation the terminator for the recd_amt row in the Record section should be \r\n not \t.
If you need further assistance, I would recommend opening a TASK ticket and we can take a closer look with you.
Dale