I've been going round and round with the Constituent Import utility and have had no luck getting the import to work. At this point, it's skipping the first row of data and merging (sort of) every other row. The prefix, name, address from row 3 is showing up in the local_use8 and local_use9 columns in TW_CONSTITUENT_IMPORT. I've attached my import file and header - hoping someone has some insight to share...
<?xml version="1.0"?> <!-- Constituent 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="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" > <RECORD> <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="55" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="street2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="55" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <!-- FIELD ID="street3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="55" 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="attribute" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" 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="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="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="attribute" NAME="attribute" xsi:type="SQLVARYCHAR"/--> <COLUMN SOURCE="cust_type" NAME="constituent_type" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="customer_no" NAME="customer_no" xsi:type="SQLINT"/> <COLUMN SOURCE="local_use0" NAME="local_use0" xsi:type="SQLINT"/> <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>
prefix fname mname lname suffix prefix2 fname2 mname2 lname2 suffix2 street1 street2 city state postal_code phone1 phone2 eaddress cust_type customer_no local_use0 local_use1 local_use2 local_use3 local_use4 local_use5 local_use6 local_use7 local_use8 local_use9 Aja Anderson 1370 Saint Nicholas Ave. Apt #32J New York NY 10033 202-903-4201 ajatealanderson@gmail.com 1 Parker Battle 4103 Silk Court Spring TX 77386 713-202-1959 parker.battle.lx@gmail.com 1 Schuyler Bento 97 Kurt Lane Hauppauge NY 11788 469-456-5303 skyb15@icloud.com 1 Ms. Andrea Burns 1614 Easton Ave. Bethlehem PA 18017 904-508-6596 andieleighburns@gmail.com 1 Mr. Javier Calderon 2586 Blue Bird Road Winter Haven FL 33884 863-618-6708 javier.calderon@ymail.com 1
Hi, i don't see anything obvious after comparing yours to mine. you might want to start by hand creating an import file in a text editor with a few fake records to try to determine if you're having an import issue or a data issue. in my experience, if data is showing up in the wrong column it is usually some hidden characters in the import data. it might take a text editor like notepad++ or lopping through each character in ssms to find one character throwing everything off. i would start with the record right before it all went horribly wrong.
Thanks Mendy! Great minds - I installed Notepad++ yesterday and started going through the process you mention. No joy there, everything looks fine. I'll try hand crafting the import file and see what happens.
Andy,
I took the liberty of taking your data and seeing what happens - I ran the utility in Review mode. When I checked the TW_CONSTITUENT IMPORT, the data looked all fine.
I note that your data file appears out of step with the format file - the data is missing the Country column - could this be an issue? It SHOULD stop the file being uploaded - did it not error?
I was also wondering in what format you're saving the file. If you work on the data in Excel and save as a text format other than as "Text (Tab delimited) (*.txt)" - so saving as Unicode, MS-DOS or Mac - the resultant file has some very strange (and invisible) text properties.
Martin