topic Re: Loading all excel files from a folder where columns don't match in Management & Governance

Loading all excel files from a folder where columns don't match

joseph_scorsone — Sat, 16 Nov 2024 03:06:36 GMT

I am trying to load all excel files from a specific folder, but a few of the files have a column missing. Qlik Sense is giving an error. I want to be able to load those files even if that column is missing. Does anyone Know how to handle this?

Example

FileOne.xlsx has the columns FirstName, LastName, Address, Address_Line_1, City, State, Zip

FileTwo.xlsx only has the following: FirstName, LastName, Address, City, State, Zip

Becuase Address_Line_1 is missing, the load script fails. How do I prevent it from failing, but still bring in the other columns?

SET ErrorMode = 0; does not work, this keeps the script from failing, but does not load the other columns. It ignores the file completely.

Re: Loading all excel files from a folder where columns don't match

rwunderlich — Tue, 03 Mar 2020 16:00:02 GMT

Can you use

LOAD *

-Rob

Re: Loading all excel files from a folder where columns don't match

joseph_scorsone — Tue, 03 Mar 2020 16:11:23 GMT

Yes, I suppose I could. The example I shared is very basic. In reality, I have over 150 fields and I wanted to make the code more elegant by using specific field names. I want to be able to write something more specific for only the field names I wanted. I will try that if there is no other way.

Re: Loading all excel files from a folder where columns don't match

rwunderlich — Wed, 04 Mar 2020 02:08:59 GMT

If the LOAD * would work, I would go for it. I suppose there might be there the case where you would be concerned about loading a lot of extra data. You could either drop the unwanted fields at the end or figure out up front which fields you want to load. In either case I suppose you would have a list of candidate fields you want to keep. Here's an example of the upfront method. The trick is to read the first row as data to get the labels and then pivot them into a list using CrossTable.

// Load candidate list once
LOAD * INLINE [
ColumnCandidate
Sort Order
Common Name
Formal Name
Type
Sub Type
];

// Then for each file
Columns:
CrossTable (x, Colname)
LOAD 1, *
FROM
[..\yourfile.csv]
(txt, utf8, no labels, delimiter is ',', msq)
Where RecNo() = 1
;

TempList:
LOAD
Concat('[' & Colname & ']', ', ') as ColList
Resident Columns
Where Exists(ColumnCandidate, Colname)
;
LET vColList = peek('ColList');
DROP Table Columns, TempList;

Data:
LOAD
$(vColList)
FROM thefile.csv
(txt, utf8, embedded labels, delimiter is ',', msq);

At the bottom of this post is an example of the other option, "LOAD *" and then drop extra fields at the end of the process.

https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com