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
Hi Jolene,
I have a personal document on how to do a contribution import from a while back because I had such a hard time with it. I also got the sync error and here is what was in my notes regarding it:
We see this when the columns in the data file don't match the columns listed in the format file. Look at the data file with the one row that successfully imported and compare it to the data file with the other rows and make sure each and every column is represented (adding a header row is very helpful, and will not hurt the import process).
I believe the headers in the import file have to be spelled exactly as they are in the format file or you'll get errors. the contribution amounts in your import file also have to be formatted a specific way. I haven't used it in a while, but our Gift Entry team uses it weekly. there was quite a bit of testing when we first set it up.