Issue with Explicit labels and No labels while loading an excel into Qlikview document

[Obulreddy1]

I'm encountering an issue with loading data from excel into Qlikview. Every month business users uploads an .xlsx file which will be loaded into my data model. Initially when we setup this we used embedded lables and as the business users have full control of the .xlsx file, sometimes they are renaming the columns, which is causing the data model to fail.

So, we switchd to use the explicit lables and started loading the data in the below format

Load
A as Column1,
B as Column2,
C as Column3
FROM [Myfile.xlsx]
(ooxml, explicit labels, table is [Sheet1]);

The above code worked out well for a month, but when business users replaced the .xlsx file next month, our data model started failing with the below error

Error: Field 'A' not found

So, we loaded the excel file again without labels as below and the data model ran successfully.

Load
@1 as Column1,
@2 as Column2,
@3 as Column3
FROM [Myfile.xlsx]
(ooxml, no labels, table is [Sheet1], filters(Remove(Row, Pos(Top, 1))));

The following month business replaced the file again with new data and we encountered the below error

Error: Field '@1' not found

When I reverted back to explicit labels, data model was successful again. And every month I had to alternate between the explicit lables and no labels to avoid the data model failures.

I would greatly appreciate if anyone can suggest a robust and permanent solution for this.

Thanks
Obulreddy

[marcus_sommer · Accepted solution]

There is no technically solution for a conceptual issue. You may load the data multiple times within an if-loop and using ERRORMODE (by an error continue just with the next load-version) and/or you may just use: load * from EXCEL and check afterwards which fields were loaded and in which order to rename them appropriate. But you could never ensure even with hundreds of iterations that there isn't any load-error and even more important that all content is loaded properly within the right fields.

The real solution is to teach the users how they should create/adjust the files/content and to support it with various restrictions / validity checks in the files - for example with a change-protection for the first row and similar stuff.

- Marcus