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
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
Hi Marcus,
Thanks for the prompt reply, But it is not working as accepted.
-Priyanka
>>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)
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
try like this...
Let vFileName = SubField($(vFile), '\', -1);
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
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.
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.
And how many records are loaded into ExcelNames?