Address Import

The Address Import Utility imports address information into Emplifi Agent from a text file. Possible uses include loading store addresses, a list of contacts from a trade show, purchased address lists, or any other suitable list of contacts.

This utility provides a simple method to import addresses, but does not replace functionality found in custom import utilities built specifically for your company.

What the utility does:

Imports addresses from a data file into the Emplifi Agent database.
Optionally deletes all addresses of a particular Address Type Code before importing.
Optionally creates related issue category codes (when importing stores).
Sets address attributes.
When the Include PowerLocator check box is selected, Longitude and Latitude are looked-up and added to the address record.

What the utility does not do:

Does not check for duplicate address information.
Does not update existing address or phone number records.
Does not look-up postal code.
Does not validate the address.
Does not modify case data.

To access the Address Import Utility:

From the Application Function Bar, select Utilities > Address Utilities > Address Import.

Utility Options

The following options are available for the Address Import Utility:

Field	Description
Address Type	Select an Address Type Code to be assigned to each address being imported.
Insert Category Code	Select this option to add a code for each address being imported. The Address Type must be specified, and the type must be tied to a category code in order for this option to be used. The utility will not run if these requirements are not met.
Include in PowerLocator	Select this option to set the PowerLocator flag to true for the addresses being imported.
Phone Type 1	Phone Type Code for the first phone number contained in the import file.
Phone Type 2-4	Phone Type Code for additional phone numbers contained in the import file.
Update Mode	Select the operating mode for the utility from the following options: Insert: Choosing this method will insert new addresses for all records contained in the import file. Delete/Insert: Choosing this method will first delete all addresses of the address type specified on the utility, and then insert new addresses for all records contained in the import file. Choosing this method will delete all addresses of the specified type, and a type is required before the utility will run.
Import File Name	Enter the Transfer folder full path including file name for the import file. Make sure that the selected path is available from the Emplifi Agent Server, preferably via a UNC path or a mapped drive. If a mapped drive is used, the drive mapping must exist for the user account under which the Emplifi Agent Server runs. Uploading HTML files that contain <script> tags is not permitted.
Start Row, End Row	The number of the first and last row to be imported. If these values are missing, the import utility will assume the start row is 1 and the end row is the end of the file.
Start Column, End Column	The first and last numeric position of the columns to be imported in the import file. Usually this will be column 1 through 23, which relates to Emplifi Agent columns 5 (address code) through 28 (a06_code). If these values are missing, the import utility will assume the first column is one and the end column is 119.
To Column	The numeric position of the column in Emplifi Agent where the import file will start populating data. Usually, this is column 5 (address code).
Results Language	Select the results language for the utility. For more information, see the Results Language section on page Utilities Overview.

Address Import Utility File Format

The Address Import Utility imports addresses from a tab-delimited text file. There should be one address record per line. Using the column order below, the file would have data entered for each column and divided by a tab. Column 1 is the leftmost column and column 119 is the rightmost column in the text file.

The order of the columns in the data file does not match the order of the columns in the database address table.

The data file can be created by entering address information in a spreadsheet and saving it as a tab delimited file, manually using a text editor, or by a custom automated process. Only information that maps to the columns in the database address table can be imported by this utility.

Keep in mind the following information when creating your data file:

Categories With Codes	If a category is defined as having codes (as opposed to strings or dates), code values from the data file must be valid, if not, an empty value is imported.
Company ID	The current company ID is always used. The value in the data file is ignored.
Address ID	When an address ID exists in the data file, it is used. If an address already exists with that ID, an error will occur.
Email columns	The column named “email” is used by the system to hold a Soundex look-up value for first name. The column that holds the first email value is named “email2”, and the column that holds the second email value is named “email3”.
Phone Numbers	The importer expects phone numbers one through four to be ten digits in length, like “6145086100.” This phone number will be saved in the address_phone table as “614-508-6100.” If a seven-digit number (“5086100”) is supplied in the data file, it will be saved in the address_phone table as “508-610-0.”

Column Mapping

The following is the column mapping if you were creating the file using a spreadsheet:

