Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi,
try with a wildmatch
if not wildmatch(Filename,vFilename) THEN
.
.
.
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
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
I guess there is no issue with file extension as FileName column in ExcelNames Table contain extension of all files.
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.
It's worked for me. Did you try to check that in Debug mode?