3 Replies Latest reply: Jun 26, 2018 9:52 AM by Felip Drechsler RSS

    How to deal with txt file data

    Alan Farrell

      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

        • Re: How to deal with txt file data
          Felip Drechsler

          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.

          • Re: How to deal with txt file data
            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