Overview
In this topic we are going to import data via the user interface of the ITRDS client. For this to work you must have either a DBF or XML file containing the data you want to export. The fields in this file must match the names of the fields in Rams to which they are being applied. You can have more fields or less fields in the file. Only matched fields are looked at.

Scenario
In the following example we are going to import cutomer records that have been initially received in Excel format.

Step 1
You will now doubt receive the data in an excel spreadsheet. If so, the first thing you need to do is add or modify existing column names to this spreadsheet in order to match the column names in the Rams tables. Even if the columns relate to fields in different tables in Rams, still rename them to the relevant column name in the relevant table.

TRY AND MAKE SURE THAT WHEN YOU RECEIVE THIS FILE, IT CONTAINS A UNIQUE ID FOR EACH CUSTOMER AND RENAME IT HERE TO BE CALLED ‘IMPORTID’ (OR USER_NUM IF IT’S LESS THAN 10 CHARACTERS). IF SUCH A FIELD DOES NOT EXIST AND YOU HAVE MULTIPLE CUSTOMER RECORDS FO REACH CUSTOMER ADD THE FIELD IN THE SPREADSHEET.

This may be the old account number from the system you are receiving this data from. This is not so important when you have one record for each customer, but if your import file has more than one record per customer, as in the case where there is a record for each bin, this is very important. If you cannot get this unique number then you need to create one which we will explain later.

Step 2
Import this data into a FoxPro table within a data base. The reason we import into a data base is so that we can specify long field names which is what Rams uses. Hence create an empty database (called ‘import’ for example). To perform this import use the import wizard in FoxPro and make sure you import into a table in your ‘import database. Also make sure that you name the destination table 'IMPORTFILE'

Step 3 - Rename Field Names
In Step 1 we renamed columns to the same as they are in Rams. The problem is that the import wizard still only uses the first 10 characters of these columns. Hence, even though we have imported into a table within a database, we now need to open our imported table exclusively and rename fields that have been changed. Also make sure the fields are at least as big as their corresponding fields in Rams.

Step 4 - Add a PK Field
You must have a field in your import table which corresponds to the PK field for the table you are importing to. For example an 8 character field called ‘account’ if importing customer records. This will be populated with new account number as they are imported.

Also if you have multiple records and you were not supplied with a unique id for the customer you need to create an id for each customer. See the program 'assignuniqueid.prg' in the importsamples folder under in your install folder for an example of how to do this.

Step 5 - Format Data
Often we need to combine fields together or change the names of data. To do this, perform a series of replace statements to achieve the desired results. The best way to do this is to place the replace statements into a program. Then run the program. In this way it's easy to re do the steps if you make a mistake. MAKE SURE YOU MAKE A BACKUP OF THE IMPORTED FILE BEFORE PROCEEDING

Examples
To combine a billing street number to the address_1 field

REPLACE ALL ADDRESS_1 WITH ALLTRIM(ALLTRIM(BLHSE_)+" " + ALLTRIM(ADDRESS_1))

See the file 'transform.prg' in the importsamples folder under in your install folder

Step 6 – Setup a record in Rams with Default Values
The import program can use an existing record as a template for default values to apply to the new records. To set this up create the new record in Rams and keep a record of the PK value for that record.

Step 7 - Create Lookup values in Rams
Whilst importing, the ITRDS will need to assign lookup values such as Billing Type, Area etc. These values must already exist before importing or the import will fail.

Now that you have prepared your import file yuor ready to import the data. Next topic