Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

loops in script

Hi All,

Today while referring to Henric post on Loops in the Script, I came to know that we can load data from excel file whose names are listed in a Tables using below script.

For vFileNo = 1 to NoOfRows('FileListTable')

      Let vFileName = Peek('FileName',vFileNo-1,'FileListTable');

      Load *,

         '$(vFileName)' as FileName

      From [$(vFileName)];

   Next vFileNo

Now, In may case I want to load data from those excel files whose names are not listed in the table.

Where do I need to do the changes to achieve output as per my requirement.

Thanks in advance

Regards

Priyanka

16 Replies
marcus_sommer

Try:

For vFileNo = 1 to NoOfRows('FileListTable')

     trace $(vFileNo);

      Let vFileName = Peek('FileName',$(vFileNo)-1,'FileListTable');

     trace '$(vFileName )'; // checking in log-file and process-window

      Load *,

         '$(vFileName)' as FileName

      From [$(vFileName)] (ooxml, embedded labels, table is [YourTable]); // could be also biff-format

   Next vFileNo

- Marcus

Not applicable
Author

Hi Marcus,

Thanks for the prompt reply, But it is not working as accepted.

-Priyanka

jonathandienst
Partner - Champion III
Partner - Champion III

>>load data from those excel files whose names are not listed in the table


So how do you know which files to load? Perhaps you mean all the files in a folder except those listed? You could use something like this:


For Each vFile in FileList('e:\data\*.xlsx')


     Let vFileName = SubField(vFile, '\', -1);

     If Not(Exists(FileName, vFileName)) Then


          ...perform load


     End If

Next //vFile


(adapt for the correct file path and name mask in FileList)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

Thanks for your response.

For Each vFile in FileList('*.csv')
Let vFileName = SubField(vFile, '\', -1);
If Not(Exists(FileName, vFileName)) Then
Load *,
'$(vFile)'
as FileName,
Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
From [$(vFileName)];
End If
Next vFile

I have implemented above code but it is not working, while debugging I found that FileName value is Null.

Regards

Priyanka

PradeepReddy
Specialist II
Specialist II

try like this...

Let vFileName = SubField($(vFile), '\', -1);

Not applicable
Author

Hi Pradeep,

It's not working.

I guess  I am missing something in below line because FileName is storing Null value

If Not(Exists(FileName, vFileName)) Then

Regards

Priyanka

jonathandienst
Partner - Champion III
Partner - Champion III

The code assumed you had loaded the file names to exclude into a field called FileName as per your initial post. So FileName is a field, not a variable.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi,

Yes, I know that

Below is the complete script

ExcelNames:
Load distinct FileName, Now() as LoadDate
Resident Volume;

For Each vFile in FileList('*.csv')
Let vFileName = SubField(vFile, '\', -1);
If Not(Exists(FileName,vFileName)) Then
Load *,
'$(vFile)'
as FileName,
Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
From [$(vFileName)];
End If
Next vFile

ExcelNames is the table containing names of those files which I don't want to load again.

jonathandienst
Partner - Champion III
Partner - Champion III

And how many records are loaded into ExcelNames?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein