I have a text file with the below data format. I'm trying to find the best way to extract the Number/Name/Amount that I have highlighted below. Data for each company is between the hyphens.
Has anyone had any success on extracting from a file similar to this data format?
I think it looked more difficult as it is. Important ist just to find regularities within the records. If the data-structure is always the same and each entry has the same number of records you could just use recno() to identify/filter your needed records. If they are different in some way you need to search within the content of a record.
In your case I think I would load the file with fix length (is an option within the file-wizard) and apply a wildmatch as filter. The second step could be a cleaning of the multiple spaces with a mapping, the third picked the appropriate values with a subfield, fourth collect the various values with an interrecord-function like previous within a single record and the fifth step removed the invalid records with a flag field which is created/maintained through the load-chain.
load * where FLAG = true();
load *, previous(F2) as F3;
load F, subfield(F, ' ', NUMBER) as F1, subfield(F, ' ', NUMBER) as F2;
load mapsubstring('map', F) as F;
load @1:n as F from Source where wildmatch(@1:n, '*DATE Opened*', '*TOTAL:*');
A quite similar example which used such mapping on spaces could you find here: