Dynamic Tab Renaming with Python

[Ornelasya]

Hello!

May I get some help with a bit of Python code? Essentially, I would like to be able to provide a list that includes a file path, an old sheet name, and the name I would like that old sheet name to be changed to. These are for excel workbooks with multiple sheets and the contents of the sheets need to remain unchanged after the tabs are renamed. I've attached a sample scenario/WF below for clarity.

Thank you for your help!

[PhilipMannering · Accepted solution]

Hi at @Ornelasya 

I had a go at this. Think I have a solution, though I did have to change [Output File] to the full path, as the Jupyter Notebook runs from a temp location and it can't find the relative path. You could probably use the Directory Tool to get the full paths too.

See my example attached.

[PhilipMannering]

Oh, and the messages above saying the sheet does not exist, are because I already ran it once > it change the sheet names > then of course it can't find the old sheet names. I added a try except statement to print that message rather than have it just error.

Let me know if any questions. 

[Ornelasya]

Thanks @PhilipMannering I'm getting the following error. Any ideas?

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-3-d8318aad6b82> in <module>
      1 for i, (file, old_sheet, filename, new_sheet) in df.iterrows():
----> 2     wb = xl.load_workbook(file)
      3     try:
      4         ss = wb[old_sheet]
      5     except KeyError:

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    315     reader = ExcelReader(filename, read_only, keep_vba,
    316                         data_only, keep_links)
--> 317     reader.read()
    318     return reader.wb

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\openpyxl\reader\excel.py in read(self)
    280         self.read_theme()
    281         apply_stylesheet(self.archive, self.wb)
--> 282         self.read_worksheets()
    283         self.parser.assign_names()
    284         if not self.read_only:

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\openpyxl\reader\excel.py in read_worksheets(self)
    255             drawings = rels.find(SpreadsheetDrawing._rel_type)
    256             for rel in drawings:
--> 257                 charts, images = find_images(self.archive, rel.target)
    258                 for c in charts:
    259                     ws.add_chart(c, c.anchor)

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\openpyxl\reader\drawings.py in find_images(archive, path)
     50         if dep.Type == IMAGE_NS:
     51             try:
---> 52                 image = Image(BytesIO(archive.read(dep.target)))
     53             except OSError:
     54                 msg = "The image {0} will be removed because it cannot be read".format(dep.target)

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\zipfile.py in read(self, name, pwd)
   1473     def read(self, name, pwd=None):
   1474         """Return file bytes for name."""
-> 1475         with self.open(name, "r", pwd) as fp:
   1476             return fp.read()
   1477 

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\zipfile.py in open(self, name, mode, pwd, force_zip64)
   1512         else:
   1513             # Get info object for name
-> 1514             zinfo = self.getinfo(name)
   1515 
   1516         if mode == 'w':

c:\program files\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\zipfile.py in getinfo(self, name)
   1439         info = self.NameToInfo.get(name)
   1440         if info is None:
-> 1441             raise KeyError(   1442                 'There is no item named %r in the archive' % name)
   1443 

KeyError: "There is no item named 'xl/drawings/NULL' in the archive"

[PhilipMannering]

I'm afraid I'm not sure. Check the exact filepath exists and that it doesn't lead to a zipped file.

[flying008]

Hi, @Ornelasya

Maybe you have some Excel2003 file format like 123.xls in field [FilePath] ?

[Ornelasya]

@PhilipMannering I tried it with a different set a files/file paths and it worked like a charm. 

Thank you so much for your help! =)

[Ornelasya]

@flying008 Yes, it appears it was just an incorrect path on my part. I appreciate the input! =)

[Ornelasya]

@PhilipMannering 

Your code has been working like a dream for the past few months. However, I updated my Alteryx to Designer (2022.1.1) and get the following error message. Is there a module or something I need to download to get it up and running again? 

Thank you for your time. 

[PhilipMannering]

Looks like you're missing the module "openpyxl". Try running,

!pip install openpyxl

in the Python Tool (a jupyter notebook cell). You only need to do it once. If you have an admin version of Alteryx be sure to right click on the Alteryx Designer app icon and "Run as Administrator". This should solve your problem.