Loading all excel files from a folder where columns don't match
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.
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, nolabels, delimiteris ',', msq) WhereRecNo() = 1 ;
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.
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, nolabels, delimiteris ',', msq) WhereRecNo() = 1 ;
