1

I have been working with excel documents lately, and having difficulty grasping "tables". I think some of the confusion stems from the fact that some online resources refer to an array of values in a worksheet as a table, whereas I am interested in dealing with a table object ("insert table" in excel).

From a previous question, I got the sense that "tables" are recognizable by openpyxl which appeared to be able to read one in and manipulate it as one would a csv etc. I am however encountering an error upon loading the worksheet as a variable in my script:

VBS_Metrics_Report_dir = "path to .xlsx file"
VBS_Metrics_Report = openpyxl.load_workbook(os.path.join(VBS_Metrics_Report_dir, "VBS_Metrics_Report.xlsx"))
VBS_Membership_ws = VBS_Metrics_Report['Membership']
VBS_Membership_table = VBS_Membership_ws.tables['Membership']

---> 192 VBS_Metrics_Report = openpyxl.load_workbook(os.path.join(VBS_Metrics_Report_dir, "VBS_Metrics_Report.xlsx"))

ValueError: Table with name Membership already exists

First, I am confused as the line in which the error is said to be does not even attempt to access the table nor the sheet named "Membership" yet, that occurs in the following lines.

Second, where does this error claim the table to already exist? considering that the error occurs even after restarting the python kernel, it can't be that it already exists as a variable in my workspace. Even if it did, I should be able to overwrite a variable no?

If instead the problem is that the table already exists in the workbook I am trying to access, then yes, of course it already exists, that is why I am trying to access it. In order to make use of the data that I know it contains. In this case I must be misunderstanding the usage of the load_workbook function.

I realize this question has essentially been asked here, but it has not really been answered and I though I would add some thoughts.

If anyone can help me solve this problem OR has insight as to how handling excel "Tables" works to give me a better understanding, please let me know. It would be hugely appreciated!

Thanks.

Improve this question
8
  • What happens when you open the file manually in Excel? Is the file generated programmatically to begin with? Commented Oct 24, 2023 at 17:57
  • @BigBen Thanks for the reply. I created the file manually. The idea is to maintain it programmatically for routine maintenance but also maintain the ability to make manual edits. When I open the file now I am met with a message which says "we encountered a problem with this file, would you like to restore it" or something like that. It does successfully restore though. Later on in my script I do try and write a new table overtop of the existing table which is what I thought was creating that message. Commented Oct 24, 2023 at 18:45
  • It seems like that step (writing a new table) corrupted your file. Commented Oct 24, 2023 at 18:51
  • Not really possible to say much more without a file but table names are restricted within a workbook: §18.5.1.2: displayName __This name shall not have any spaces in it, and it shall be unique amongst all other displayNamesanddefinedNamesintheworkbook. __ Commented Oct 24, 2023 at 19:34
  • @CharlieClark huh ok so you think it is possible that because I have a worksheet and a table within the document called "Membership" that could be causing the issue then? Commented Oct 24, 2023 at 20:45

1 Answer 1

Reset to default
1

This is some additional information on the cause of the error that might help.

The ValueError: "Table with name Membership already exists" is generated in the 'add_table()' function in the Openpyxl worksheet file. i.e. the error text only exists in that function so can only occur if that function is called.

This function 'add_table()' is called when you add a table to a sheet in your code for example create the table 'TABLE1' then add to the sheet VBS_Membership_ws.table_add(TABLE1).
However, it will also be called by load_workbook if the workbook contains any sheet with at least one table.
On 'load_workbook' the code will check each sheet in the workbook and if a table exists calls ws.add_table(table) for each Sheet, where table is the name of the table.
This is why the error is raised on the code line VBS_Metrics_Report = openpyxl.load_workbook(os.path.join(VBS_Metrics_Report_dir, "VBS_Metrics_Report.xlsx"))

When 'add_table' is called it will run a duplicate table name check and if this returns True the ValueError is raised.
worksheet.py

def add_table(self, table):
    ...
    if self.parent._duplicate_name(table.name):
        raise ValueError("Table with name {0} already exists".format(table.name))

In the _duplicate_name function the test is simply;
workbook.py

if name == t.lower():

where
'name' is the name of the table to be checked
't' is the name of an existing table
Both 'name' and 't' are set to lowercase so that the check is case insensitive.

There is no check here against the Sheet Name (Title) so having a Table with the same name as the Sheet will not return True and raise the error.

The 't' list appears cumulative as in as each table name is checked its added to the 't' list to be checked against the next table name.

For example;
If I had 3 tables on a Sheet named 'Table1', 'Table2' and 'Table3' then

  1. 'Table1'; t is empty so it is checked against nothing, 'Table1 is added to the list and 't' now contains 'Table1'
  2. 'Table2'; Is checked against 'Table1' and then 'Table2' is added to the list so 't' now contains 'Table1' and 'Table2'
  3. 'Table3'; Is checked against 'Table1' and 'Table2'

    Note the 't' list cumulation will be across all sheets since the unique naming is across the workbook. So if there was another Sheet with a table then 't' would contain the three table names for the next Sheet where the first table would be checked against 'Table1', 'Table2' and 'Table3'.

Therefore I can only see the error occurring if in fact two tables are named the same which is not possible to create in Excel or in Openpyxl given this check. However if the Excel file is manually modified or using an app that does not check names before updating perhaps this might happen.

However ultimately as mentioned without the actual workbook itself it's difficult to determine exactly what is happening but you can debug the code yourself to see what is occurring at these points.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hmm incredibly helpful thank you, I think what likely happened then, in light of this information, is that later in the script I attempted to overwrite the existing table with a new version of it VBS_Membership._tables[0] = resTable. Though I attempted to write the table overtop (in the same cells) as the original, I guess this did not actually delete it. Can I edit my question to reflect this?
This was indeed the case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.