Re: [Gnumed-devel] Transferring/importing/bootstrapping from legacy systems

[Karsten Hilbert]

On Fri, Jan 20, 2006 at 01:06:48AM -0800, Jim Busser wrote:

> To better help any reading I will want to do, could I ask some 
> overview of options or best methods of getting data from a legacy 
> system into gnumed?
Sure, that's what GNUmed is for, after all.

> Presently, I maintain some patient information in a program used for 
> billing, with the data stored in FoxBASE .dbf (dBase-compatible) 
> tables. It is organized loosely as follows:
> 
> - patient table fields including
>    surname + given names, date of birth, sex,

> health insurance num, demographics foreign key
> (administrative identifier) of other providers (family &
> referral doctors)
These would go into dem.lnk_identity2ext_id.

> - link table (patients' diagnoses)
Can you be more specific ?

> - icd9 table (into which I had created some extra, "custom" codes)
Official icd9 should go into tables in the "reference" part.
Your "custom" codes should likely go into
clin.coded_narrative which holds custom terms for
official codes. Any officially named codes that you
use would be shadowed in clin.coded_narrative as well.

> I am not sure whether there exists for postgres any tools that permit 
> it to "read" from the native .dbf form in which my data is stored,
Likely there are importers which read DBF and write to a
PostgreSQL database.

> or 
> whether that data would first need to be exported into tab, comma or 
> standard delimited format.
This might be useful depending on circumstances.

> But even with my data converted into a readable format, I did wonder 
> what kind of scripting I would need to get someone (e.g. local) to do 
> up for me.
Well, it doesn't matter too much. The two standard
approaches are to either write a script which takes a data
format and generates SQL statements which are sent to the
database or to use a generic import tool (say CSV import)
and load the data into staging tables. Then use either
standard SQL or a script to massage the data from the
staging tables into the real ones. I would suggest this:

- create a staging schema (say, "jb_staging")
- "somehow" import the data into staging tables
- use a script to read the data from the staging area,
  possibly do some transformations and write it to the real
  tables

> I noticed that the bootstrap files seem to specify within them both 
> the fields and the actual values of the data,
Those are hardcoded SQL statements, not a particularly
practical way for dynamic data import.

> Also, the first time this is done on an "empty" gnumed database, all 
> records that survive the data requirements/constraints will get 
> appended. But if any records are rejected (fail the constraints), is 
> the entire file import rejected?
That entirely depends on how you do it. The transformer
script (staging -> real) sort of needs to understand what it
takes to get the data properly imported. That requires
coding - some deduction may be possible as Syan points out
but that's cutting edge and quite advanced stuff.

> And if "no" (meaning the valid 
> subset of records *is* imported) is there any automatic or standard 
> practice that captures the records that were rejected?
automatic: no, standard: yes (use a log file)

> Has anything like this already been programmed / submitted into the CVS?
No. There isn't really a generic way to properly import
arbitrary data (into any database). There can only be an API
importers agree on using - which makes them pluggable.
Still, the guts of an importer/transformer must be written
and spelled out programmatically one way or another.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346