Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

For Each loop for finding text file names

Hi all,

I have a scenario whereby the transactions is exported to a text file on a daily basis due to the volume of transaction lines.

I am trying to create a for each loop whereby I can find all the text files and load these files into the transaction qvd.

The file name format is tran_dd_mm_yyyy.txt. So today's file will be tran_06_09_2011.txt

I was thinking of using the filebasename to determine the filename to load but my file name is dynamic, is their anybody who can maybe help.

Regards

Jimmy

1 Solution

Accepted Solutions
spividori
Valued Contributor

For Each loop for finding text file names

Hi.

I use the following script to read all the txt files that are inside a folder.

The qvw file must be in the same directory.

SET FilePath='*.txt';

for each File in filelist (Dir&'\'&'$(FilePath)')

    SET sFile = '$(File)';

    Directory;

    Data:

    LOAD @1:3         as Leg,

         @4:36         as Name,

         @37:48     as Amount

    FROM

    [$(sFile)]

    (fix, codepage is 1252);

    next File

Hope this help!

Regards.

19 Replies
spividori
Valued Contributor

For Each loop for finding text file names

Hi.

I use the following script to read all the txt files that are inside a folder.

The qvw file must be in the same directory.

SET FilePath='*.txt';

for each File in filelist (Dir&'\'&'$(FilePath)')

    SET sFile = '$(File)';

    Directory;

    Data:

    LOAD @1:3         as Leg,

         @4:36         as Name,

         @37:48     as Amount

    FROM

    [$(sFile)]

    (fix, codepage is 1252);

    next File

Hope this help!

Regards.

Not applicable

For Each loop for finding text file names

Thanks Sandro, your method helped me.

Not applicable

For Each loop for finding text file names

Thanks Sandro,

This is just perfect.

Best regards

Piskitta

Not applicable

For Each loop for finding text file names

Hi,

Is it possible to get the creation time of each file and load only those files after a certain time?

Best regards,

Xue Bin

jagannalla
Valued Contributor III

For Each loop for finding text file names

Hello,

Use the below code. It givs the name, size and timestamep of created file.

sub DoDir (Root)

for each Ext in 'qvw','qva','qvo','qvs'

for each File in filelist (Root&'\*.'&Ext)

Load '$(File)' as Name,

FileSize('$(File)') as Size,

FileTime('$(File)') as FileTime

autogenerate 1;

next File

next Ext

for each Dir in dirlist (Root&'\*')

call DoDir (Dir)

next Dir

end sub

call DoDir ('D:\Project_Examples')

Not applicable

Re: For Each loop for finding text file names

Hi,

Thank you for your reply. I think I didn't make myself clear. I have a new text file generated every 15 minutes. I am processing this file every 15 minutes. I want to load only this file as the rest has already been processed. Anyway I already found out a solution using this:

Let creationtime=FileTime('$(FILE)');

However, I still cannot figure out how to get a time 15 minutes before current time.

I have

CurrentTime=now();

How can I get a variable 15 minutes from now? I know this is a simple question. But I simply couldn't find the answer on the forum. It would be really nice if Qlikview could provide a documentation about its functions.

Regards,

Xue Bin

jagannalla
Valued Contributor III

Re: For Each loop for finding text file names

Hope it helps you below code

vVaraible =Date(Now()- Interval(Interval#(15, 'm'),'mm'),'mm')

Not applicable

Re: For Each loop for finding text file names

Hi,

By applying your code i get some integer(56) as output. What i needed was a date. Anyway I've solved this. Just use

vVariable=timestamp(now()-maketime(00,15,00)).

Regards,

Xue Bin

Not applicable

Re: For Each loop for finding text file names

Hi Sandro, may be you can help me. I have a similar problem. I have monthly csv s in one folder, which I have to load to appropriate monthly qvds. I want to make a loop of loading this csvs. So if I use your procedure, how I store this csvs to the appropriate qvds with dynamic monthly names? Thanks a lot!

Community Browser