3 Replies Latest reply: Aug 17, 2018 2:44 PM by Todd Buss RSS

    detecting file error while loading multiple excel files

    Todd Buss

      I'm able to load multiple excel files during a qliksense data load, but one or more of the files has a wrong format (missing header).  I don't want to check each excel file manually. 

       

      During the load, can QlikSense present a messagebox with the filename of each successful .xlsx file name?  Better yet, display the filename when it fails?

        • Re: detecting file error while loading multiple excel files
          Gysbert Wassenaar

          Nope. Qlik Sense can't display message boxes. I suppose you could write a test routine that creates a list of excel files, then tries to load the first record. That will either succeed or fail. If you set the ErrorMode variable to 0 first then you can test after each load if an error occurred and log the file name to a table. After the load you can display the records of this table to see which files failed to load. Something like this:

           

          SET ErrorMode = 0;

           

          For vFile in FileList('LIB://MyExcelFiles\*.xlsx')

           

               MyData:

               LOAD * FROM [$(vFile)] (xlsx, ...etc);

             

               If ScriptErrorCount>0 Then

             

                    FailFiles:

                    LOAD '$(vFile)' as FailedFile AutoGenerate 1;

             

               End if

           

          Next

           

          SET ErrorMode =1;

           

          NOTE: the above is untested. You can debug it yourself

          • Re: detecting file error while loading multiple excel files
            Quy Nguyen

            Hi Todd,

            In your script, set the errormode =0, so Qlik will ignore any error and continue script execution. Then you need some script to log the error manually after any excel load command. For example:

             

            Set ErrorMode = 0;
            //Any Excel load
            Load * From [FileName];
            // Log Error
            ErrorList:
            First 1 Load 
                 Date(today(),'YYYYMMDD') As ExecuteDate,
                'File Name' As FileName,
                '$(ScriptErrorCount)' As Error_Seq,
                '$(ScriptError)' As ErrorName,
                '$(ScriptErrorDetails)' As ErrorDetail
            AutoGenerate 1;
            

            Then after reloading, you just check the ErrorList table.

            Hope this helps.