Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Change in .xlsx file format

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.

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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);

NEXT

DROP FIELD dummyfield from  MyFinalTable;

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Creator
Creator

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);

NEXT

DROP FIELD dummyfield from  MyFinalTable;

View solution in original post