• Replies 3 replies
  • Subscribers 336 subscribers
  • Views 172 views
  • Users 0 members are here
Related

Constituent Import Struggles

Former Member
Former Member $organization

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

Fullscreen BO_SAP_Format.xml Download 
<?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>
Fullscreen FY19_BO_SAP_Import.txt Download 
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

  • $organization

    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.

  • Former Member
    Former Member $organization in reply to Mendy Sudranski

    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.

  • $organization in reply to Former Member

    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