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

[NareshGuntur]

You create a mapping table with the list of files you don't want to load.

FilterFiles:

Mapping load * inline [

File,Flag

A1.txt, 0

A2,txt, 0

A3.txt, 0

];

And you load all the files with a where condition at the end.

Load....

.....

where applymap('FilterFiles',FileName,1)=1;

[sasikanth]

Hi,

try with a wildmatch

if not wildmatch(Filename,vFilename) THEN

.

.

.

[tamilarasu]

I guess, the file extension causing the problem, Try something like below.

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

For Each vFile in FileList('*.csv')
Let vFileName = Replace(SubField(vFile, '\', -1),'.csv','');
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

[settu_periasamy]

try this (

ExcelNames:

Load distinct FileName, Now() as LoadDate

Resident Volume;

NoConcatenate

Concat_file:

LOAD

  Concat(Chr(39)&FileName&chr(39),',') as FileList

Resident ExcelNames;

Let vFileList=Purgechar(Peek('FileList'),chr(39));

DROP Table Concat_file;

For Each vFile in FileList('*.csv')

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

If Not Match('$(vFileName)','$(vFileList)') then

Load *,

'$(vFile)' as FileName,

Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date

FROM

[$(vFileName)]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

End If

Next vFile

[#6]

I guess there is no issue with file extension as FileName column in ExcelNames Table contain extension of all files.

[#7]

I tried this code, its is loading all the files. i.e filenames which are existing in the table as well as which are not.

[settu_periasamy]

It's worked for me. Did you try to check that in Debug mode?