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

thabo2g5 — Fri, 12 Apr 2024 12:34:29 GMT

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

drop Table Fieldname;

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

marcus_sommer — Fri, 12 Apr 2024 15:11:14 GMT

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,
....

topic How to load excel files having different format eg. Biff, ooxml dynamically in App Development

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

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