Overview

The customer import framework allows users to import new and updated customers and customer addresses using an Excel template. The template is provided and can be downloaded from the system with the existing customers and addresses or as a blank template for data entry.

Step 1: Downloading the Template

From NorthScope, expand the Company module on the navigation menu and select ‘Integration Inbound’. By default, this list view displays all integration inbound transactions other than those with Complete and User Bypassed statuses. Clicking the ‘Inquiry Criteria’ toolbar icon opens a dialog window, which allows users to edit the inquiry criteria to e.g. display completed integration transactions.

Click the ‘File Import’ toolbar icon to download a customer template. This opens a new dialog window. Select ‘Customer’ as the template. To create a blank customer import template, select Customer from the Choose Options dropdown menu and click ‘Create Template File’ to generate the template. To create a template with the existing item data, check the check box Include Data. This will export all item data to the template. To create a blank customer address template, select Customer Address from the Choose Options dropdown menu, or to create a blank template for both Customer and Customer Address select both options from the Choose options dropdown menu and then click Create Template File.

The template is saved to your computer’s default download location (e.g. Downloads folder). Depending on your browser and browser settings, the file download might be also displayed on the footer or header of the browser window for easy access. The file can be opened from the actual file location or from the browser header / footer.

Step 2: Entering Data

Entering New Customers

The template contains column headers and data can be entered under the column headers (starting from row 2). The following data is always required:

Customer ID
- Must be unique to create new customers.

Customer Name
Customer Class
(Attribute Class)
- Must be provided only if importing Attribute values.

Checkbook
- This value defaults from the customer class.

Payment Terms
- This value defaults from the customer class.

Other customer details are optional and can be provided in the file or updated from the item record once the items have been uploaded to the system.

All customer attributes are displayed as columns. If a user provides a new list type attribute value, the value is automatically added to the attribute values.

Entering New Customer Addresses

The template contains column headers and data can be entered under the column headers (starting from row 2). The following data is always required:

Customer ID

Must be unique to create new customers.

Address Name
Address ID
- Must be unique to create new addresses.
Customer Class
Address Type

An Address can only have one address with type “Billing” or “Both”.

(Address Attribute Class)

Must be provided only if importing Attribute values.

Item Access
Sales Order Freight Programs

Updating Existing Customer Data

When the template is created with the existing item data, the cells are populated with the existing customer data. Most of the customer properties are editable except the following:

Customer ID

Also, the following data must be provided and should not be removed:

Customer Name
Customer Class
(Attribute Class) if attribute values are provided
Checkbook
Payment Terms

New customers can also be created while updating existing customers. To do this, enter the customer details to the end of the template.

Updating Existing Customer Address Data

When the template is created with the existing item data, the cells are populated with the existing customer address data. Most of the customer address properties are editable except the following:

Customer ID
Address ID

Also, the following data must be provided and should not be removed:

Address Name
Customer Class
Address Type
(Address Attribute Class) if attribute values are provided
Item Access
Sales Order Freight Programs

New customer addresses can also be created while updating existing customer addresses. To do this, enter the customer address details to the end of the template.

Due to the limitations of Excel, when users enter hyphens or forward slashes into a text field, Excel may interpret the value as a date. For example, the value “2-3” may be converted to the text representation date value, which displays as ”43864”. Similarly, when users enter leading zeros into a field, the leading zero may be removed. Users can now use the escape characters of “^^” to preface any values with hyphens, forward slashes or leading zeros. During the file import process, NorthScope will recognize the escape characters, automatically remove them and insert the value immediately following the escape characters.

Step 3: Saving the Template File

The file must be saved in a CSV format. By default, Excel creates the template in a TXT format and users must manually change the file format. This is very important, because the upload fails if the file type is not correct. To do this click ‘Save As’ in Excel and select 'CSV (Comma delimited) (*.csv)' as the file type before clicking Save.

Step 4: Uploading the Template File

If you have closed the file import window, re-open it and re-select ‘Customer’ as the template and either Customer, Customer Address or both from the Choose options dropdown. Click on ‘Select Files to Import’ and locate the file you saved on your computer. Select the file and click Open. Verify that a green dot is displayed in front of the file name. If the file is not in the correct format, a red dot is displayed indicating that the file cannot be uploaded to the system.

After the file has been selected and the file type validation is green, click on ‘Upload to NorthScope’. A pre-validation is performed before the file is added to the integration queue. This validation verifies the correct lookup values have been provided (e.g. the customer class provided exists in the system) and that all required data is provided (e.g. CustomerID, AddressID ). If the pre-validation does not pass, an error message is displayed for the user with the error details.

Successfully uploaded files are added to the Integration Inbound list view with the status New. When the integration has been processed successfully, the status is changed to completed and the new/updated items are added to the system. If the file cannot be integrated (e.g. duplicate customer ID/ address ID is provided), the status is updated to Error and the Error Text field displays the reason why the integration failed. When a file fails to integrate, none of the customers/customer addresses from the file are imported to the system and the file fails as a whole. Users must fix the errors in the file and re-import the file.

Customer and Customer Address Import (UG)