Loading multiple Excel files - Some with no headers and data

[Qlik_ULG]

Hello,

I'm loading multiple excel files which have a relatively consistent structure. Additional files are generated in the source location regularly.

The application which generates the files creates standard tables on numerous tabs, which contain the data we're using (basically error logs). I'm simply loading all of the files as per the structure and loading the fields from the different tabs. I'm also taking one field from the filename which is the unique identifier for each file.

However, some files will have no data on any tab (if the task had no corresponding errors). The files in this case simply have no data on these tabs, no headers, no table etc.

This obviously causes an error during reload as soon it reaches one of these files, as the fields can't be found. To address this, I have set the ErrorMode variable =0, which ensures all subsequent files load.

However, it means the records of the blank files are not part of the data. We need these to be loaded also, so they can be reported on as error-free tasks.

Any suggestions on how to achieve this?

Thanks in advance.

[rubenmarin]

Hi, using ScriptError you can check when an error has happened and do an alternative script, like only load filename as unique identifier:

https://help.qlik.com/es-ES/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Error...

[Qlik_ULG]

Thank you very much, Ruben.

I tried to apply this logic but with no success as of yet. Below is a summary example of what I have tried.

It still only loads the filenames of files with data. Do you have any guidance on how I can apply this correctly?

SET ErrorMode = 0;

///Loading files

DataTable:
LOAD  [Error #],
             [Error Description],
             [Error Source],

            [Error Date], 
            [Error Score],
           FileName() As  Filename
FROM
[D:\Error Logs\Error Files\*.xls]
(biff, embedded labels);

///Trying to apply script error, to load files where field is not found due to no data

If ScriptError=11 then
LOAD FileName() As Filename

FROM
[D:\Error Logs\Error Files\*.xls]
(biff, embedded labels);

End If

[rubenmarin · Accepted solution]

Hi, maybe with a bucle to read files one by one so you can capture the error of an specific file:

DataTable:
LOAD * Inline [dumbField];

for each File in filelist ('PathTofiles\*.xls')
  Concatenate (DataTable)
  LOAD 
    ...
  From [$(File)] (biff, embedded labels);

  If ScriptError<>0 then
    Concatenate (DataTable)
    LOAD Subfield('$(File)', '\', -1) As Filename
    Autogenerate 1;
  EndIf
next File

DROP Field dumbField;

Not tested, probably needs some fixes.