Excel Reader - How to solve: the following columns are not contained in excel?

[Felipereis50]

Hi friends

I didn’t find a solution to pass the error of reading excel files with the node “excel reader” with the configuration of “files in folder”.

I create a test to simulate error on the second file.
The following columns are not contained in all source files: [Col4]

I would like to know: Is there a proper/clever way to solve this problem?

Imagine that I have like 50 files to read and some then, do name of columns change (slight) e.g. One is Col4, the other is could be Column4.

Is there a way to append, and put that column left?

I tried various ways: maybe I will need to know all the changes and create a dictionary of all columns. But to do so, I’ll need to read the files.

the following columns are not contained in excel.knwf (92.6 KB)

[MartinDDDD]

Hey there,

Not on a computer right now but think this thread will help you solve it. Long story short: there’s a setting to use geheuer column headers (A,B,C…) instead of names of data in first row of a tab / sheet…

Excel File Reader - column header

There is a setting under Data Area tab - can you check that it is checked and set to the correct row that has the headers in it? [image]

[PBJ]

Only use the Excel File Reader with theses settings:
Files selection:

File layout change:

[takbb]

Hi @Felipereis50,

As has been noted, the Excel Reader can be configured to help handle this problem for some use cases, but whether it works for you also depends on whether the order of the columns is always the same. If in one file a “middle” column is completely skipped, then having columns returned with generic names such as A, B, C… or “column1”,“column2”, “column3” wont put the data in the right place for all files.

Extending your uploaded workflow, which uses the loop, you can incorporate the Column Renamer (Dictionary) to adjust any column names that vary, and it won’t matter where they are positionally.

I have modified your test files and added two more:
file1.xlsx

file2.xlsx

file3.xlsx

file4.xlsx

Column dictionary

Output:

Make sure that in the Column Renamer (Dictionary) you don’t have it fail if a column is not present in the dictionary table. This way you only have to perform renaming that are known to be needed rather than specifying every column.

On the Loop End, make sure it allows changing table specification to allow for a varying number of columns

the following columns are not contained in excel - takbb.knwf (154.3 KB)

[Felipereis50]

Guys,

I’m sorry for the delay in responding. I was very busy all week. I had read all the messages, but I wanted to reply after the final analysis.

@MartinDDDD
@PBJ

Thank you very much for the information. I had never noticed the option for columns A B C. That already helps a lot. Thank you so much.

I’ve made all my notes.

@takbb You’re amazing.

And regarding your explanation: “If in one file a ‘middle’ column is completely skipped, then having columns returned with generic names such as A, B, C… or ‘column1’, ‘column2’, ‘column3’ won’t put the data in the right place for all files,” I completely understand, and it’s a problem I would indeed face. The issue is that the information from one column gets concatenated with another column, but the information is different and needs to be in separate columns.

Regarding your explanation for: not flag “fail if no assignment in dictionary table,” very interesting. Now I understand what it’s for.

Regarding your explanation for Loop end: “Allow changing table specifications,” very, very interesting indeed. Now I get it. I’ve always had problems with Loop end when the structure changes before reaching the loop end.

Thank you, everyone.

Excellent explanations.

You are awesome.