Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Michiel_QV_Fan
Specialist
Specialist

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

Hi, loading multiple excel files. Some files cannot be opened (not in QlikView, not in Excel, locked in some way).

Despite SET errormode = 0; the load stops.

Is there a work around needed? 

No error is given.

print screen load windowprint screen load window

Labels (2)
20 Replies
marcus_sommer

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

Michiel_QV_Fan
Specialist
Specialist
Author

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

marcus_sommer

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 ...

Michiel_QV_Fan
Specialist
Specialist
Author

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

marcus_sommer

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.

Michiel_QV_Fan
Specialist
Specialist
Author

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

marcus_sommer

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.

 

Michiel_QV_Fan
Specialist
Specialist
Author

 
marcus_sommer

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 ...

Brett_Bleess
Former Employee
Former Employee

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 do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.