Hello,
I am in the process of conducting a series of constituent imports. I need to include the Attribute Value to the Format File XML file. The default format file available on the network excludes this column, and I was wondering if anyone knew where I could find an XML file with the attribute value column added in. I am swimming in the land of XML, and so my next step if there isn't a preexisting file available, would be to figure out how to create the XML file with attribute value included.
Thanks!
Andrew
The XML lives on the server in the Import Formats folder. Example our path looks like L:\Impftp\Import Formats. Just take the comment out and save.
Thank you, Travis! I am making progress...I removed the comment from the attribute lines by copy/pasting the XML on the server in to notepad and then saving as .XML. I am now getting the error message when running the import utility:
9438 (error reading the format file) XML parsing: line 1, character 8, text/xmldecl not at the beginning of input.
This is my code...
<?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="20" COLLATION="SQL_Latin1_General_CP1_CI_AS" /> <FIELD ID="state" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS" /> <FIELD ID="postal_code" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" 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" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS" /> <FIELD ID="customer_no" xsi:type="CharTerm" TERMINATOR="\r\n" /> - <!-- 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" /> </ROW> </BCPFORMAT>
You have all the columns in the file? Even if there is nothing the column it must be there and in the proper order. Review your import file.
We just completed our first constituent import yesterday in v11 - so we've been playing with this a lot. I haven't found the error messages it spits out to be all that helpful, though. :(
An easy thing to overlook is that if you are creating new constituent records, you'll need a space in the customer_no column in Excel - and double check that space is in your final tab-delimited text file (try turning on invisible characters in Word to see spaces and paragraph breaks). I think I ended up typing <apostrophe><space> in each field to make sure the space stuck there.
Also - maybe its just a forum or cut/paste thing - but it looks like you have some extra dash characters in the code before each comment marking ( this: - <!-- should just be this: <!-- ) - an extraneous dash also appears at the beginning of a few other lines (at least here in the forum).
Beth
Thanks so much, Beth and Travis, for your support! I ended up using a different program to edit the standard form and it worked! The program I used was Microsoft Visual Studio 2008.