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
Is this the best way to deal with above situations?
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 ?