topic For Each loop for finding text file names in QlikView

For Each loop for finding text file names

Tue, 06 Sep 2011 12:47:19 GMT

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

For Each loop for finding text file names

spividori — Tue, 06 Sep 2011 13:09:31 GMT

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.

For Each loop for finding text file names

Thu, 15 Sep 2011 07:08:14 GMT

Thanks Sandro, your method helped me.

For Each loop for finding text file names

Mon, 28 Nov 2011 15:27:37 GMT

Thanks Sandro,

This is just perfect.

Best regards

Piskitta

For Each loop for finding text file names

Wed, 25 Apr 2012 08:47:47 GMT

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

For Each loop for finding text file names

jagannalla — Wed, 25 Apr 2012 08:57:06 GMT

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

Re: For Each loop for finding text file names

Wed, 25 Apr 2012 10:11:29 GMT

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

Re: For Each loop for finding text file names

jagannalla — Wed, 25 Apr 2012 11:22:58 GMT

Hope it helps you below code

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

Re: For Each loop for finding text file names

Thu, 26 Apr 2012 03:11:44 GMT

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

Re: For Each loop for finding text file names

Tue, 25 Sep 2012 17:02:10 GMT

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!

Re: For Each loop for finding text file names

spividori — Wed, 26 Sep 2012 14:02:13 GMT

Hi.

You should save the file name in a variable, for example:

let sNom = '01.01.2012'

and then do something like the following:
STORE Data INTO $(sNom).qvd;

Hope this help!

Regards.

Re: For Each loop for finding text file names

Tue, 02 Oct 2012 14:32:57 GMT

Hi,

I am doing the following in order to load and store dynamically:

SET FilePath='*.csv';

set vFileName = 'file';

for each File in filelist (C:\Users\innas\Desktop\Qvd\QV_new\daily\ &'\'&'$(FilePath)')

    SET sFile = '$(File)';

    Directory;

   NetworkDomainDailyData:

    LOAD *

    FROM

    [$(sFile)]

    (fix, codepage is 1252);

store * from NetworkDomainDailyData into C:\Users\innas\Desktop\Qvd\Sql_Data\$(vFileName).qvd(qvd);

drop table NetworkDomainDailyData;

    next File



    exit script;

and it does not load/store anything.

Where is my mistake?

thanks a lot.

Re: For Each loop for finding text file names

spividori — Tue, 02 Oct 2012 21:11:06 GMT

Hi.

Try the attached example.

Regards.

Re: For Each loop for finding text file names

Wed, 03 Oct 2012 12:49:15 GMT

Hi Sandro,

still does bot load anything.

What else can be the reason?

Re: For Each loop for finding text file names

spividori — Wed, 03 Oct 2012 13:00:14 GMT

Hi.

Please, upload your QlikView file for review.

Regards.

Re: For Each loop for finding text file names

Wed, 03 Oct 2012 13:08:40 GMT

Hi,

here it is.

Thanks a lot.

Re: For Each loop for finding text file names

spividori — Wed, 03 Oct 2012 13:32:54 GMT

Hi.

Sorry, you could upload a csv file you are trying to read.

Or an example with unreal data.

Regards.

Re: For Each loop for finding text file names

Wed, 03 Oct 2012 13:55:03 GMT

Hi ,

here is the file csv.

Re: For Each loop for finding text file names

spividori — Wed, 03 Oct 2012 14:18:30 GMT

Hello again.

See the attachment.
You must change the directory:
call DoDir ('C:\Users\spividori\Desktop\QView\forum\LeerArchivos'); for yours

and it should work.

The error was:
(ooxml, embedded labels, table is Hoja1);

this line is characteristic for each file type. You were trying to read a csv file with sentences for excel files.

I hope you understand me, my English is poor.

Regards.

Re: For Each loop for finding text file names

Wed, 03 Oct 2012 15:10:32 GMT

Hi,

I managed with it.

Thanks a lot!!!!