Load Multiple Excel Files into one table when each file may have different field names.

[smagboo2019]

Load Multiple Excel Files into one table when each file may have different field names.

I need to be able to load multiple files with a single load or script. The files have the same field names for the most part, but over time new fields are added to the end of the files.

I have multiple Excel files that typically have been loaded with a simple

BaseData:
LOAD *
FROM
[\\Path\*.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [Data]);

The above works as all the files have the same header names. Over time we’ve now added new data fields to the end of the Excel files, so we’ll have:

File 1 - Field A, B, C

File 2 - Field A, B, C, D

File 3 - Field A, B, C, D, E

As these files are only write files our solution has been to archive the data when new data fields were added. We’d create a QVD load where File 1 would be:

Load A,

B,

C,

‘ x’ as D,

‘y’ as E

…

File 2 would be similar, and then we’d continue to load the new File 3 type without out error or synthetic joins. Effectively creating one table of our data.

Now due to a business process change this folder of Excel files contains multiple file types over time. On one day we might get File 3 types for weeks then all of a sudden from an older tool we get File Type 1 or 2.

QlikView fails to load correctly in this situation and for now the only solution we have is to manually identify the file types and process them manually.

I’d love a solution where QV could identify the file type by the header values and then load and concatenate the tables correctly.

Thanks for your help.

[cwolf · Accepted solution]

// 1. Create a empty table with an existing fieldname
BaseData:
LOAD
Null() as A
AutoGenerate 0;

// 2. Concatenate all Files
Concatenate(BaseData)
LOAD *
FROM
[\\Path\*.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [Data]);

[smagboo2019]

Thanks, I had to load all fields as NULL to make this work, but your solution appears to have solved my problem. I had attempted something similar, but didn't add the auto generate or the NULL(). 

Thanks.

BaseData:
LOAD
Null() as A,
Null() as B,
Null() as C,
Null() as D,
Null() as E
AutoGenerate 0;