How to avoid field not found when loading multiple files with only one column present or not
Hi
I load, from a directory, csv files that are generated by a tiers software. I can't change names or location or format of those files.
Due to an update, the extraction has one field which was added in recent files. I want to add this field to the load statement when available or set a default value (0 since it's a quantity) for older files (where the field is not available).
How could I do that in the same load statement ? avoiding te Field not found error ?
The dream syntax would be : LOAD if(fieldexists(MyField),MyField,0) AS MyField
I suggest you iterate over the files in a loop, and use the built-in error handling possibilities. Something like this:
for each file in filelist...
set ErrorMode=0; //don't stop execution on errors concatenate(MyTable) load A, B, MyField from [lib://blablabla/$(file)]; //try load with MyField if ScriptError>0 then set ErrorMode=1; //restore errormode here, since you probably want to raise an error if none of the attempts are successful concatenate(MyTable) load A, B from [lib://blablabla/$(file)]; //MyField did not exist, try loading without it endif
next file set ErrorMode=1; //make sure that ErrorMode has been restored to it's original setting
One possible issue here is that the script won't stop for any errors
I usually approach this problem by using "Load *" instead of listing fields names. It requires that you predefine the table so you can use Concatenate in wildcard load (or loop if you use filelist()). Like this:
Data: LOAD 0 as DummyField AutoGenerate 0;
Concatenate (Data) First 10 LOAD * FROM lib://data/T*.xlsx (ooxml, embedded labels, table is Sheet1);
