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?
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.
@ipazin is refering to the possibility of creating Excel-Tables with the help of Python.
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!
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.
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…