Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tfrazerheartlan
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

Labels (1)
6 Replies
tush
Creator II
Creator II

Hi,

     can you please provide sample text file

 

Thanks

Tushar

tfrazerheartlan
Contributor III
Contributor III
Author

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

tfrazerheartlan
Contributor III
Contributor III
Author

Qlik_Text_ETL_Error.png

tfrazerheartlan
Contributor III
Contributor III
Author

Attaching zip file

tfrazerheartlan
Contributor III
Contributor III
Author

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

marcus_sommer

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