Qlik Community

Qlik Deployment Framework

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master II
Master 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
Highlighted
Partner
Partner

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Partner
Partner

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.