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

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

Michiel_QV_Fan — Sat, 16 Nov 2024 21:18:01 GMT

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 window

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

Vegar — Wed, 06 Mar 2019 09:16:17 GMT

It sounds like the file is corrupt in some way or that it is not an excel file despite the xlsx/xls suffix.

Try recovering it manually in Excel by following these instructions. https://support.office.com/en-us/article/repair-a-corrupted-workbook-153a45f4-6cab-44b1-93ca-801ddcd4ea53

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

Michiel_QV_Fan — Wed, 06 Mar 2019 09:19:15 GMT

I don't want to recover the file. I want to get passed the QlikView load error. Regardless of file type or whatever error there is with that file.

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

Brett_Bleess — Wed, 06 Mar 2019 15:10:48 GMT

That sounds like an unhandled exception in our code more than likely that ErrorMode=0 cannot handle, so in theory it would be classes a defect, but we need to be able to replicate things, so the best course of action would be to create a support case, and we would need version info as well as one of the 'bad' Excel files as well to try to see if we can cause the same issue.

The only workaround I have is to switch things back to default of value of 1, and run things to see if we get a better error message at that point or not, as that may provide a better clue as to what is going on here. You are correct that ErrorMode=0 should ignore the error, so that is clearly a problem we need to look into, but I really do not have any other workaround other than logic loop that uses the loop to see if the file can be read, and if so, we read it, otherwise skip it.

Really need someone that is a better developer than am I to come up with some additional code that may get around things here in the interim. You did not mention which version you are running either, if you are not on the latest SR of the major release you have installed, I would try pulling the latest SR for that release and run things there, as there is a decent chance that might fix things.

I was able to find a defect related to things in 12.10 track, so if you are on 12.10, trying SR10 should likely fix the issue I would believe, sorry I do not have anything better for you.

Regards,
Brett

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

marcus_sommer — Wed, 06 Mar 2019 16:22:11 GMT

A way to bypass such issue might be to use an EXECUTE statement which executes a batch which tries to open the file with a vbs-macro or any editor or something similar and to fetch an error from this attempt or maybe any kind of content from the file and then using this information for skipping the load or executing it.

I could imagine that this approach might not very easy and therefore I suggest to go at first with the recommendations from Brett.

- Marcus

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

Michiel_QV_Fan — Fri, 08 Mar 2019 07:24:14 GMT

Brett:

version: 11.20.12904.0 SR12 64-bit

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

marcus_sommer — Fri, 08 Mar 2019 08:03:32 GMT

IMO it's a quite stable release and I never experienced such error with it whereby I don't use the ERRORMODE very often.

Another point - not directly related to the issue of not ignoring an error - might be a change from the load-mode. You didn't mentioned it but I assume that you load the files with a filename-wildcard like:

load * from path\fileprefix*.xlsx (fileformat);

If so you should try a change to load with a filelist-loop, like

for each vFile in filelist('path\fileprefix*.xlsx')
...

Maybe it's a workaround for you.

- Marcus

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

Michiel_QV_Fan — Fri, 08 Mar 2019 08:09:00 GMT

I create a filelist first so I use full paths to load each excel

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

marcus_sommer — Fri, 08 Mar 2019 09:49:44 GMT

Which error happens if you tries to open the file with Excel? Did you try to open it with an editor like notepad ++?

- Marcus

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

Michiel_QV_Fan — Fri, 08 Mar 2019 09:55:13 GMT

Marcus, I understand your question but it is not relevant for this particular load to work around the errors by going to the files in detail. My only concern is that I get past the load error to the next file. If a file is not loaded, so be it.

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

marcus_sommer — Fri, 08 Mar 2019 10:10:13 GMT

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.

Further is the storage a normal windows-storage?

- Marcus

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

Michiel_QV_Fan — Fri, 08 Mar 2019 10:23:04 GMT

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

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

marcus_sommer — Fri, 08 Mar 2019 10:31:43 GMT

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

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

Michiel_QV_Fan — Fri, 08 Mar 2019 10:34:22 GMT

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

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

marcus_sommer — Fri, 08 Mar 2019 10:59:35 GMT

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.

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

Michiel_QV_Fan — Fri, 08 Mar 2019 11:05:30 GMT

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

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

marcus_sommer — Fri, 08 Mar 2019 11:23:37 GMT

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.

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

Michiel_QV_Fan — Fri, 08 Mar 2019 11:59:57 GMT

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

marcus_sommer — Fri, 08 Mar 2019 12:28:55 GMT

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

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

Brett_Bleess — Mon, 18 Mar 2019 14:18:27 GMT

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