Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

ETL Text File

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?

Qlik_Text_ETL.png

6 Replies
Highlighted
Creator II
Creator II

Hi,

     can you please provide sample text file

 

Thanks

Tushar

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

Qlik_Text_ETL_Error.png

Highlighted
Contributor III
Contributor III

Attaching zip file

Highlighted
Contributor III
Contributor III

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

Highlighted
MVP & Luminary
MVP & Luminary

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