Hello Martin,
Yes, I had considered that, but I decided to do it this way because some files were large, so I thought it would be better to process them one by one (even though, in the end, the Loop End concatenates them all together).
About the issue
It’s really difficult to simulate the error.
I also don’t know why it happens.
The most frustrating part is that I built this workflow for a friend, and she was running it but kept complaining about this error.
I tested it on my machine, and there was no error. Then, I exported it to her, and on her machine, the error appeared.
I went crazy.
But I ended up solving it with a simple strategy:
- I went to the Transformation tab in the Excel Reader node and changed everything to String. This way, the Excel Reader won’t throw an error due to column type issues.
- Before reaching the Loop End (but it could also be after it), I used the String to Number node.
In short, I treated everything as String first and only changed the Data Type after the Excel Reader had processed the files.
See the screenshot.
I believe the error was caused by the following scenario:
Imagine that in the first file, there is a column where the numbers are "0" or "1" to "9".
In this case, the Excel Reader will consider the column as Integer.
However, in the second file, imagine that the same column contains at least one value like "10.1".
Then, the Excel Reader will treat it as Double.
At this moment, I believe the column “jumps” to the end and triggers a Data Type error.
If I check the “Change table specifications” option in the Loop End, it wouldn’t be ideal because the columns that “jumped” to the end would stay there, changing the order of colums dinamic.
This isn’t what I want because of this issue—each file could be different depending on which column caused the error.
Another functionality I couldn’t find was an option in the Excel Reader node to not use the first row as a header.
If I could disable that, the columns would automatically be treated as String, eliminating the need to manually force it in the Transformation tab.
Since I couldn’t find such an option, I had to manually set everything to String and then convert it back with String to Number.
In the end, the result was satisfactory, and the error no longer occurred.
On the forum, I found a post from 2020 where someone complained about the same issue.
I’ll send it to you so you can check it out if you’re curious.
My strategy
All colums to String
Changing to Number after
Same problem