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