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

[Michiel_QV_Fan]

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

[Vegar]

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

[Michiel_QV_Fan]

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.

[Brett_Bleess]

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

[marcus_sommer]

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

[Michiel_QV_Fan]

Brett:

version:  11.20.12904.0 SR12 64-bit

[marcus_sommer]

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

[Michiel_QV_Fan]

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

[marcus_sommer]

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

- Marcus

[Michiel_QV_Fan]

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.