Read Excel Tables

[cmor]

Hello!

how can I read tables from Excel files? I mean formatted tables (the usual CTRL+T).
I have a bunch of Excel files, from which I should read a single table, which has the same exact name in all files.
Is there a connector that allows that?

Thanks!

[ipazin]

Hello @cmor,

welcome to KNIME Community!

To my knowledge this is not possible with KNIME native nodes. Probably Python can be used as a workaround. See this topic for example on how to write Table to Excel:

Write KNIME table to Excel as table (proposal)

OK I think now I get what you want. Yes it is possible to first save a table as a ‘pure’ excel file and then define a range as a table object “All” which you can later use to refer to it. In my example the no of lines that the object would have is assigned dynamically while the columns are fixed but one might also be able to change that to a dynamic setting. [image] After the ‘raw’ KNIME export: [image] After openpyxl got to work: [image] (cf. https://openpyxl.readthedocs.io/en/stable/…

Br,
Ivan

[mlauber71]

We had a recent discussion about this. Here is an example where you would identify tables by a keyword and then import them. You might have adapt that to your current format and see if there are regularities in the data that you can identify and use.

Loop on the basis of single Excel sheet data

OK it was slightly more complicated than I initially thought. But I hope it does work now: give each line a unique ID to identify them later identify the ‘heads’ of the tables (“FORMNAME”) like you already did use moving aggregate to give the heads unique numbers bring these numbers back to the data fill the intermediate (empty) cells with the head’s number use a group loop on the blocks identified by the head numbers extract the first line as column names make sure empty columns that would ju…

@ipazin is refering to the possibility of creating Excel-Tables with the help of Python.

[Daniel_Weikert]

What about reading the whole sheet and remove missing rows and columns?

[cmor]

Thank you all for your replies!
@ipazin Thank you! I would hope for a quicker solution for such a simple task though.
@Daniel_Weikert and @mlauber71 I think this is the right direction! I cannot remove the empty rows below, because they’re not empty (meaning I have a table above and another table below).
However, the table I need occupies the same cell range in all files, always in the first sheet.

I can definitely exploit this! I guess I must just learn how to store the first sheet name as a variable to be able to read from the first sheet no matter the name (the first sheet’s name is not always the same in all files).

It wouldn’t be bad to be able to exploit the fact that tables are named ranges though…that would make everything way faster!

[mlauber71]

@cmor I am notexactly sure what you mean by ‘simple task’ - if you know where your table is and know the line of the head you could just tell the Excel Reader what to read

If you want to identify the formatted tables you might have to resort to Python

openpyxl read tables from existing data book example?

Access Excel Tables with Python

---

If you want to explore further options how to deal with Excel (import) I have a collection for that prepared. The identification of formatted tables would be a new feature for sure

Import EXCEL - a meta workflow trying to answer questions about importing...

And then if you could provide us with examples that cover your whole challenge one might be able to further explore possible solutions.

[cmor]

Thank you very much for your quick and detailed reply!

I’m indeed using the Excel reader in the end

I’ll try to provide more detail:

I need to read several excel files from a folder. (Excel reader is fine here)
I need to read the same (one-row + header) table from each file, irrespective in which sheet it is (here is Excel reader no longer enough, because it doesn’t allow me to identify a named range)

Since the table I need is always in the first sheet and always occupies the same range of cells, I can use the Excel reader, choosing the first sheet option.
(Note that I’m just lucky here, because technically the table could be in any other sheet after the first one)

All I need to do now is learn how to store the first sheet name as a variable, so the loop doesn’t fail as soon as it finds an Excel file that has a different name for the first sheet.

[ipazin]

Hello @cmor,

@cmor

All I need to do now is learn how to store the first sheet name as a variable, so the loop doesn’t fail as soon as it finds an Excel file that has a different name for the first sheet.

Don’t understand this cause Excel Reader will not fail if configured to always read first sheet.

Also seems to me you can use option Read files in folder in order to avoid loop. (It will work only if you have same structure in each file)

Br,
Ivan

[cmor]

Hello @ipazin!
I though so too and that was exactly my first try. However, it throws an error as soon as it gets to the second file, saying it cannot find a sheet named like the first sheet of the first file

Note that the table I need occupies the same range of cells, but not always in a sheet with the same name.

That’s why I thought storing the sheet names may solve the issue…

[mlauber71]

You could list the sheets of all excel files and proceed only those that match criteria

Read an XLS file – scottF

In this example also all sheets are listed and you can adapt how the files should be processed

m_001_import_r_excel_unite_fields – mlauber71

[Daniel_Weikert]

the read files option also does not work if the datatype for a column is different. So I think the loop is still the better option

[ipazin]

Hello @cmor,

ok. I see now. As @mlauber71 pointed out you can use Read Excel Sheet Names (XLS) node to get sheet names and then Row Filter to leave only first row which has the name of first sheet. Use Table Row to Variable node and control sheet name with newly created flow variable.

@Daniel_Weikert you are right, currently to use that option one must be sure that data structure is the same.

Br,
Ivan