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

smagboo2019 — Thu, 02 Jul 2020 15:07:54 GMT

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,

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

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

cwolf — Fri, 03 Jul 2020 15:47:23 GMT

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

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

smagboo2019 — Mon, 06 Jul 2020 13:49:08 GMT

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;

topic Re: Load Multiple Excel Files into one table when each file may have different field names. in QlikView

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

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

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