Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears,
I have an application which reads a bunch of excel files (> 6.000, all should have the same format) in a loop. At a certain point I get the following error :
"Error: Cannot open HTML file"
My question is : i am sure that only a few of the files cuase the error but I don`t know which..so how can a trace the name of the file which could not be opened?
Here is my script code :
-------------------------------------------------------------------------------------------
let vDataFolder = '..\..\..\ExcelData\';
Set ErrorMode=0;
if(FileSize('$(vDataFolder)*.xls')>0) then
trace "files exist";
for each vFiles in filelist('$(vDataFolder)*.xls')
Ligalisten_tmp1:
LOAD FileName() as filename, FileTime() as filetime, *
FROM [$(vFiles)](html, codepage is 1252, no labels, table is @4);
NEXT
ELSE
trace "no files exist";
END if;
---------------------------------------------------------------------------------------------------
Thanks and regards,
Hendrik
Are you getting data from Share point?
In load script make this statement before execution of loop:
ErrorMode = 0;
It'll make your loop run without stop.
Another part of code must load FileName of handeled file. Something like this:
First 1 LOAD FileName() as FileName
FROM
(fix, codepage is 1251, no labels);
You need to make table of passed and unpassed files in your loop.
After loop turn ErrorMode ON by script:
ErrorMode = 1;
I guess you are well trained developer in scripting and my advise would help to make what you want.
I just tested your code and I found problem in the (html, codepage is 1252, no labels, table is @4);
Because of this, each file reading is throwing cannot open HTML file.
I suggest you create script by clicking table files button at the bottom of the script editor and generate the code from one of your target file and use that code in the script above. When I used (biff, embedded labels, table is DATA$); for my sample files it worked properly.
Also I kept one HTML file in the folder, it skipped that because we are only targetting the files with xls extension. so having html file in folder is not a problem.
Thanks,
Hey guys,
thank you really much for your help, I was too blind to see that I already had each filename in the document log
So was able to locate the bad file.
Thanks and cheers
How did you solve it? just curious to know!