loops in script

[#1]

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

[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

[#3]

Hi Marcus,

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

-Priyanka

[jonathandienst]

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

[#5]

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]

try like this...

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

[#7]

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]

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.

[#9]

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]

And how many records are loaded into ExcelNames?