BS Toolkit – Universal Importer

This will import data from an Excel file into almost any Opera table or tables..

For each Excel file to be imported there must be another Excel file with the ‘Rules’ for the import.

The input data should be in rows, each row is potentially a field in an Opera table.

The Rules file can also update associated tables – e.g an import into STRAN can also update (or create) the account record in SNAME.

A simple rules file might look like this

Alias	Field	Expresion	Add?	Edit?	Key	Tag

SNAME	sn_account	padr(in01,8)	Y	Y	m.sn_account	accounts
	sn_name	alltrim(in02)
	etc.

Etc.

This is interpreted as

SNAME the alias(table) to be updated

Sn_account - the field to be updated

padr(in01,8) - the data to go into sn-account – IN01 is column 1(A) of the input data
(any valid VFP function which returns the right type of data may be used)

Y - new accounts can be created (any valid VFP function which returns Y/N may be used)

Y - existing records can be edited (any valid VFP function which returns Y/N may be used)

m.sn_account - the key for SNAME; the m. is obligatory

accounts - the tag in sname.cdx which matches the key you defined (should be unique)

The alias need not be repeated on successive lines.

Here’s an example with two tables being updated. The Input file will have a row for each record to be created

STRAN	st_account	padr(in01,8)
	St_trdate	ctod(alltrim(in02))
	St_trref	padr(in03,10)	Y	N	m.st_account+dtos(m.st_trdate)+m.st_trref	stran1
	St_trvalue	val(alltrim(in04))
	St_etc etc

SNAME	sn_account	m.st_account)	Y	Y	m.sn_account	accounts
	Sn_name	alltrim(in05)
	Sn_currbal	sname.sn_currbal+m.st_trvalue

N.B. This approach requires that every line in the input contains every required columns. Thus the Account name in column5 must appear on every line. of the data file.

Columns D, E F and G of the Rules control the addition and editing of each record.

Column F contains the expression for the unique key to the table.. Column G names the tag of the unique key.

Column D and E contain Y or N or an expression evaluating to Y or N to indicate if records can be added or edited respectively.

The entries in columns D through G should appear on the line where the last element of the unique key has been defined.

The fields which make up the key should be imported before the line with the definition of the key, e.g in the above example st_account, st_trdate and st_trref must exist for the key to be constructed

Field names in expressions should be prefixed with m. to distinguish between the values in memory and the empty values in a new record, or existing values in a record which is to be updated.

It is possible to need more than one rules file. It is possible to use the same Rules file for a number of Input files

Cell A1 of the data may contain the name of the rules file to be used with this data. This simplifies operational use as the operator does not have to select a ruls file at run time.

The Rules file has a header of two rows (If you have a blank line(s) between the header and these entries the blank lines are ignored).

The top line is just a header and its contents are irrelevant, the next non blank line should be as follows

Alias

Field

Expression

etc

– column

headings

for

your

convenience

ignored

importer

Criteria

Passes

where:-

Cell C2 - Crtiteria is an expression which indidcates which data rows are to be imported;

This expression can include IN00 which is the row number – thus IN00>2 will igore the first two rows of input

IN00>2 and !empty(in01) will skip rows 1 and 2 and any row which has an empty cell A

Cell D2 Passes the number of times the rules are to be operated in each line which meets the criteria – can be blank (one pass) or a number. Variable bs1_pass contains the pass number, this iif(bs1_pass1=1,x,y).

Cell G2 X is The column in the data where the importer will place it’s comments (success/fail)

BS importer has built functions to handle Parm and Header files.

bs1_nparm(my journal) – returns the next nominal document number and increments nparm on first use with my journal

bs1_iparm(legacy_order_no) - returns the next SOP Order number and increments iparm

bs1_dparm(legacy_order_no) - returns next PO Number and increments DPARM

bs1_atype(l_new_entry,entry_type) - returns next Cash book Entry Number

Other similar functions will be supplied as the need arises.

There are 10 public variables (bs1_userspace0 to bs1_userspace9) where you can put values from one pass of the rules for use on a later pass (or input line)

e.g.

	Bs1_userspace0	Iif(!empty(IN05),in05,m.bs1_userspace0)	< remember in05 if not empty
XTRAN	XX_ACCOUNT	m.bs1_userspace0	<use the in05 you remembered

Folders

BS Importer has default locations (paths) for the Input and Rules Files. These are defined in the Settings file (BS Toolkit - /BS Settings) at :=L_bs1_ui.

The defaults are O:\Input and O:\Rules. You may need to change these to suit your installation.

BS Importer will try to create these folders if they don’t exist.

You are recommended to make use absolute paths – e.g O:\input rather that .\input.