topic Change in .xlsx file format in QlikView

Change in .xlsx file format

kmswetha — Wed, 06 Apr 2016 15:31:35 GMT

Hi,

I have Multiple files in directory.Say file per day.

And there is a change in the Source file structure.Additional fields has been added. From say starting of Apr 3rd.

I have to handle this while reading it self, since while Load instead of * field names are used.

Any suggestions on how to concatenate data from Apr 3rd. File names has date on it as well.

Re: Change in .xlsx file format

marcus_sommer — Wed, 06 Apr 2016 15:46:44 GMT

In these cases you couldn't use a wildcard-load anymore and needed to change to load your data within a filelist-loop and force within them a concatenation:

Loops in the Script

Re: Load multiples files with different headers

- Marcus

Re: Change in .xlsx file format

jlongoria — Wed, 06 Apr 2016 20:44:24 GMT

If you do something like below, you can iterate through excel files and still use LOAD *. However, it will be obvious after you do this that the "additional" field values will be null for rows that came from files that did NOT contain the "additional" fields in them.

MyFinalTable:

LOAD * inline [

dummyfield

]

;

FOR EACH vFile in FileList('c:\MyDataFiles\MyDataFile_*.xlsx') // where filename is MyDataFile_<YYYYMMMDD>

Concatenate (MyFinalTable)

LOAD

'$(vFile)' as SourceFile

FROM

$(vFile)

(ooxml, embedded labels, table is Sheet1);

DROP FIELD dummyfield from MyFinalTable;