topic Re: for each dir in QlikView

for each dir

Tue, 22 Sep 2015 06:24:32 GMT

Hi All,

The problem iam facing with the script is .. it always takes the source files only from where the qvw is placed. Even changing the path to the different drive it does take the file from their..

All my files are placed in a folders like 2014.csv /2015.csv etc.. so the csv file names are like file1-2014. so here file1-2014 needs to be into product. similarly I have file2-2014...etc

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

    FoundFile = right('$(FoundFile)', 8);

Product:
LOAD

Customer Number,
    Customer Name,
     Product name] as [Product Name],
     [Product number] as [Product Number]

FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFIleNames('H:/') ;

Re: for each dir

jonathandienst — Tue, 22 Sep 2015 06:28:26 GMT

You are changing the iteration variable inside the loop.

FoundFile = right('$(FoundFile)', 8);

This may cause unpredictable behaviour.

Re: for each dir

jonathandienst — Tue, 22 Sep 2015 06:31:12 GMT

Rather use the inner script like this:

For Each FoundFile in filelist( Root & '\*.csv')

Product:

LOAD

Customer Number,

Customer Name,

Product name] as [Product Name],

[Product number] as [Product Number]

FROM [$(FoundFile)]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Next FoundFile

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 06:34:56 GMT

Your function call parameter is wrong?

it should be Call GetCSVFIleNames('H:') ;

Re: for each dir

Tue, 22 Sep 2015 06:46:15 GMT

Hi Jonanthan,

But their are multiple csv folders

for ex : 10020203.csv

100203232.csv

2014.csv

2013.csv

so iam only looking for folders like 2013.csv /2014.csv that's the reason I have limited the found file to look for 8 characters.

now as you said if I remove .. its taking most of the files.

Re: for each dir

Tue, 22 Sep 2015 07:37:18 GMT

Hi Sridhar,

H: drive is good.. but still limitation of found file to 8 characters how can we achieve this?

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 07:58:20 GMT

Try

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

    FoundFile = right('$(FoundFile)', 8);

//To get the file name

let vTmpFoundFile=SubField(FoundFile,'\',-1);

Product:
LOAD

Customer Number,
    Customer Name,
     Product name] as [Product Name],
     [Product number] as [Product Number]

FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFIleNames('H:') ;

hth

Sasi

Re: for each dir

Tue, 22 Sep 2015 08:10:06 GMT

hi Sridhar,

it is loading but how do I know it is take from file1-2014 only?

Re: for each dir

Tue, 22 Sep 2015 08:14:02 GMT

hi sasi,

for ex

my requirement was to

['$(FoundFile)'\file1-'$(FoundFile)'] -file into Product:

but indeed it is stopping exactly after loading into product.. file 2 it is saying not found..

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 08:21:05 GMT

Hi Benny

Why dont you store the file name into a field in your table so that you will know which file the data has come from?

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

    FoundFile = right('$(FoundFile)', 8);

//To get the file name

let vTmpFoundFile=SubField(FoundFile,'\',-1);

Product:
LOAD

Customer Number,
    Customer Name,
     Product name] as [Product Name],
     [Product number] as [Product Number]
    '$(FoundFile)' as SourceFile
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFIleNames('H:') ;

hth

Sasi

Re: for each dir

Tue, 22 Sep 2015 08:24:53 GMT

Hi Sridhar,

Their is a mistake in the load here.

My requirement is each file is dedicated to each load

so if put just as from ['$(FoundFile)'] .it also tries to load the same file as below and gives an error field not found

Product:
LOAD

Customer:
LOAD

Customer Name,
First Name,
Address] as [Address]

FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

so actually it should be as below.. so this case it is not working

LOAD

Customer:
LOAD

Customer Name,
First Name,
Address] as [Address]

FROM
['$(FoundFile)'\file2-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Re: for each dir

Tue, 22 Sep 2015 08:34:41 GMT

Hi sasi,

I can try this '$(FoundFile)' as SourceFile - but how can I give the filenames for as SourceFile.?

for example - file1 is common name .. only the year goes on changing --based on the folder my file looks like this as below

2014.csv folder >> has the following below files

file1-2014.csv

file2-2014.csv

file3-2014.csv

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 08:38:58 GMT

Try like this

sub GetCSVFIleNames(Root)

for each FoundFile in filelist( Root & '\*.csv')

FoundFile = right('$(FoundFile)', 8);

let vTmpFoundFile=SubField(FoundFile,'\',-1);

//if filename contains file1

if Index('$(FoundFile)','file1-')>0 then

LOAD

[Customer Number],

[Customer Name],

[Product name] as [Product Name],

[Product number] as [Product Number]

FROM

['$(FoundFile)']

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Elseif Index('$(FoundFile)','file2-')>0 then

Customer:

LOAD

[Customer Name],

[First Name],

[Address] as [Address]

FROM

['$(FoundFile)']

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

end if

next FoundFile;

for each SubDirectory in dirlist( Root & '\*' );

call GetCSVFIleNames(SubDirectory);

next SubDirectory;

end sub

Call GetCSVFIleNames('C:') ;

Re: for each dir

Tue, 22 Sep 2015 09:06:08 GMT

hi sasi,

it looks perfect so far..iam trying to put across all files and do the test..can you tell me what this corresponds to

let vTmpFoundFile=SubField(FoundFile,'\',-1);

Re: for each dir

jonathandienst — Tue, 22 Sep 2015 09:09:05 GMT

My post is just the correct way to do the inner part of the loop. You still need the rest to navigate to the other folders.

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 09:13:11 GMT

oh that is to just to get the file name without the path..I thought you needed that in your logic

Re: for each dir

Tue, 22 Sep 2015 09:37:50 GMT

hi sasi,

I have just started the load. it has around 600 files so it would take few mnts.. can you also let me know can I load this all the csv data into a qvd ----means ..

can I include store into statement at the end after the below statement

Call GetCSVFIleNames('H:') ;

Store into H:\...test.qvd ..after storing how could I load the data

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 09:50:43 GMT

You could try after your method call

Call GetCSVFIleNames('H:') ;

Store Products into Products.qvd (qvd);

Store Customers into Customers.qvd (qvd);

hth

Sasi

Re: for each dir

Tue, 22 Sep 2015 12:17:26 GMT

Hi sasi,

I loaded all the files and is perfectly looping through.

Re: for each dir

sasiparupudi1 — Tue, 22 Sep 2015 12:24:08 GMT

Great,glad that your problem is solved.

iMay I suggest that you close this thread by marking any helpful and a correct answer so that It can be helpful to other who might be looking for a similar solution?

Thanks

Sasi