CRM - Bulk import account data
Overview
Sequoia provides the ability to import multiple account records. This functionality is available from within the Sequoia toolbox application. This functionality is only available to run on the Sequoia Server, within the Sequoia Toolbox application. The data to import will need to be in a CSV file format.
This tool is not intended for day to day use, and can only be performed by someone with access to the Sequoia Server.
Launch the toolbox and get ready to import.
- Launch the toolbox application on your Sequoia Server.
- You can get to this in one of the following ways (depending on your OS):
- If you have a Start menu - use it to find the 'Sequoia' folder and click on the toolbox icon.
- Another way is to open 'File Explorer'
- Navigate to your Sequoia Server folder (typically installed in
c:\Program Files x86\ASM\Sequoia Server
) - Navigate to the
Toolbox
sub folder - Open the file called
ASM.Sequoia.Toolbox.exe
- Navigate to your Sequoia Server folder (typically installed in
- You can get to this in one of the following ways (depending on your OS):
- If the 'External Data Importing' menu is not expanded in the menu bar on the left, expand it.
- Click on 'Import Accounts'
- You will see a tab appear on the right hand side as shown in the screen shot below.
How to import
- Specify the 'User' who will be importing the account records.
- All accounts are created by a user of Sequoia. This is shown in Sequoia as the 'Created by' value. In order to import, you will need to select a user account to assign as the person who created your account from the 'User Context' dropdown.
- You will typically find and select your own user account from the drop down.
- If you do not have a Sequoia user account, please ensure you have permission from the person whose user context you are using.
- Specify the 'Data Source'. This is the file location of the data you wish to import.
- You can type or paste in the file location. This file location needs to be fully qualified.
- i.e.
c:\users\jane doe\desktop\accounts.csv
- i.e.
- If you would like to browse your file system for the file you wish to import, click on the ellipsis at the end of 'Data Source' field.
- You can type or paste in the file location. This file location needs to be fully qualified.
- When you have selected a file, click the 'Import File' button.
- If the file exists, data will be imported.
- If the file does not exist, you will be prompted to specify a valid file location.
- All data is imported from the CSV file into the data grid, so that you can preview the data to import into Sequoia.
An example of a simple data import is shown below.
- In the screenshot above, there is one record which has warnings (the yellow triangle on the left).
- Rows which have one or more warnings will not be imported. The data in the row will need to be corrected prior to import.
- When selecting a row with warnings, a list of problems will appear in the 'Validation Results' pop-up box at the bottom right of the screen (as shown).
As and when you correct problems with the account record, the warnings will disappear.
- You can import data within the preview data grid into Sequoia by clicking the 'Import Data' button.
- If you try to import one or more account records, only records which do not have warnings (accounts which are valid) will be imported.
- All remaining records which have not been imported will remain in the list so they can be corrected, and the importation process can be tried again.
Resetting the form
If you wish to reset the form so that you can process another CSV file, you can click on the 'Reset' button. You will be prompted to clear existing data, and if you choose to, the form will be reset so you can start the process over again.
Overwriting existing accounts in Sequoia.
You cannot use this tool to overwrite existing account data in Sequoia.
If you try to import an account record with a short code that matches an account already in Sequoia, the import (for that particular account record) will fail and you will receive a warning against that line on the left.
The account record with the same shortcode which is already in Sequoia will remain unchanged.
CSV file format
A CSV file is a delimited text file that uses a comma to separate records.
This file type is commonly used for data exchange between computer systems and is widely supported. You can even save Excel data in CSV format.
The CSV file should not have a header row (a row which lists the field names should not be present), and the order of the data must be exactly as described below. Every field below should be represented within the CSV file.
The following table defines the supported CSV file format.
CSV Field | Optionality | Data Format | Max Length |
---|---|---|---|
Account Name | Mandatory | Any character | 200 |
Short Code | Mandatory | Any upper alpha numeric character | 20 |
Account Number | Optional | Any character | 200 |
EORI | Optional | 2 upper alpha characters followed by up to 15 upper alpha numeric characters | |
Street 1 | Mandatory | See address restrictions below | 50 |
Street 2 | Optional | See address restrictions below | 50 |
Street 3 | Optional | See address restrictions below | 50 |
City | Optional | See address restrictions below | 50 |
State | Optional | See address restrictions below | 50 |
Post Code | Optional | See address restrictions below | 20 |
Country Code | Mandatory | 2 upper alpha characters only | |
Telephone | Optional | Any character | 50 |
Fax | Optional | Any character | 50 |
Deferment Prefix | Optional | The letters A or B or C only | |
Deferment Number | Optional | 7 numeric characters | |
Default Representation | Optional | The letters D or I only |
Address Field Restrictions
The same rules for creating a record within the Sequoia client application apply when importing data using the bulk import utility. The application will inform you of any problems with data that may exist within the preview data grid. These rules and restrictions are enforced to ensure that Sequoia behaves correctly with your new account data.
The importation utility imposes further address field restrictions. Only the following characters are allowed:
Character | Restriction |
---|---|
Letters, upper case | A to Z |
Numerals | 0 to 9 |
Space character | |
Full stop | . |
Comma | , |
Hyphen/minus sign | - |
Opening parentheses | ( |
Closing parentheses | ) |
Oblique stroke (slash) | / |
Apostrophe | ' |
Plus | + |
Colon | : |
Equals sign | = |
Question mark | ? |
Exclamation mark | ! |
Percentage sign | % |
Ampersand | & |
Asterisk | * |
Semi-colon | ; |
Less-than sign | < |
Greater-than sign | > |