Column Number	Spreadsheet Column	Emplifi Agent Column Name	Comments
1	A	company_id	Company ID: Value does not matter. The utility always uses the current company ID.
2	B	address_id	Address ID: used if supplied, otherwise the next available ID will be used.
3	C	hierarchy_code	Hierarchy Code: Parent of code being inserted. Required when using Insert Category Code.
4	D	address_type_code	Address Type Code: defined category code, like “STORE.” When using Insert Category Code the value is set by the Address Type control on the options screen.
5	E	address_code	Address code: a string value. Required when using Insert Category Code. This becomes the value of the related issue code.
6	F	company_name	Company name: a string value
7	G	address1	Street address 1, a string value
8	H	address2	Street address 2, a string value
9	I	city	City, a string value
10	J	state	State: a defined code, like “OH” for Ohio
11	K	postal_code	ZIP or postal code. Utility does not generate nor validate. Utility does not insert formatting (dash).
12	L	country	Country: defined code, like “USA.”
13	M	phone1	Phone number 1 (type set by import options). The utility inserts dashes, and expects a raw 10-digit string like “6145086100”.
14	N	phone2	Phone number 2 (type set by import options). The utility inserts dashes, and expects a raw 10-digit string like “6145086100”.
15	O	phone3	Phone number 3 (type set by import options). The utility inserts dashes, and expects a raw 10-digit string like “6145086100”.
16	P	phone4	Phone number 4 (type set by import options). The utility inserts dashes, and expects a raw 10-digit string like “6145086100”.
17	Q	instructions	Instructions: string
18	R	account_number	Account number: string
19	S	email	Email: Although it’s named “email”, the column is used to store the first-name Soundex value (email2 is the first email). The data file value doesn’t matter. This value is system generated.
20	T	job_title	Job title: like: “Manager”, defined category code
21	U	name_title	Name title: like “Mrs.”, defined category code
22	V	given_names	First name
23	W	middle_initial	Middle initial (single character, no period)
24	X	last_name	Last name
25	Y	suffix	Suffix: like “Jr.”, value is not validated
26	Z	a05_code
27	AA	a06_code
28	AB	a07_code
29	AC	a08_code
30	AD	a09_code
31	AE	a10_code
32	AF	a11_code
33	AG	a12_code
34	AH	a13_code
35	AI	a14_code
36	AJ	a15_code
37	AK	active	Active: address active flag, values: Y or N
38	AL	allow_survey	Allow survey, values: Y or N
39	AM	originated_via	Originated via: data source. Values: 0=Unknown, 1=Case Entry, 2=Address Editor, 3=Import Utility
40	AN	originated_date	Originated date: date/time
41	AO	last_modified	Last Modified: date/time
42	AP	last_contact	Last Contact: date/time
43	AQ	last_survey	Last Survey: date/time
44	AR	accumulated_goodwill	Accumulated Goodwill: Dollar amount
45	AS	where_to_buy	Where To Buy: “Include in PowerLocator”. The data file values doesn’t matter. The Include In PowerLocator setting from the options screen overrides the data file values.
46	AT	latitude	Latitude: data file value used if Include In PowerLocator option check box is not ticked, if ticked the value is supplied by the postal verification software.
47	AU	longitude	Longitude: data file value used if Include In PowerLocator option check box is not ticked, if ticked the value is supplied by the postal verification software.
48	AV	search_name	Last-name Soundex value. The data file value does not matter. This value is system generated.
49	AW	search_address	Street address 1 Soundex value. The data file value does not matter. This value is system generated.
50	AX	address3	Street address 3.
51	AY	email2	Email address 1.
52	AZ	a16_code
53	BA	a17_code
54	BB	a18_code
55	BC	a19_code
56	BD	a20_code
57	BE	a21_code
58	BF	a22_code
59	BG	a23_code
60	BH	a24_code
61	BI	a25_code
62	BJ	repeater_code	Repeater code. Category code, like: “repeater”, or “goodwill”
63	BK	a26_code
64	BL	encl_auth_level	Enclosure Authorization Level. Defined values: 1 through 9, 1=highest, 9=lowest
65	BM	email3	Email address 2.
66	BN	a27_code
67	BO	a28_code
68	BP	a29_code
69	BQ	a30_code
70	BR	a31_code
71	BS	a32_code
72	BT	a33_code
73	BU	a34_code
74	BV	a35_code
75	BW	a36_code
76	BX	a37_code
77	BY	a38_code
78	BZ	a39_code
79	CA	a40_code
80	CB	a41_code
81	CB	a42_code
82	CD	a43_code
83	CE	a44_code
84	CF	a45_code
85	CG	a46_code
86	CH	a47_code
87	CI	a48_code
88	CJ	a49_code
89	CK	a50_code
90	CL	a51_code
91	CM	a52_code
92	CN	a53_code
93	CO	a54_code
94	CP	a55_code
95	CQ	a56_code
96	CR	a57_code
97	CS	a58_code
98	CT	a59_code
99	CU	a60_code
100	CV	a61_code
101	CW	a62_code
102	CX	a63_code
103	CY	a64_code
104	CZ	a65_code
105	DA	a66_code
106	DB	a67_code
107	DC	a68_code
108	DD	a69_code
109	DE	a70_code
110	DF	a71_code
111	DG	a72_code
112	DH	a73_code
113	DI	a74_code
114	DJ	a75_code
115	DK	a76_code
116	DL	a77_code
117	DM	a78_code
118	DN	a79_code
119	DO	a80_code

Examples of How to Use the Utility

The following are three examples of how to use this utility. Each example provides step-by-step instructions. There are many other ways you can use this utility. These examples are provided only as a basis for how you can use the utility for your company.

Example Using the To Column

