Consolidating .xlsx with slight variations

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 :slight_smile:

1 Like

Hey @JWebb,

Have you tried enabling the “Support changing file schemas” option in the Excel Reader node? It’s under the “Advanced Settings” tab.





I honestly had no idea that was even there. And that, combined with unchecking “fail if specs differ” has actually worked! That’s excellent. Thank you very much. That will come in so useful in future!


@JWebb No problem, I’m glad it was the solution you were looking for :slight_smile:


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.