topic Re: ETL Text File in Connectivity & Data Prep

ETL Text File

tfrazerheartlan — Tue, 28 Dec 2021 20:21:57 GMT

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?

Re: ETL Text File

tush — Wed, 20 Mar 2019 06:14:31 GMT

Hi,

can you please provide sample text file

Thanks

Tushar

Re: ETL Text File

tfrazerheartlan — Wed, 20 Mar 2019 14:37:51 GMT

For some reason .txt is not a valid file type to attach here?

Re: ETL Text File

tfrazerheartlan — Wed, 20 Mar 2019 14:40:04 GMT

Re: ETL Text File

tfrazerheartlan — Wed, 20 Mar 2019 14:47:29 GMT

Attaching zip file

Re: ETL Text File

tfrazerheartlan — Fri, 17 May 2019 14:45:25 GMT

Was just curious if you ever had a chance to check out that attached file?

Re: ETL Text File

marcus_sommer — Fri, 17 May 2019 15:42:09 GMT

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:

Issue-with-loading-special-character

- Marcus