Thread: Copy HL7 record/file into PostgreSQL Table
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
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?
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/
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
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?
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? >
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/
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.
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
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.