Getting Things Done – with Dovetail ArchiveManager (DIET)
Sam Tyson
October 24, 2008

I am currently working on a project where I need to take a database from empty to usable with a script file. I am at the point where I need to import data files, and the data files I need to import don’t exist. The customer has the data, and luckily they have a tool to create nice, tidy data files for me – Dovetail Software’s Data Import Export Tool (DIET).

 

The challenge for me is to make it easy for them to use DIET, so I can get the data files that I need.

 

DIET is the perfect tool for the job, because it is designed to do exactly what I need. I need data from three related tables in the database for this file, so I created a directives file to specifically control what information to extract.

 

User-Defined Lists are one of the most challenging data structures within the database, but the directives file was pretty easy to build (with a little help from Gary Sherman). Here is the end result, which I will explain below:

 

 

At the top level,  I want to export data from the hgbst_lst table, and then also export any related data from the hgbst_show and hgbst_elm tables at the lower levels. Since I want to be able to extract this data from one database and import it into a different database, I avoided using any unique record identifiers (objid’s). Using “title” as the unique identifier for the hgbst_lst table prevents multiple copies from being imported into the database.

 

Each lower level is reached by the “TO <table> THROUGH <relation>” phrases, and that traverses through the parent-child relations that make up the user-defined list structure.

 

The batch file that I set up and used for this data extraction is the other key piece of the puzzle. Here it is, and again the explanation follows:

 

 

 

 

This batch file is built for an Oracle database. The command line options that were key were:

  • -directive hgbst.dir – contains the directives file described above
  • -archive – copies the data from the database but does not remove it
  • -export hgbst.dat – specifies the file where the output data is written
  • -where “title IN (‘Msg Reason’, ‘Recipient’, ‘Reimbursement_addr’)” – controls which lists are exported

 

The other command line options control the database connection. Each one has to have a valid value, but otherwise they are self-explanatory.

 

Here is a sample of the data that gets created:

 

 

Hopefully this will help get the data that you need out of your database – DIET can definitely do the job.

 

Leave a Comment

International: +1 (512) 610-5400
Toll Free: 1 (800) 684-2055