How to deal with txt file data

[rustyfishbones]

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

[felipedl]

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);

Attached the used QVW.

Felipe.

[marcus_sommer]

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

[felipedl]

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.