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:
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 | Spreadsheet | Emplifi Agent | 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: 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.
Define the columns as text.
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.
In the second row, type the column values for each address.
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.
Define the columns as text.
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.
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.
Enter values in the remaining columns as needed.
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.
Define the columns as text.
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.
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.
Enter values in the remaining columns as needed.
Complete a spreadsheet row for each address to be imported.
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.