It is not necessary to use all 119 columns available for import. This example only uses 20 columns, “company_name” through “suffix”. The utility supplies values for several system columns automatically, such as company_id, address_id, Soundex values, and originated_date.

Create a Spreadsheet.
1. Define the columns as text.
2. Enter labels in the first row so that you can identify the column while editing. Label the first 20 columns, beginning with “company_name”. The last column will be “suffix”. For a table of the column names, see the Address Import Utility File Format section above.
3. In the second row, type the column values for each address.
4. Save the spreadsheet as “Text, tab delimited”.
From Application Function Bar, go to Utilities > Address Utilities > Address Import.
In the Address Type field, select the appropriate address type that applies to all addresses in the data file or leave the field blank.
Select the Insert Category Code check box.
Select the Include in PowerLocator check box.
Select a Phone Type for phones 1 through 4.
In the Update Mode field, select Insert.
In the Import File Name field, type the complete path and file name for the data file.
In the Start Row field, enter a value of 2 to skip the column labels.
Leave the End Row field blank to import all remaining rows.
In the Start Column field, enter a value of 1. The first 6 columns defined by the utility are not included in the data file created in step 1 above.
In the End Column, enter a value of 20 to include the first 20 columns.
In the To Column field, enter a value of 6 to begin with the sixth column defined by the utility.
Click the Run Utility button.
Check the log file for errors.
Check the results by searching Emplifi Agent for several of the imported addresses.

Example Using All Columns

Create a spreadsheet.
1. Define the columns as text.
2. Enter labels in the first row so that you can identify the columns while editing. Label a column for all 119 columns of the address table. For a table of the column names, see the Address Import Utility File Format section above.
3. In the second row, type-in the column values for each address. The first five columns can be left empty:
  - company_id - the utility always uses the current company_id.
  - address_id - the utility will use the next available ID.
  - hierachy_code - leave empty, it is used with the Insert Category Code feature.
  - address_type_code - leave empty if an Address Type is supplied on the options screen.
  - address_code - leave empty, it is used with the Insert Category Code feature.
4. Enter values in the remaining columns as needed.
5. Save the spreadsheet as “Text, tab delimited”.
From Application Function Bar, go to Utilities > Address Utilities > Address Import.
In the Address Type field, select the appropriate address type that applies to all addresses in the data file or leave the field blank.
Select the Insert Category Code check box.
Select the Include in PowerLocator check box.
Select a Phone Type for phones 1 through 4.
In the Update Mode field, select Insert.
In the Import File Name field, type the complete path and file name for the data file.
In the Start Row field, enter a value of 2 to skip the column labels.
Leave the End Row field blank to import all remaining rows.
In the Start Column field, enter a value of 1.
Leave the End Column field blank to include all columns.
Leave the To Column field blank to allow all utility import columns to be used.
Click the Run Utility button.
Check the log file for errors.
Check the results by searching Emplifi Agent for several of the imported addresses.

Example Using Insert Category Code to Create Store codes

This example uses all 119 columns, and adds C08 store codes for each address imported. Your categories may be defined differently, and values used in the example may need to be modified.

Create a spreadsheet.
1. Define the columns as text.
2. Enter labels in the first row so that you can identify the columns while editing. Label a column for all 119 columns of the address table. For a table of the column names, see the Address Import Utility File Format section above.
3. In the second row, type-in the column values for each address. The first five are entered as follows:
  - company_id - the utility always uses the current company_id.
  - address_id - the utility will use the next available ID.
  - hierachy_code - this is the parent of the C08 code that will be created for this address row. In this example, the hierarchy is “Store” / “East” / “OH”. The value entered in column #3 will be OH, and the new C08 codes will show up in the store hierarchy under OH when browsed. The hierarchy_code can be different in each row, as needed. The address for the next row may be for a store in Indiana, and the defined category code may be “IN”.
  - address_type_code - leave empty, the Address Type is supplied on the options screen as "STORE" and will override any value entered in the Insert Category Code mode.
  - address_code - this is the value of the C08 code that will be created. This code will be displayed when the C08 Store hierarchy is browsed. You may want to use the store number.
4. Enter values in the remaining columns as needed.
5. Complete a spreadsheet row for each address to be imported.
6. Save the spreadsheet as “Text, tab delimited”.
From Application Function Bar, select Utilities > Address Utilities > Address Import.
In the Address Type field, select STORE.
Select the Insert Category Code check box.
Select Include in PowerLocator check box.
Select a Phone Type for phones 1 through 4.
In the Update Mode field, select Insert.
In the Import File Name field, type the complete path and file name for the data file.
In the Start Row field, enter a value of 2.
Leave the End Row field blank.
In the Start Column, enter a value of 1.
Leave the End Column field blank.
Leave the To Column field blank.
Leave the Results Language field blank.
Click the Run Utility button.
Check the log file for errors.
Check the results by searching Emplifi Agent for several of the imported addresses.