Parsing question for odd data set

mishmish3000 · Post by **mishmish3000** » 28 Oct 2011, 18:41

Hello! Hey, I'll be working soon with some data that's sent to us in an odd manner (sort of like XML but not quite). I've been wondering about a way to parse it out and attached a PDF with a simple example. Any ideas would be appreciated. I need to get this into an Access database but am not sure of the best way to do it.
Thanks
MishMish3000

agibsonsw · Post by **agibsonsw** » 28 Oct 2011, 19:21

A little odd that you would need to do this but, hey!

I would consider opening it in a text editor, removing/replacing all the opening tags < with "" (or nothing), then replacing all the closing ones > with a different character - one that won't appear in the file itself, such as a pipe |.
Save the file as a text file and import it (External Data tab, Text file). Don't specify the first row as field names, so that each column will be named Field1, Field2, etc.
You may be able to save the import steps to re-use later.

I tried importing it with just the closing tags > in place, but Access (2010) tries, and fails, to parse it as XML.

Andy.

Added: Mind you, I was assuming that you're just talking about one row of data? Otherwise it becomes a different question. You would need to replace the stuff that tells you that you've reached the end of one record, perhaps <RecordX>... <RecordX> with end-of-line characters.

mishmish3000 · Post by **mishmish3000** » 28 Oct 2011, 19:24

OK, I'll try that! Sounds interesting. This is a weird, weird data set. :P

Post by **HansV** » 28 Oct 2011, 19:31

Have you tried importing the actual file as XML? (External data tab of the ribbon, XML file in the Import & Link group)

mishmish3000 · Post by **mishmish3000** » 28 Oct 2011, 19:36

No, not yet. In fact, I haven't even gotten an actual copy of the file to play with. I just had my manager show it to me, and started wondering how in the world would I get it into Access! I can try, though. I don't think it's XML... it's in a format called HL7 and is a medical record. It's kinda weird. Thanks for the ideas, though! I'll try to get a copy next week and play with it. Have a great weekend, everyone! :)

Post by **HansV** » 28 Oct 2011, 19:43

OK, an HL7 file is indeed (very) different from an XML file. Data in an HL7 file are not stored in a table format.
To convert it to one or more Access tables, you will need to have an additional file that describes the structure of the HL7 file. Without that, it is impossible to interpret its meaning.

mishmish3000 · Post by **mishmish3000** » 28 Oct 2011, 19:50

Ah, yes. It's like a definition file or something. The manager talked about that, too. I just got promoted to a new position where I'll be helping to decipher HL7 files, electronic lab reports, things like that. So she was showing me some of the files they're dealing with now. We'd like to be able to import them into database files so we can more easily make reports for the staff to use. Each record seemed to be in one cell of the file, and it has to be parsed out into fields. I'll get a copy of the file and its definition file and play around with it hopefully next week. The official transition takes place on the 13th of Nov. so I have some time to learn. :)

Post by **HansV** » 28 Oct 2011, 19:55

Conversion will probably require programming. The way it's often done is to parse the HL7 records into a "flat" database table that just contains a bunch of text fields Field1, Field2, Field3 etc., then run a program to move the contents of this "flat" table into the relational structure, using the definition file to interpret the meaning of each field.

agibsonsw · Post by **agibsonsw** » 28 Oct 2011, 20:20

Came across this site that offers a trial download that can view HL7 and export them as XML.

Whether the XML is in a format that Access can handle would be the next question. If not, then the next step might be to find an XML Schema (that can translate the XML for Access) or an XML Transform (XSLT) that can transform XML files from one format to another; that is, to a format that Access can more readily interpret.

But take what I suggest with a large pinch of salt as it's been ages since I explored XML. Andy.

Post by **HansV** » 29 Oct 2011, 09:54

Congratulations on your promotion, by the way, MishMish3000!

mishmish3000 · Post by **mishmish3000** » 04 Nov 2011, 14:59

Thank you for the links and for the congratulations! I'm very excited about my new job. Plus the $$$ went up too so that's even better! LOL

Eileen's Lounge

Parsing question for odd data set

Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set

Re: Parsing question for odd data set