Introduction

This utility was the first utility we developed to handle the import and update of customers and contacts at the same time.  Since then, we have developed a separate utility to import or update customers which we encourage you to use. See KB articles below:

    https://support.elliott.com/knowledgebase/articles/1880497-feature-added-a-generic-utility-to-mass-update-c

    https://support.elliott.com/knowledgebase/articles/1924153-feature-customer-csv-import

    https://support.elliott.com/knowledgebase/articles/1126927-feature-mass-change-salesman-utility

This document was written back when this utility was used for import and updating both customers and eContacts. This legacy feature is still supported and you can reference the feature in this documentation below.  On the other hand, if you wish to use this utility to import or update contacts only, you are encouraged to reference the following KB article:

   https://support.elliott.com/knowledgebase/articles/2014805-feature-accounts-receivable-customers-and-contac

Legacy Customer/eContact Import Utility

You can access this utility at A/R -> Utililty -> Customer/eContact Import -> customer/eContact Import -> Econtact Import. See sample screen below:

To import customer, go the following area: A/R -> Utililty -> Customer/eContact Import -> customer/eContact Import -> Customer Import.

 To mass update customer, go to the following area: A/R -> Utililty -> Customer/eContact Import -> Mass Update Utility -> Mass Change Customer. The mass change salesman is a special version of Mass Change Customer to update the salesman field only.

Introduction

The purpose of this utility is to allow contacts or customer (probably prospect) data to be imported into the Elliott Customer database.  In this document, we are going to use the word “customer” and “prospect” interchangeably.  Elliott’s approach to the prospect database is to save the prospect record as a customer record and differentiate it from a regular customer by using the customer type field. 

This utility can be used under the following scenarios:

• When you want to import a prospect database from other CRM packages, like ACT or Goldmine.
• When you want to convert from other ERP software packages to Elliott.
• When you obtain a list of prospects from a leads database provider.
• When you collect a list of prospects when attending a trade show.
• When you want to import a list of contacts under a particular customer.

The main objective of this import utility is to prevent duplicate records (customers or contacts) from being imported while making the process as easy to use as possible.  The import file needs to be in a CSV format (Coma Separated) which can be easily edited by a spreadsheet program.

When you obtain a list of prospects, you can usually open the list by using a spreadsheet program.  We suggest you save the list in the spreadsheet native format for easy editing in the future.  Then you can manipulate the columns sequence to be compatible with this import utility.  Once all necessary editing in the spreadsheet is completed, you can save the spreadsheet in a CSV format and submit it to Elliott for importing.

The import process is broken down into Pre-Interface and Interface.  Pre-Interface will show you the result of importing the CSV file without actually importing.  You should check for any warnings and errors during the Pre-Interface stage, correct the data in your spreadsheet and save it to a CSV again for another Pre-Interface.  Repeat this process until the result is satisfactory, at which moment you will then perform the Interface to actually put prospective customers or contacts into the Elliott database.

Editing Your Spreadsheet

Most of the time, you should be able to obtain your prospect data in a spreadsheet format.  Even if the data is not in a spreadsheet format, the spreadsheet program should be flexible enough to open your data in another format (like ODBC, Access, Dbase, DIF, Fixed Length ASCII, etc.).  Once the spreadsheet program has opened your data, you can save the spreadsheet in its native format for easy editing in the future.  In this document, we are going to assume that you are using Microsoft Excel, even though you should be able to use other spreadsheet programs to perform the same function as well.

Spreadsheet Columns

The spreadsheet columns represent the various different fields that you are going to import into the Elliott Customers and Contacts database.  The Elliott Customers & Contacts Import Utility assumes Columns A – O as the following:

• Column A: Customer Number
• Column B: Company Name
• Column C: Address 1
• Column D: Address 2
• Column E: City
• Column F: State
• Column G: Postal Code
• Column H: Country
• Column I: Telephone Number
• Column J: Phone Extension
• Column K: Fax Number
• Column L: Mobile Phone Number
• Column M: Contact Name
• Column N: Position
• Column O: E-mail Address

These columns are fixed and if your list does not match this format, you must manipulate your spreadsheet by using copy and paste to match the columns.  If your list does not contain a particular required column, then simply create an empty column at the right position.  You may have additional columns which require mapping to Elliott fields, like Salesman, Customer Type, Comments 1 & 2, Notes, Attributes, Links, etc.  If additional columns are provided but not mapped, those columns will simply be ignored in the import process.  You can have up to a maximum of 256 columns in your spreadsheet.

Spreadsheet Rows

