Qlik Community

Qlik Deployment Framework

This group hosts information related to the Qlik Deployment Framework (QDF). Providing best practices, libraries and utilities that facilitate the recommended setup and management of QlikView or Qlik Sense environments. An understanding of the Qlik platform is recommended before joining this group. Download QDF and documentation here: https://github.com/QlikDeploymentFramework/Qlik-Deployment-Framework/releases

Highlighted
rustyfishbones
Honored Contributor II

How to deal with txt file data

Hi All,

I have hundreds of text file with a format like below

Purge (Yes/No):                             yes

Day Count to Retain (INT):        180

Purge Limit (INT):                          3

Days Deleted:                                 3

Export Data to file (Yes/no):     no

Export Path (CHAR):                     D:\Index\auditExportDir

E-mail Confirmation (Yes/No): yes

E-mail Address(char):                  support@support.ie

Audit Records Deleted:              33960

Duration:                                          00:02:32

What is the best way to read this data, use RowNo() or RecNo()

Thanks

3 Replies
Partner
Partner

Re: How to deal with txt file data

Hi Alan,


Depends on what you need with those files, the bellow script loads all the txt files in the folder "C:\test", getting the field name and values by the ":" delimiter.

for each file in FileList('C:\test\*.txt')

textFile:

Load

'$(file)' as [File Name],

trim(SubField(Line,':',1)) as [Field Name],

trim(SubField(Line,':',2)) as [Field Value];

LOAD @1 as Line

FROM

[$(file)]

(txt, codepage is 1252, no labels, delimiter is \x4, msq);

sample.png

Attached the used QVW.

Felipe.

MVP & Luminary
MVP & Luminary

Re: How to deal with txt file data

I assume that you want to transform the rows into columns. For this you could use the suggestion from Felip as a peparing step for a The Generic Load. Another way could be to identify each record and field with something like this:

load *, ceil(rowno() / 10) as RecordID, mod(rowno(), 10) + 1 as FieldID from ...

// 10 is the number of values in your example above

and using them to filter and join the data or using some interrecord-functions like Peek() or Previous() ? to build your needed records.

- Marcus

Partner
Partner

Re: How to deal with txt file data

Yeah, would assume that he'd want the transpose of the table, but frankly I really dislike the Generic load for transposing.

Could be an easier way implemented to reverse a table.