Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MVP & Luminary
MVP & Luminary

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Yes, that's clear. But if we would know the reason for the error we might be able to bypass it with some workaround and also Qlik could check if this kind of error could be added to the errormode-handling respectively fix it as bug.

Beside this my previous question was aimed to check if the file is locked in some way from the OS or any security tool and did it really exists there - maybe it's just a link and not a real file. Or anything in this direction.

Further is the storage a normal windows-storage?

- Marcus

michielvandegoo
Valued Contributor

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Yes, that's clear. But if we would know the reason for the error we might be able to bypass it with some workaround and also Qlik could check if this kind of error could be added to the errormode-handling respectively fix it as bug. OK, clear.

Beside this my previous question was aimed to check if the file is locked in some way from the OS or any security tool and did it really exists there - maybe it's just a link and not a real file. Or anything in this direction. These are real excel files. Cannot be opened in excel either because someone or something has locked these apparently.

Further is the storage a normal windows-storage? Yes

MVP & Luminary
MVP & Luminary

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Locked means the file couldn't be (manually) copied, moved or renamed or anything like that?

Maybe a look within the windows event-log and/or the logging from your security tool could give some hint ...

michielvandegoo
Valued Contributor

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Marcus, valid question. But, does that lead to a  solution that these files will be ignored by the QlikView load when errormode = 0?

MVP & Luminary
MVP & Luminary

Re: Load Excel file which cannot be opened not ignored by errormode = 0

In regard to errormode - in the first step rather no - but beside the possibility to find any workaround to the issue Qlik might be able to fix it in any way.

A further possibility might be to load the Excel with an ODBC driver - maybe here is an error returned which the errormode is able to handle.

michielvandegoo
Valued Contributor

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Marcus,

ODBC connect might generate an error. But, the data is in > 3 folders with each max 53 subfolders and in there 4 to 5 subfolders with 2 or more excels (in total 940000 xls). Don't know the SQLtables function good enough if this can be handeld with 1 ODBC connection

MVP & Luminary
MVP & Luminary

Re: Load Excel file which cannot be opened not ignored by errormode = 0

In general it should work because the (dis)connect-statement is inside the dirlist/filelist approach, see for example this posting: Issue-pulling-data-from-a-multi-tab-excel-document which specify some other issues/features but should be good starting point if you would go with this approach.

Before diving in this logic you should do performance-check of loading xls and xlsx directly or per ODBC. I never did it and would assume that ODBC is significantly slower by xls and by xlsx it's rather not a big difference - but it might be adding to a larger amount of time by such many files.

 

Highlighted
michielvandegoo
Valued Contributor

Re: Load Excel file which cannot be opened not ignored by errormode = 0

 
MVP & Luminary
MVP & Luminary

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Ok. It seems that the cause is an open-lock from the OS. I would assume that Qlik should recognize this error and handle it with the errormode because a locked sql-table and/or missing access-rights to a database are quite similar and not extremely rare.

Although it's nothing in regard to the errormode-issue I suggest to consider to find and/or to resolve those accesses - the simplest approach here might be just to restart the system which put the files there and/or to restart the storage-system ...

Support
Support

Re: Load Excel file which cannot be opened not ignored by errormode = 0

Hey guys, apologies for the absence, got tied up on other stuff last week, and I did not have a chance to circle back until today.  Given the version, 11.20 SR12, one thing would be to pull the SR18 11.20 Client and try that I think, as I do know SR12 did still have some stability issues from what I recall, SR13 was where we took care of most of those, so I think it would be worth your while to try SR18 to see if any of the fixes in the interim SRs gets things to work as you desire.  I do not believe R&D are going to entertain looking at SR12 at this point, but if we can replicate this in SR18, I think you could go ahead and open a Support Case with us at that point, but the trick for us is going to be figuring out how to replicate things here.  We may be able to using SysInternals tools to fake a file lock etc. to see if we can cause the same thing, but if you can test SR18 first, that would be great, hopefully that may address things, as the only thing of which I can think is we did have a bug here that may have been fixed.  Sorry I do not anything better for you, but if the file is locked, I do agree ErrorMode should be doing better than it is to handle that...

Regards,
Brett

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.