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: 
qw_johan
Creator
Creator

Errorhandling, field names and excel sheetnames

Hi,

I am loading serveral Excel-files by looping through a directory and sometimes subdirectories. This works fine but.....

1. Sometimes there is an Excel-file with a different sheetname ... instead of the default 'Sheet 1' it says lets say 'Info'.
What is the best way to handle this? I would rather not manually have to edit the sheetname. And I don't want to load all the sheets
in the excel file, only the first (always the first sheet). I would like to be able to write "if sheetname = Sheet1 or Sheetname= Info then..."

2. How to deal with situations where field names(lables) doesn't have the names you expect them to have? Qlikview throws an error
and complains about the fieldname. Is there a good way to take care of this in the script?

In the helpfile i have read about errorhandling and "errormode".

set ErrorMode=0;
load * from abc.qvw;
if ScriptError=11 then //Field not found in table (10 - Table not found)

code code code

end if

Is this the best way to deal with above situations?

Thanks

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The easiest way to load the first sheet is to reference it by the sheet number using @n syntax:

"table is @1"

I haven't experimented with using ErrorMode to handle column variations. I usually use SQLTABLEs to examine the sheets and pick out what I want. This blog post
http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html

shows how to do this and the QV Cookbook has a complete example.

View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The easiest way to load the first sheet is to reference it by the sheet number using @n syntax:

"table is @1"

I haven't experimented with using ErrorMode to handle column variations. I usually use SQLTABLEs to examine the sheets and pick out what I want. This blog post
http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html

shows how to do this and the QV Cookbook has a complete example.

qw_johan
Creator
Creator
Author

Hi,

Thanks for the quick reply.

I didn't know that I could use @1 to reference the first sheet. GREAT...just what I needed.

I also downloaded and had a quick look at QV cookbook to see how you handle culumn variations...very interesting.

Looks like this could be VERY helpful to me...a lot of code (and commenting of the code) and interesting stuff.

Thanks! Smile

qw_johan
Creator
Creator
Author

Hi again,

Using "@" to reference a sheet works very well.
But I am having problem with the SQLColumns technique that I saw in QV Cookbook. This is how I do it...

ODBC CONNECT TO [Excel Files;DBQ='C:\Lists\003_9786438_091219.xls];
columns:
SQLColumns;
DISCONNECT;

But my odbc connection keeps failing, Message...SQL##f - SqlState: 42000, ErrorCode: 4294965487 ...
I'm on windows 7, don't have the regular MS JET drivers installed...could this be the reason? It would be helpful to me if I could know the number or columns in an excel file before I load it. Is there any other way to get this information?

Is there other techniques or ways to get around the problem with loading multiple excelfiles into Qlikview when u have files with different number of columns and/or different column names ?

Thanks,