Thread: Copy HL7 record/file into PostgreSQL Table

Copy HL7 record/file into PostgreSQL Table

From
"Kenneth B. Hill"
Date:
I'm looking for a way to get an HL7 file into a table. My current
thinking is I need to parse the HL7 file to a flat-file and use the
copy command. I would appreciate some guidance on doing this. Thank you.

-Ken Hill

Re: Copy HL7 record/file into PostgreSQL Table

From
Scott Marlowe
Date:
On Fri, 2006-05-12 at 11:41, Kenneth B. Hill wrote:
> I'm looking for a way to get an HL7 file into a table. My current
> thinking is I need to parse the HL7 file to a flat-file and use the
> copy command. I would appreciate some guidance on doing this. Thank you.

I'd love to help out.

Just one thing... What's an HL7 file?

Re: Copy HL7 record/file into PostgreSQL Table

From
"Joshua D. Drake"
Date:
Kenneth B. Hill wrote:
> I'm looking for a way to get an HL7 file into a table. My current
> thinking is I need to parse the HL7 file to a flat-file and use the copy
> command. I would appreciate some guidance on doing this. Thank you.
>

Well I have no idea what a HL7 file is but yes you either parse and use
copy or parse and use insert.

Joshua D. Drake

> -Ken Hill
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>


--

            === The PostgreSQL Company: Command Prompt, Inc. ===
      Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
      Providing the most comprehensive  PostgreSQL solutions since 1997
                     http://www.commandprompt.com/



Re: Copy HL7 record/file into PostgreSQL Table

From
Sean Hamilton
Date:
I'm currently working on an EHR myself and would love to hear a solution
to this.. If youget valuable feedback can you please forward that to me?
and if I hear anything I'll do the same

Thanks,

Sean

Kenneth B. Hill wrote:
> I'm looking for a way to get an HL7 file into a table. My current
> thinking is I need to parse the HL7 file to a flat-file and use the
> copy command. I would appreciate some guidance on doing this. Thank you.
>
> -Ken Hill
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: Copy HL7 record/file into PostgreSQL Table

From
"Kenneth B. Hill"
Date:
An HL7 file is a tagged file used to transfer electronic health
records. The file looks like:

MSH|^~\&|EPIC|EPICADT|SMS|SMSADT|199912271408|CHARRIS|ADT^A04|1817457|
EVN|A04|199912271408|||CHARRIS
PID||0493575^^^2^ID 1|454721||DOE^JOHN^^^^|DOE^JOHN^^^^|19480203|M|
NK1||CONROY^MARI^^^^|SPO||(216)731-4359||EC|||||||||||||||||||||||||||
PV1||O|168 ~219~C~PMA^^^^^^^^^||||277^ALLEN FADZL^BONNIE^^^^||||||||||

You can download a PDF for details here:

http://www.hl7.org/Special/IG/final.pdf

-Ken

On May 12, 2006, at 10:01 AM, Scott Marlowe wrote:

> On Fri, 2006-05-12 at 11:41, Kenneth B. Hill wrote:
>> I'm looking for a way to get an HL7 file into a table. My current
>> thinking is I need to parse the HL7 file to a flat-file and use the
>> copy command. I would appreciate some guidance on doing this.
>> Thank you.
>
> I'd love to help out.
>
> Just one thing... What's an HL7 file?


Re: Copy HL7 record/file into PostgreSQL Table

From
Scott Marlowe
Date:
WOW, that's some huge document, kinda meandering and not sure where it
wants to go.  :)

I've written importers for such stuff in the past.  Looks like a week
long job there.

a couple of points.  Looks like an HL7 file is kind of a broad format.
I'd shoot for whatever subset gets you what you need.

Looks like there are there a lot of dependent tables you'll need to make
it work.  Am I right?

Di you just need to parse this thing into pieces and toss it into a
generic table, or are you gonna have a lot of other processing to make
it work?

On Fri, 2006-05-12 at 12:27, Kenneth B. Hill wrote:
> An HL7 file is a tagged file used to transfer electronic health
> records. The file looks like:
>
> MSH|^~\&|EPIC|EPICADT|SMS|SMSADT|199912271408|CHARRIS|ADT^A04|1817457|
> EVN|A04|199912271408|||CHARRIS
> PID||0493575^^^2^ID 1|454721||DOE^JOHN^^^^|DOE^JOHN^^^^|19480203|M|
> NK1||CONROY^MARI^^^^|SPO||(216)731-4359||EC|||||||||||||||||||||||||||
> PV1||O|168 ~219~C~PMA^^^^^^^^^||||277^ALLEN FADZL^BONNIE^^^^||||||||||
>
> You can download a PDF for details here:
>
> http://www.hl7.org/Special/IG/final.pdf
>
> -Ken
>
> On May 12, 2006, at 10:01 AM, Scott Marlowe wrote:
>
> > On Fri, 2006-05-12 at 11:41, Kenneth B. Hill wrote:
> >> I'm looking for a way to get an HL7 file into a table. My current
> >> thinking is I need to parse the HL7 file to a flat-file and use the
> >> copy command. I would appreciate some guidance on doing this.
> >> Thank you.
> >
> > I'd love to help out.
> >
> > Just one thing... What's an HL7 file?
>

