Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
// 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]);
// 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]);
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;