Each spreadsheet row represents a customer and contact that you are going to import into Elliott.  Each customer and contact should have one row.  If you have two contacts for the same customer, then you should have two rows of data with the customer portion of the data identical. 

The spreadsheet, by default, should have a header row.  The column name of the header row is not important and you may name it anything you like since it is for your reference only.  Do not leave a blank row between the first row of data and the header row.  That is to say, the first row of data should start at row number 2.  You can have as many rows as your spreadsheet program allows you.  In Excel, you can have up to a maximum of 65,536 rows.

Columns Details

The following are detailed explanations of each column for your reference:

Column A (Customer Number)

It is possible for you to perform the following operations to the Elliott customer database upon importing the CSV file.  This is done by the data you put into the Customer Number column:

1. Adding a customer (let Elliott sequentially assign the customer number)
2. Adding a customer (you pre-assign the customer number)
3. Update a customer (with a new address, phone number, etc.)
4. Do not update the customer (i.e. add or update contacts without updating the customer)

If you are importing a list of new customers into Elliott, then you should leave the customer number column blank, which directs the import utility to assign new customer numbers sequentially.  This is operation 1.

Alternatively, you can assign customer numbers in this column and the system will use the customer number you assigned when importing into Elliott, as long as the customer number does not exist already in the Elliott database.  It is very important that you assign a unique customer number for each row and the customer number does not exist in Elliott yet.  Otherwise, an erroneous import will take place.  This is operation 2.

During the pre-interface function, Elliott will attempt to check for duplicate customers by comparing the customer name and postal code with records in the Elliott Customer File.  If Elliott detects a potential duplicate customer, it will give you a warning on the pre-interface report.  It is your responsibility to identify if they are the same customer.  If it is the same customer and you wish to take the spreadsheet data (address, phone number, etc.) to update the Elliott customer record, then you will specify the Elliott customer number in this column.  When the Elliott import utility detects that the customer number exists, it will assume you wish to perform an update and not add a new customer record.  You do not have to specify leading zeroes in your spreadsheet for Elliott’s customer number.  This is operation 3.

Similar to operation 3, but you do not wish to update the Elliott customer’s data because you feel the Elliott customer’s data is more accurate than the spreadsheet data.  Then, you will put something in this column like ‘X 100’ where ‘X’ directs the import utility to not update the Elliott customer record and ‘100’ indicates that you agree that this is the same record as customer number 100.

Column B (Company Name)

The information in this column will be used to check against the Elliott customer’s sort name for duplication.  If Elliott is to add this record into the customer database, the name you specify here will be used to update both the customer sort name and corresponding name.

The import utility will take the first word from this column and look up all Elliott customer’s names (sort name) starting with the same first word for comparison (case insensitive comparison).  If Elliott detects the first two words in company name are the same, then Elliott will give a rating of strong match (2 points).  If it detects only the first word in the company name are the same, then Elliott will give a rating of weak match (1 point).  If the first word does not match, then Elliott will skip this record since it is not likely a duplicate customer record.

If Elliott detects a strong or weak match in the name field, then it will proceed to check the postal code (zip code).  If the postal code has a 5-digit match, then it is considered a strong match (2 points).  If the postal code has a first 3-digit match, then is considered a weak match (1 point).

Elliott will warn the user in the pre-interface report that a potential duplicate customer is found if there are a total of 3 or 4 matching points.  The following is an illustration of the possible combinations:

Column C (Address 1)

If the import program is flagged to update or add to the customer file, then the information here will be written to the first address line of the customer record.  The only restriction, as with all the fields, is that you refrain from using a quote (“) and comma (,) together.  The import program uses the quote and comma (“,) and comma and quote (,”) as delimiters, so having them in your data will trigger the import program to advance to the next field.  This will split the field containing the quote and comma into two fields and the rest of the fields in the record will be off, giving an incorrect import for the record.

Column D (Address 2)

If the import program is flagged to update or add to the customer file, then the information here will be written to the second address line of the customer record.

Column E (City)

If the import program is flagged to update or add to the customer file, then the information here will be written to the city field of the customer record.

Column F (State)

If the import program is flagged to update or add to the customer file, then the information here will be written to the state field of the customer record.  The state abbreviation must be present in the State Table File.  You can maintain this table in A/R under Maintenance and State Table File.

Column G (Postal Code)

If the import program is flagged to update or add to the customer file, then the information here will be written to the postal code field of the customer record.

Column H (Country)

If the import program is flagged to update or add to the customer file, then the information here will be written to the country field of the customer record.

