3 Replies Latest reply: Mar 24, 2011 4:53 PM by Johan Adolfsson RSS

    Errorhandling, field names and excel sheetnames

    Johan Adolfsson

      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

       

        • Errorhandling, field names and excel sheetnames
          Rob Wunderlich

          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.

           

            • Errorhandling, field names and excel sheetnames
              Johan Adolfsson

              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

              • Errorhandling, field names and excel sheetnames
                Johan Adolfsson

                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,