Re: Extract Data from Flat Text (350 Views)
Reply
Valued Contributor
rmueller58
Posts: 851
Registered: ‎02-19-2001
Message 1 of 7 (420 Views)

Extract Data from Flat Text

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:

Capture.PNG

 

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.

 

 

Acclaimed Contributor
Dennis Handly
Posts: 25,187
Registered: ‎03-06-2006
Message 2 of 7 (403 Views)

Re: Extract data from flat text

[ Edited ]

>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.  ;-)

 

Honored Contributor
Bill Hassell
Posts: 14,210
Registered: ‎05-29-2000
Message 3 of 7 (389 Views)

Re: Extract Data from Flat Text

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?

 

 

 

 

Valued Contributor
rmueller58
Posts: 851
Registered: ‎02-19-2001
Message 4 of 7 (383 Views)

Re: Extract Data from Flat Text

[ Edited ]

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)

 

Acclaimed Contributor
Dennis Handly
Posts: 25,187
Registered: ‎03-06-2006
Message 5 of 7 (369 Views)

Re: Extract data from flat text

>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?

 

Frequent Advisor
RJHall
Posts: 33
Registered: ‎05-30-2013
Message 6 of 7 (355 Views)

Re: Extract Data from Flat Text

[ Edited ]

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...

Valued Contributor
rmueller58
Posts: 851
Registered: ‎02-19-2001
Message 7 of 7 (350 Views)

Re: Extract Data from Flat Text

Thanks all.

I think i have it figured out now.


Hope all you guys in Colorado are OK!!

 

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.