Column I (Telephone Number)

If the import program is flagged to update or add to the customer file, then the information here will be written to the telephone number field of the customer record.  This number will also be written to the telephone number field of the contact record in eContacts.  Elliott will attempt to re-format the telephone number into a 999-999-9999 format.  If this is a foreign telephone number that is longer than 10 digits, then Elliott will not re-format it.  If the telephone number is longer than 12 digits, then truncation will take place and Elliott will give a warning message.

Column J (Phone Extension)

This number will be written to the phone extension field of the contact record in eContacts.

Column K (Fax Number)

If the import program is flagged to update or add to the customer file, then the information here will be written to the fax number field of the customer record.  This number will also be written to the fax number field of the contact record in eContacts.  Similar to Telephone Number, Elliott will attempt to re-format Fax Number.

Column L (Mobile Phone Number)

This number will be written to the mobile phone number field of the contact record in eContacts.

Column M (Contact Name)

If the import program is flagged to update or add to the customer file, then the information here will be written to the contact field of the customer record.  This name will also be written to the name field of the contact record in eContacts.  Contact Name is also used to identify if a duplicate contact exists for the same customer.  This is especially important if the duplicate contact does not have an email address to identify duplication.

Column N (Position)

This text will be written to the position field of the contact record in eContacts.

Column O (E-mail Address)

This text will be written to the email field of the contact record in eContacts.  This is an important field to identify duplicate contacts.  Since Elliott will enforce the email address to be unique in the contact database, then Elliott will import any contact database you provide without any duplicate contact information.

Identifying Duplicate Customers in Your Spreadsheet (DEPRECATED)

NOTE: As of 3/18/2024, this section is deprecated. While it is still available to use, it is strongly recommended if you want to import contacts only without updating your customer file that you refer to this newer add-on feature.

When importing the CSV file into the Elliott customer and contact database, the import utility will check for and warn you of duplicate customers and contacts by comparing each record with the existing customers and contacts in Elliott. However, the import utility will not be able to warn you if the duplication is inside the CSV file itself. For example, if you have the following two records in your CSV file:

            , ABC Company, 1234 Main Street,, Small Ville, CA, 91789, John Smith

            , ABC Company, 1234 Main Street,, Small Ville, CA, 91789, Mary White

These two records obviously represent the same customer with two different contacts. If you do not specify in your spreadsheet that they are the same customer, then they will be imported as two different customer records.

Since most likely you wish to import them as one customer record with two contacts attached to the same customer, you will need specific instructions in the customer number column so the import utility will know these two rows are the same customer and only create one customer record upon importing. You may use the following few formats to specify duplicate customer records:

Scenario 1 - Adding Customers & Contacts:

              , ABC Company, 1234 Main St,, Small Ville, CA, 91789, John Smith

            X, ABC Company, 1234 Main St,, Small Ville, CA, 91789, Mary White

You leave the first record in the first column (customer number) blank. This implies a customer number will be sequentially assigned. The import utility will add the customer as well as the contact. You enter ‘X’ in the 2nd record first column to indicate this is a repeating customer record of the previous record. The Elliott import utility will not add the customer record, but will still add the contact record and attach it to the previous customer record.

Scenario 2 - Adding Customers & Contacts

            A, ABC Company, 1234 Main St,, Small Ville, CA, 91789, John Smith

            X, ABC Company, 1234 Main St,, Small Ville, CA, 91789, Mary White

This is identical to scenario 1. The ‘A’ in the first record means “Add” and it is the same if you leave the first column blank. You may put an “A” in the first record customer number column for it is visually easier to identify a group of identical customer records.

Scenario 3 - Adding Contacts & Maybe Adding or Update Customers:

            100, ABC Company, 1234 Main St,, Small Ville, CA, 91789, John Smith

            X   , ABC Company, 1234 Main St,, Small Ville, CA, 91789, Mary White

This indicates “ABC Company” either already exists in Elliott (if customer 100 is found in the Elliott customer database) or customer number 100 does not exist yet and you wish to assign the specific customer number 100 for “ABC Company.” If customer 100 exists in Elliott, the system assumes you wish to use the data (address, phone, etc.) in the CSV file to update the Elliott customer record. The “X” in the second row indicates that the second row is the same customer as the previous row and do not add or update the customer with the 2nd row. The contact, Mary White, will be added (if not duplicated) to customer 100.

