Load from multiple text files (dynamic)

[#1]

From my payroll system, every two weeks a text file is created, which I need to load into qlikview

The text files are all stored in the same hard drive folder (C:\textfiles\) and are named

Payroll_week13-14.txt
Payroll_week15-16.txt

The format of the files are completely the same, and they all need to be concatenated into the same Qlikview table.

How do I create a load statement that dynamically loads ALL text files, no matter if there are 3 or 300 in the folder?
I also need a datafield in the qlikview table that contains the source file name.

Please help. [:D]

regards, Jesper

[sparur · Accepted solution]

Hello, Jesper.

PayrollTable:

LOAD

*,

mid(filename( ), 13, 5) AS WeekNumberData

FROM Payroll_week*.txt (....)

[#3]

Hi Jesper,

somthing like this should do the trick:

Directory C:\MyData\TXT;

For each txtFile in filelist ('*.txt')

MyTable:
LOAD

Field1,

Field2,
'$(txtFile)' as Name,
FileName('$(txtFile)') as FileName,
FileSize('$(txtFile)') as Size,
FileTime( '$(txtFile)') as FileTime
From '$(txtFile)' ;

NEXT txtFile;

Good luck!

Rainer

[#4]

Thx guys, i'll give it a go!

jesper

[#5]

Hi there..

i have a problem with the FROM syntax.

I can doFROM Payroll_*.txt if my filename is Payroll_xyz.txt

it wont work if the filename is Payroll_xyz_2011-08-05.txt.

is this normal? or something is wrong?

LOAD

*,

mid(filename( ), 13, 5) AS WeekNumberData

FROM Payroll_week*.txt (....)