Import data to a Database (DbLoad)       

Description:
DbLoad allows you to import data back into your database!
In this way you can migrate data between any database:
a) Export from database1 (using db2txt, db2csv or db2xml)
b) Import into database2 (using DbLoad)

You can import data from DBF, XML, CSV or TXT (Tab delimited) format.
To avoid data duplication during the import, DbLoad will use the following logic
for each new record it encounters during the load:

a) Specified Table has a primary key
- Attempt to find the record using the PRIMARY KEY(s)
- If record is found (exists already) update all non primary key fields
- If record is NOT found, add a new record

b) Specified Table has NO primary key - no problem!
- Attempt to find the record using ALL the columns
- If record is found (exists already) ignore duplicate record update
- If record is NOT found, add a new record

In CSV and TXT imports the header defines the order of each column,
if no header is specified the database order is assumed.
XML Imports specify the column in each record, so don't need a header.

As Dbload imports the data it will show its progress each 100 record:
Line 1100: Added 10, Update 2, Error 0, From XXX.txt
Line 1200: Added 10, Update12, Error 0, From XXX.txt ...

To suppress this progress info use the -s (silent) option.

DbLoad offers the following combinations to specify the table and input file:
a) Only specify table (DbLoad -t table), input file is expected in Table.txt
b) Only specify input (Dbload -i tablle.txt), input filename has to be the table (table)
c) Specify both (DbLoad -t table -i tab.xml), input file can have any name and format
d) If neither table not input specified DbLoad stops with a diagnostic message

DbLoad writes all diagnostics to a log file "logs/yyyymmdd/DbLoad.log"
Note that In demo mode up to 500 records can be imported.

Syntax:
usage: dbload [-nsv] [-d{db}] [-t{tab}] [-i{in}]
-d = DATABASE: Specify database input file (default Sample.mdb)
-t = TABLE : Specify Table name or table number (#3 = 3rd table)
-i = INPUT : Specify input filename (default {table}.txt)
-n = NOHEADER: TXT/CSV input has no header showing with field sequence
-s = SILENT : Don't show loading progress messages
-v = VERBOSE : Show more diagnostics

Example:
a) generate sample import data
Db2Txt -d Sample.mdb -t Employees

b) Edit Data for update
Write Employees.txt
-
change "Sales Representative" in Line 2 for Nancy to "Sales Manager" (promotion)
- change EmployeeID in last line from 9 to 10

c) Import into Database
Dbload -d Sample.mdb -t Employees
Line 10: Added 1, Update 8, Error 0, From Employees.txt

DbLoad has updated 8 records and added one record

d) repeat Import into Database
Dbload -d Sample.mdb -t Employees
Line 10: Added 0, Update 9, Error 0, From Employees.txt
Note that now the EmployeeID number 10 is not added again,
but became one of the 9 updates.

Logfile:

If you run this command on Feb 18th, 2001 the logfile would be created in:
logs/20010218/dbload.log
INIT   |11:16:41.613 |====== Opening C:/DbTkXml/logs/20020309/dbload.log ======
DbTkXml|11:16:41.703 |ExecCmd: opendb Sample.mdb
DbTkXml|11:16:41.783 |ExecCmd: table Employees
DbTkXml|11:16:41.833 |ExecCmd: dbload
DbLoad |11:16:41.883 |Table=Employees, Input=Employees.txt,
Dbload |11:16:42.033 |0 added, 9 updated, Table: Employees, Input: Employees.txt

How to speed up the Database Structure Loading Time:
Before any Database operation can take place,
the internal Database Manager is loading all the Table and  Field information.
This can take some time for large Database, so we have devised a caching mechanism to speed up this process:

Step1:
Instruct DBLOAD to build a cached database structure file.
Open the configuration file (DbLoad.ini) and change the following setting to YES:
[SYS]
DBMakeStructCache=YES


Step2:
Create a cached database structure file.
Call DBLOAD normally
After completion a new file - containigthe database structure - should be found:
CacheDbStruct.xml

Step3:
Switch the creation of the database structure file off and the loading from it on:
[SYS]
DBMakeStructCache=NO
DBLoadStructCache=YES
Call DBLOAD normally and it open teh database faster

Repeat Step1 to Spet3 each time the database structure changes!