Normally, you can find if ABC Company already exists in Elliott by running the Pre-Interface function. The system will warn you that ABC Company is probably the same as the customer number 100 that already exists in Elliott. You will then need to make a judgment if the warning is valid. If it is same customer that exists in Elliott, you will then put ‘100’ into the customer number column in the CSV file to indicate that you agree this record is the same and the import utility will not create a new customer record.

Many users from this mode of operation confusing because it may add customer, change customer or do nothing with the customer depending on various condition. Evaluate the functionality carefully and decide if you want to use this mode of update.

Scenario 4 - Adding Contacts & Do Not Add Customers:

            X 100, ABC Company, 1234 Main St,, Small Ville, CA, 91789, John Smith

            X       , ABC Company, 1234 Main St,, Small Ville, CA, 91789, Mary White

This is similar to scenario 3 and implies that customer number 100 already exists in Elliott. ‘X 100’ implies that you agree that ‘ABC Company’ is the same as the Elliott customer number ‘100’ and you do not wish to update customer ‘100.’

The only difference between Scenario 3 and 4 is scenario 3 will update the Elliott customer data from the spreadsheet data (assuming the spreadsheet has the latest data) and scenario 4 will not update the Elliott customer data (assuming the Elliott data is more current). If your intention is to update contacts only, then use scenario 4.

Scenario 4 also supports the updating of Ship-To contacts. For example:

            X 000100001,ABC Company, 1234 Main St., Small Ville, CA, 91789, John Smith,,jsmith@company.com

If the first character is and 'X' and the customer number is longer than 6 characters, the application will attempt to add an econtact for the ship-to number specified. This will not update the ship-to record itself. The ship-to record for the customer must already be on file when the import is run. If the address provided matches the adress on file in the ship-to record, the econtact will be linked to the ship-to record. Otherwise, the econtact will not be linked and will use the address provided.

Importing the Data

From the Accounts Receivable’s Util-setup menu, we have added customer/eContact import.  A batch ID must be assigned to each import process.  This allows you to import multiple files at the same time.  This menu allows you to add, change, delete and list the batches.  Also, you can get a list of the CSV layout, perform a pre-interface and interface the CSV to the Customer File and eContact File.

Adding A Batch

There are 9 parameters that can be given to each batch:

1. Batch ID

Each batch must have a unique batch ID.  This can be up to 6 characters in length.

2.   Description

A 30 character description can be given to each batch.

3. Interface File Path and Name

Specify the full path and file name where the CSV file is located.

4. CSV File Contains Header Row?

If your CSV import file contains a header row, then answer “Y” here.

5. Default Customer Type

You can specify a customer type and a list is available by pressing F7.  If you leave this blank, then it will use the customer type in the Default Customer, &&&&&&.  If the Default Customer does not exist, then the customer type field will be left blank.

6. Default Salesman No

You can specify a salesman number and a list is available by pressing F7.  If you leave this blank, then it will use the salesman number in the Default Customer, &&&&&&.  If the Default Customer does not exist, then you will receive a warning.  If left in this condition, you will receive a warning when attempting to import the batch.  Either specify a default salesman number or create the Default Customer which will supply the salesman number.

7. Comments 1 & 2

You can specify what is to be filled in for comments 1 and 2.  If you leave these blank, then it will use the comments in the Default Customer.  If the Default Customer does not exist, then the import will leave these fields blank.

8. Delete File After Interface?

If the interface is successful and this flag is set to “Y”, then the import file will be deleted.

9. Delete Batch After Interface?

If the interface is successful and this flag is set to “Y”, then the batch will be deleted.

An edit list can be printed for each batch to insure the parameters set are correct.

The Layout menu has two options.  The first option will display the required columns for the import file and the second option will print.  The print option will prompt you for starting and ending batch IDs.  If you have three batches on file and you specify all batches, then three layouts will be printed.  Although the layouts will be the same for each printout, each one will print the parameters of each batch (ID, description, file name and path, etc.).

We strongly suggest you review the pre-interface before performing the interface.  This review will assist you with importing clean data.

Before an import can be performed, the Customer Number Sequencer must be turned on.  This can be found in the Global Setup under Acct, A/R Global Control and field number 6, Use Customer Number Sequencer.

The state abbreviation should be present in the State Table File.  You can maintain this table in A/R under Maintenance and State Table File.  If the abbreviation does not exist, then the import will present a warning, but will let you import the record.

Modified Programs: ARECIIMP.PL, ARECIIMP.WS, ARECIBAT.FD, ARECIBAT.WS, ARECIBAT.SL, 

                                ARECEIBAT.DS, ARECIBAT.IO, ARECIITP, ARECILST, ARECIMNT, ARECIPIP

EMK