Convert .xls, .xlsm and .xlsb into .xlsx and parse the input

[#1]

As we all know, there are some challenges when dealling with .xls/.xlsm/.xlsb files as input.

Because of this, i built this macro that automatically convert .xls/.xlsm/.xlsb into .xlsx and parse the data.

This macro uses the python component with the libraries openpyxl, xlrd and pyxlsb.

OBS: The python libraries were added as assets on the package.

[Richard_Data]

The alteryx version has an issue with the naming of the designer.venv and should instead be named Jupyter venv now.

[Bargeton]

Hello,

On my desktop it's working well.

But deploying on server I had issue... (see error log copy bellow).

Note that on both (desktop and server) I have only the builtin python installed.

Regards,

Alex

00:00:54.273 - Error - ToolId 6: Tool #3: ---------------------------------------------------------------------------
com_error Traceback (most recent call last)
d:\apps\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\dynamic.py in _GetGoodDispatch(IDispatch, clsctx)
88 try:
---> 89 IDispatch = pythoncom.connect(IDispatch)
90 except pythoncom.ole_error:
com_error: (-2147221005, 'Invalid class string', None, None)

During handling of the above exception, another exception occurred:
com_error Traceback (most recent call last)
<ipython-input-4-04ef72a74d7b> in <module>
17 while (rowcount<max_rows):
18 print(str(rowcount) + " " + df.iloc[rowcount, df.columns.get_loc('FullPath')])
---> 19 save_xls_as_xlsx(df.iloc[rowcount, df.columns.get_loc('FullPath')])
20 rowcount = rowcount + 1
<ipython-input-4-04ef72a74d7b> in save_xls_as_xlsx(path)
3 def save_xls_as_xlsx(path):
4 # Opening Excel application
----> 5 excel = win32.gencache.EnsureDispatch('Excel.Application')
6 wb = excel.Workbooks.Open(path)
7
d:\apps\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in EnsureDispatch(prog_id, bForDemand)
525 def EnsureDispatch(prog_id, bForDemand = 1): # New fn, so we default the new demand feature to on!
526 """Given a COM prog_id, return an object that is using makepy support, building if necessary"""
--> 527 disp = win32com.client.Dispatch(prog_id)
528 if not disp.__dict__.get("CLSID"): # Eeek - no makepy support - try and build it.
529 try:
d:\apps\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\__init__.py in Dispatch(dispatch, userName, resultCLSID, typeinfo, UnicodeToString, clsctx)
93 """
94 assert UnicodeToString is None, "this is deprecated and will go away"
---> 95 dispatch, userName = dynamic._GetGoodDispatchAndUserName(dispatch,userName,clsctx)
96 return __WrapDispatch(dispatch, userName, resultCLSID, typeinfo, clsctx=clsctx)
97
d:\apps\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\dynamic.py in _GetGoodDispatchAndUserName(IDispatch, userName, clsctx)
112 else:
113 userName = str(userName)
--> 114 return (_GetGoodDispatch(IDispatch, clsctx), userName)
115
116 def _GetDescInvokeType(entry, invoke_type):
d:\apps\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\dynamic.py in _GetGoodDispatch(IDispatch, clsctx)
89 IDispatch = pythoncom.connect(IDispatch)
90 except pythoncom.ole_error:
---> 91 IDispatch = pythoncom.CoCreateInstance(IDispatch, None, clsctx, pythoncom.IID_IDispatch)
92 else:
93 # may already be a wrapped class.
com_error: (-2147221005, 'Invalid class string', None, None)

[ravi061]

Hi @Felipe_Ribeir0,

I am trying to run your macro in my designer, it is erroring, error indicates something python related, Even i have installed python and jupyter notebook.

Are there any Pre-Requisites before using your macro?

Thanks

[Felipe_Ribeir0]

Hi @ravi061 

There are no pre-requisites other than the normal that the installation of alteryx already provide. But the alteryx python tool is tricky sometimes, some errors can happen in some machines and not in others.

Could you add a screenshot of the error message here? Thanks

[ravi061]

Hi @Felipe_Ribeir0,

I got the below error which is in logs

"

00:00:07.793 - Error - ToolId 10: Tool #3: ---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-4-14b035833aa4> in <module>
     29 while (rowcount<max_rows):
     30     print(str(rowcount) + " " + df.iloc[rowcount, df.columns.get_loc('FullPath')])
---> 31     save_xls_as_xlsx(df.iloc[rowcount, df.columns.get_loc('FullPath')])
     32     rowcount = rowcount + 1
<ipython-input-4-14b035833aa4> in save_xls_as_xlsx(path)
     11     close_excel()
     12     # Opening Excel application
---> 13     excel = win32.gencache.EnsureDispatch('Excel.Application')
     14     wb = excel.Workbooks.Open(path)
     15
c:\users\acook\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in EnsureDispatch(prog_id, bForDemand)
    532                         tlb, index = ti.GetContainingTypeLib()
    533                         tla = tlb.GetLibAttr()
--> 534                         mod = EnsureModule(tla[0], tla[1], tla[3], tla[4], bForDemand=bForDemand)
    535                         GetModuleForCLSID(disp_clsid)
    536                         # Get the class from the module.
c:\users\acook\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in EnsureModule(typelibCLSID, lcid, major, minor, progressInstance, bValidateFile, bForDemand, bBuildHidden)
    389         try:
    390                 try:
--> 391                         module = GetModuleForTypelib(typelibCLSID, lcid, major, minor)
    392                 except ImportError:
    393                         # If we get an ImportError
c:\users\acook\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in GetModuleForTypelib(typelibCLSID, lcid, major, minor)
    264         # module to our cache though - check that here.
    265         if "_in_gencache_" not in mod.__dict__:
--> 266                 AddModuleToCache(typelibCLSID, lcid, major, minor)
    267                 assert "_in_gencache_" in mod.__dict__
    268         return mod
c:\users\acook\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in AddModuleToCache(typelibclsid, lcid, major, minor, verbose, bFlushNow)
    550         # module - this doesn't mean anything special though!
    551         mod._in_gencache_ = 1
--> 552         dict = mod.CLSIDToClassMap
    553         info = str(typelibclsid), lcid, major, minor
    554         for clsid, cls in dict.items():
AttributeError: module 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'CLSIDToClassMap'
00:00:07.793 - ToolId 11: 0 iterations were run.

"

Thanks for helping...

[ravi061]

Hi @Felipe_Ribeir0 ,

Do you know anything about above error?