topic Load from multiple text files (dynamic) in QlikView

Load from multiple text files (dynamic)

Fri, 04 Jun 2010 13:22:11 GMT

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

Load from multiple text files (dynamic)

sparur — Fri, 04 Jun 2010 13:36:37 GMT

Hello, Jesper.

PayrollTable:

LOAD

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

FROM Payroll_week*.txt (....)

Load from multiple text files (dynamic)

Fri, 04 Jun 2010 13:37:56 GMT

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

Load from multiple text files (dynamic)

Tue, 08 Jun 2010 08:02:31 GMT

Thx guys, i'll give it a go!

jesper

Load from multiple text files (dynamic)

Fri, 05 Aug 2011 05:55:45 GMT

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 (....)