Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kmswetha
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
jlongoria
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
marcus_sommer

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

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