Last row of text from 1000 files

[olivetwist]

I have a directory where 1000s of text files are saved. I am trying to create a table that shows the file names and the last row of text from each file. I have currently created a process that can do this by looping though each and every file but this is a slow process.

FOR Each vXML IN FileList ('Directory\*.xml')

TEST:

LOAD

  FirstSortedValue([@1:30],-RowNo()) AS TEXT,

  FileName() AS FILE

FROM [$(vXML)] (fix, codepage is 1252);

NEXT vXML;

EXIT Script;

Does anyone have suggestions for a more efficient method?

[marcus_sommer]

I think you will need an incremental approach with storing your results in a qvd and checking the next time if this file was already loaded and if yes the skip it. This should speed up your task a lot then the reading per filelist is quite fast and your xml-loadings will be the slower part.

- Marcus

[swuehl]

I assume you need to recheck every file for appended lines?

I would probably create an external script that iterate over all files in that folderand writes the last line in a output file.

(e.g. using Power Shell or what ever you are familiar with)

Use EXECUTE statement in Qv to execute that script.

Then just read in the created output file.

[Clever_Anjos]

Complementing swuehl‌

This powershell command will list last line of 'c:\scripts\test.txt'

Get-Content c:\scripts\test.txt | Select-Object -last 1

[swuehl]

I would probably go for the tail parameter, and if you want to iterate over all files, something like

PS C:\>dir .\*.txt | ForEach {Get-Content $_ -Tail 1}

[Clever_Anjos]

I was not aware of -Tail parameter

[olivetwist]

Unfortunately, they have prevented QV from running external scripts in my company. Oh well, resources will be hogged. I appreciate the replies and if anyone can prove one as the "correct" answer let me know and i will mark it.

[marcus_sommer]

Have you tried it with an incremental approach? Maybe something like this:

TEST:

Load TEXT, FILE From YourQVD.qvd (qvd);

FOR Each vXML IN FileList ('Directory\*.xml')

     if fieldindex('FILE', '$(vXML)') = 0 then

          concatenate(TEST)

          LOAD FirstSortedValue([@1:30],-RowNo()) AS TEXT, FileName() AS FILE

          FROM [$(vXML)] (fix, codepage is 1252);

      end if

NEXT vXML;

store TEST into YourQVD.qvd (qvd);

EXIT Script;

- Marcus

[olivetwist]

I did think about but it wouldn't work in this situation because the forms go back and forth between individuals. They make changes and it may not be corrupt today but the next person edits and saves causing the corruption.

After reading your post, I revisited this and will consider trying something related to Filetime(). Maybe if Today()-Filetime()>=8 instead of just looking for the filename. That way I can check only those changed in the previous week. I will post my results.