Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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!
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,