Hello everyone. I am looking for a way to consolidate .xlsx files but perhaps in a way which is not so rigid. The problem is that we receive a lot of spreadsheets from different institutions. We do send them templates, but of course they like to modify them slightly (defeating the point of a template in my opinion!). This makes it a nightmare when we try and consolidate them into one large list.
Is there a known method for doing this where there may be variations? In the example of a missing field, for example I would, ideally, like my reader to just ignore it, rather than throwing up an error to tell me that the number of columns is different. It would be fine to just keep that blank. Then I could just handle blank cells later down the line. If columns have slightly different names, I could just fix them in the input or merge the columns later.
I have tried doing a list files > table row to variable loop start > excel reader > concatenate (with a template file as the left table), but that doesn’t quite work.
This may be something which is common knowledge here, but I can’t quite get my head around other ways it may be possible. Other than opening 30+ excel files and manually checking all of the columns and their names and formats, of course! Thanks