Re: Copy HL7 record/file into PostgreSQL Table

From
"Joshua D. Drake"
Date:
Kenneth B. Hill wrote:
> An HL7 file is a tagged file used to transfer electronic health records.
> The file looks like:
>
> MSH|^~\&|EPIC|EPICADT|SMS|SMSADT|199912271408|CHARRIS|ADT^A04|1817457|
> EVN|A04|199912271408|||CHARRIS
> PID||0493575^^^2^ID 1|454721||DOE^JOHN^^^^|DOE^JOHN^^^^|19480203|M|
> NK1||CONROY^MARI^^^^|SPO||(216)731-4359||EC|||||||||||||||||||||||||||
> PV1||O|168 ~219~C~PMA^^^^^^^^^||||277^ALLEN FADZL^BONNIE^^^^||||||||||
>

You *might* actually not have to parse this... just use | as your
delimiter with copy.

SIncerely,

Joshua D. Drake

> You can download a PDF for details here:
>
> http://www.hl7.org/Special/IG/final.pdf
>
> -Ken
>
> On May 12, 2006, at 10:01 AM, Scott Marlowe wrote:
>
>> On Fri, 2006-05-12 at 11:41, Kenneth B. Hill wrote:
>>> I'm looking for a way to get an HL7 file into a table. My current
>>> thinking is I need to parse the HL7 file to a flat-file and use the
>>> copy command. I would appreciate some guidance on doing this. Thank you.
>>
>> I'd love to help out.
>>
>> Just one thing... What's an HL7 file?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--

            === The PostgreSQL Company: Command Prompt, Inc. ===
      Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
      Providing the most comprehensive  PostgreSQL solutions since 1997
                     http://www.commandprompt.com/



Re: Copy HL7 record/file into PostgreSQL Table

From
Hélder M. Vieira
Date:
HL7 is a complex standard, because the reality it tries to describe is
complex. It was designed (and is still being designed) to allow the transfer
of all kinds of information related with health institutions.
So, depending on the specific institutions and purposes involved, either the
HL7 messages flowing between them contain a small and simple set of useful
information, and therefore might be parsed on the fly, or they contain a
full load of technical, administrative and financial information and
therefore demand a slightly more complex parser and a lot of related tables
and business rules.
I guess it would be important to Sean to get more specific information on
the task, because the parser will probably be the least complicated part of
it.


Hélder M. Vieira



----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Kenneth B. Hill" <ken@scottshill.com>
Cc: "Scott Marlowe" <smarlowe@g2switchworks.com>;
<pgsql-admin@postgresql.org>
Sent: Friday, May 12, 2006 7:04 PM
Subject: Re: [ADMIN] Copy HL7 record/file into PostgreSQL Table


> Kenneth B. Hill wrote:
>> An HL7 file is a tagged file used to transfer electronic health records.
>> The file looks like:
>>
>> MSH|^~\&|EPIC|EPICADT|SMS|SMSADT|199912271408|CHARRIS|ADT^A04|1817457|
>> EVN|A04|199912271408|||CHARRIS
>> PID||0493575^^^2^ID 1|454721||DOE^JOHN^^^^|DOE^JOHN^^^^|19480203|M|
>> NK1||CONROY^MARI^^^^|SPO||(216)731-4359||EC|||||||||||||||||||||||||||
>> PV1||O|168 ~219~C~PMA^^^^^^^^^||||277^ALLEN FADZL^BONNIE^^^^||||||||||
>>
>
> You *might* actually not have to parse this... just use | as your
> delimiter with copy.



Re: Copy HL7 record/file into PostgreSQL Table

From
"Jim C. Nasby"
Date:
On Fri, May 12, 2006 at 01:14:18PM -0400, Sean Hamilton wrote:
> I'm currently working on an EHR myself and would love to hear a solution
> to this.. If youget valuable feedback can you please forward that to me?
> and if I hear anything I'll do the same

Given how complex HL7 apparently is (someone up-thread was estimating a
week to write a parser) you might want to go with more of a pre-canned
solution rather than trying to roll your own. I know that folks I work
with that do consulting on our data integration products know a lot
about the health industry, so they might be able to turn something
around very quickly, though it would mean buying one of our products.
I'm sure there's also other experts in the field that could write
something that would work directly with PostgreSQL.

There's also a number of HL7 related projects on sourceforge
(http://lnk.nu/sourceforge.net/9fj/); one of them might prove useful.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Copy HL7 record/file into PostgreSQL Table

From
Scott Marlowe
Date:
On Mon, 2006-05-15 at 09:59, Jim C. Nasby wrote:
> On Fri, May 12, 2006 at 01:14:18PM -0400, Sean Hamilton wrote:
> > I'm currently working on an EHR myself and would love to hear a solution
> > to this.. If youget valuable feedback can you please forward that to me?
> > and if I hear anything I'll do the same
>
> Given how complex HL7 apparently is (someone up-thread was estimating a
> week to write a parser) you might want to go with more of a pre-canned
> solution rather than trying to roll your own.

The week I was estimating was for creating what appear to be all the
ancillary tables listed in that spec, and testing to make sure you've
got the schema all right.  Just importing the data that was presented
looked fairly easy.  It was the validating it that looked hard...

Just clarifying.