topic Re: Extract Data from Flat Text in Operating System - HP-UX

Extract Data from Flat Text

rmueller58 — Fri, 06 Sep 2013 16:11:43 GMT

Been a while since i've been on the ITRC, I am posing a question of the script Gurus.

I have been giving some DATA, originally screen scraps from a client's web location.

There is ONE page per record, each record is formatted as such:

I would like to pull out the "FIELDS" into a CSV, so I can populate a DB table with them.

using Awk or Perl, I am looking for suggestions on how to process each line as a "field" and each document as a record.

Suggestions on Extraction and conversion deeply appreciated.

Re: Extract data from flat text

Dennis Handly — Sat, 07 Sep 2013 07:49:05 GMT

>how to process each line as a "field" and each document as a record.

The first line seems to have 3 fields, is that a special case?

Also, are those underscores actually there or are they spaces?

Otherwise, each field seems to be separated by "|". And I suppose the continuation lines have leading spaces.

And blank lines and lines with underscores should be ignored.

Do you have some examples of data, instead of a picture?

(While a picture may be worth a 1000 words but in this case it's worthless for scripting since I can't grep it.

Unless OCR works. ;-)

Re: Extract Data from Flat Text

Bill Hassell — Tue, 10 Sep 2013 17:34:36 GMT

There are a *lot* of questions to answer about the data.

Is the data actually plain ASCII or is this an HTML snapshot of the web page?

You say 'record'...is this one text file per screen scrape or are the scrapes hooked together in a long file?

If part of a large file, how do you determine where the each screen starts and stops?

Are there tabs occupying the white space or just spaces?

Does the text have special characters imbedded that are not visible?

Re: Extract Data from Flat Text

rmueller58 — Wed, 11 Sep 2013 14:08:08 GMT

Bill and Dennis,

I actually ran HTML through "html2text" then wrote a scriptlet to strip out some other stuff to reduce it to the core

the original piece was actually HTML.

what is seen is ASCII/Text

Dennis, the '_" is actually underscore, I have tried to field strip with awk -F'_' but it doesn't handle it correctly..

i think this is how the html2text parses spaces..

i put it in a hex editor to see what character it was it actually x\5F (underscore.)

the HTML code is in fact \x20 or spaces.

(see attached html)

Re: Extract data from flat text

Dennis Handly — Wed, 11 Sep 2013 19:09:53 GMT

>I actually ran HTML through "html2text"

So that's what you want to process?

>I have tried to field strip with awk -F'_' but it doesn't handle it correctly.

Your field separator appears to be "|", -F"|". And use gsub to convert "_" back to space.

> (see attached html)

(That's missing. You must have a suffix like .txt.)

Can you also provide some example html2text output so we can see what needs to be processed?

Re: Extract Data from Flat Text

RJHall — Mon, 16 Sep 2013 20:28:41 GMT

In Perl I'd probably cycle through each line, checking for pipe symbols (if ( $line =~ /\|/), then doing a split into an array based on the '|' character (@data = split('|',$line);). The individual array elements with data can be globally converted from underscores to spaces ($data[$n] =~ s/_/ /g;), as necessary. You'll then have to put in the logic for merging multiple lines together where appropriate.

Hope this made some sense...

Re: Extract Data from Flat Text

rmueller58 — Mon, 16 Sep 2013 20:27:25 GMT

Thanks all.

I think i have it figured out now.

Hope all you guys in Colorado are OK!!