How to load excel files having different format eg. Biff, ooxml dynamically

[thabo2g5]

Hi everyone , 

How to load excel files having different format eg. Biff, ooxml dynamically?

excel files are either in biff format or ooxml format when I ran the code below I get this error for some excel files :

The following error occurred:

Bad Zip File

The error occurred here:

I have this code 

I think the error occur because of the format qliksense read the excel file in.

sub DoDir (Root)

   for each Ext in 'xls'

      for each File in filelist (Root&'\*' &Ext)

Fieldname:

         LOAD 

            '$(File)' as filename, 

            FileSize( '$(File)' ) as Size, 

            FileTime( '$(File)' ) as FileTime,

            SubField('$(vFile)', '/', -1) as file

         autogenerate 1;

      next File

   next Ext

   for each Dir in dirlist (Root&'\*' )

      call DoDir (Dir)

   next Dir

end sub

call DoDir ('********');

FOR Each vFile in FieldValueList('filename')

  let vsub =  SubField('$(vFile)','/',4);

  trace $(vsub);

 IF (SubField('$(vFile)','/',4) < 2019) then 

 Data:

LOAD

    Date(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Date,

    Month(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Month,

    Year(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Year,

    MonthName(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as MonthYear,

@2 as Manager,

    @3 as Origin,

    @6 as "Investment Style",

    @7 as Holding,

    @8 as Movement,

    @11 as Value

FROM [$(vFile)]

(biff, no labels, header is 4 lines, table is [Comparative Holdings$])

Where not IsNull(@3) and Trim(@2) <> '';

Else 

    Data:

    LOAD

        Date(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Date,

        Month(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Month,

        Year(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Year,

        MonthName(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as MonthYear,

        @2 as Manager,

        @3 as Origin,

        @6 as "Investment Style",

        @7 as Holding,

        @8 as Movement,

        @11 as Value

    FROM [$(vFile)]

    (ooxml, no labels, header is 4 lines, table is [Comparative Holdings$])

    Where not IsNull(@3) and Trim(@2) <> '';

 End If

Next

drop Table Fieldname;

[marcus_sommer]

You may just add the needed format-information here:

... ($(vFileType), no labels, header is 4 lines, table is [Comparative Holdings$]) ...

and this variable is set before in regard to EXT or the year-query in the if-loop or ... but EXT should be sufficient and then you may save the two redundant load-statements.

Beside this - by associating the date later with a calendar you could skip the year/month deriving and if they are needed for further script-logic you could simply the approach by doing it in a preceding-load, like:

load *, month(Date) as Month, ....;
LOAD Date(Date#(Right(FileBaseName(),10),'DD-MM-YYYY')) as Date,
....