For Each loop for finding text file names

[#1]

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

[spividori · Accepted solution]

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.

[#3]

Thanks Sandro, your method helped me.

[#4]

Thanks Sandro,

This is just perfect.

Best regards

Piskitta

[#5]

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]

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')

[#7]

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]

Hope it helps you below code

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

[#9]

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

[#10]

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!