Universal Excel Importer v1.2

Import data from Excel to any table in just 3 clicks – now possible !

Universal Excel Importer allows to import data directly from Excel file to any table. Select Excel file, then select worksheet, then select fields and your’e done !

UEI has many extra options available. Most of the options are selectable by user (1), or settable inside Excel file (2), or can be predefined as a parameter passed to import function(3).

The options are:

- selection whether to import new records, update existing ones, or do both actions (1)(3)
- predefinition destination table number (1)(2)(3)
- individual selection of fields to import (1)(2)
- define mapping from Excel column to Table Field (1)(2)
- put a filter to column numbers which user can choose to import (3)
- individually select fields on which VALIDATE will be launched (1)(2)(3)
- selection whether to use OnInsert/OnValidate triggers ( 1)(3)
- matching of fields by Field No, Field Name or Field Caption in working language (3)

User can be also prevented from changing any of above option.

UEI does not modify any standard table.

UEI assumes that header is placed in first non-empty row. It skips empty rows between data, and skips non empty rows if the data is placed in columns not selected to import

UEI by default tries to match names in Excel header row to field captions, so easiest way to prepare Excel template to import is:

1. open destination table (directly or on some form)
2. select one row
3. copy and paste to Excel
4. delete copied data but leave header row
5. delete unnecessary columns or rename names in header
6. fill desired data in appropriate columns.

All fields which are parts of primary key of destination table have to be included in Excel.

To define destination table inside Excel put table:tableNo in A1 cell (no spaces!). That case Importer starts to search for header from row 2.

To define individually which on columns launch VALIDATE simply bold the header field, and put 128 in option passed to Import function. By default all fields are VALIDATEd

I’ve learned a lot from mibuso and its users and this is my way to thank all of you.

Hope you enjoy this tool. If you have any questions, or suggestion what to improve, or find any errors, please write to sguzekSGUZEK@onetPL.pl (remove all CAPITALS from email address)

New in version 1.2:

- No more headache with country-specific number formatting.

- Auto-Increment of fields. Very useful when importing journals. Instead of filling entire "Line No." column with consecutive numbers just include text 'incr:Line No.=00001' in one of the first cell in your Excel file

- Constant values of fields. Very usefull when importing journals. Instead of filling entire "Journal Template" column with "GENERAL" word just include text 'const:Journal Template=GENERAL' in one of the first cell in your Excel file

- new field mapping interface.

- more parameters from Excel are recognized

- short manual included

Valid parameters in version 1.2 are:

table:destination_table_no
TableHeader:Row_no_with_column_headers
ImportAll
SkipTriggers
Const:Destination_Field=Constant_Value
Incr:Destination_Field=Counter_starting_value

WARNING - Importer default settings, parameter keywords and header text formatting dependence are slightly changed from the previous version. Consult included manual

Version 1.1:

- error in Update mode removed (occurring in some cases when updating records with option data type fields in primary key)

- default insertion mode changed - now importing to journals is possible in only one step

- added Delayed Insert option (1)(3) – record will not be inserted until all fields are filled from Excel.

- added possibility of specifying where the data starts inside Excel file (2)(3) - put header:headerRowNo in configuration row and Importer will skip all rows until selected row number, and then will start to search for table header from here. Useful for putting some comments, or any oth er non imported data, at the beginning of Excel file.

Configuration row – it is first non-empty row in Excel file containing at least one parameter definition in any cell. If found Importer will search for table header from next row or from row number specified in parameters

Valid parameters in version 1.1 are:
header:headerRowNo
table:tableNo