Convert .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 convert .xls 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.

[JokeFun]

What shall I do as there is the Error: Python (3): Failed to find virtual env named: "designerbasetools_venv"

[Felipe_Ribeir0]

Hi @JokeFun 

Inside your python tool

1)Click on Kernel

2)Click on change Kernel

3)Select the Kernel that is available for you (could be something like jupytertool_venv)

4)Save the workflow

5)Try to run again

[JokeFun]

@Felipe_Ribeir0 Thanks for the update. I think I have to wait for my company to upgrade the Alteryx version before I can try on this.

[Hamder83]

What is the xlsb has more sheets, can I define that? 


I get this error using the macro:

MC_CONVERT_XLS_INTO_XLSX ("2)	Tool #3":"---------------------------------------------------------------------------¶com_error                                 Traceback (most recent call last)¶<ipython-input-4-da50bea6407a> 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-da50bea6407a> in save_xls_as_xlsx(path)¶      4     # Opening Excel application¶      5     excel = win32.gencache.EnsureDispatch("Excel.Application"")¶----> 6     wb = excel.Workbooks.Open(path)¶      7 ¶      8     # Rename path with .xls + x¶C":\Users\DENNIS~1.SCH\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x9\Workbooks.py in Open(self,
"Filename",
"UpdateLinks",
"ReadOnly",
"Format",
"Password",
"WriteResPassword",
"IgnoreReadOnlyRecommended",
"Origin",
"Delimiter",
"Editable",
"Notify",
"Converter",
"AddToMru",
"Local",
CorruptLoad)¶     73,
"Editable=defaultNamedOptArg",
"Notify=defaultNamedOptArg",
"Converter=defaultNamedOptArg",
"AddToMru=defaultNamedOptArg",
Local=defaultNamedOptArg¶     74,
"CorruptLoad=defaultNamedOptArg)":¶---> 75 		ret = self._oleobj_.InvokeTypes(1923,
"LCID",
1,
(13,
0),
((8,
1),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17),
(12,
17)),
Filename¶     76,
"UpdateLinks",
"ReadOnly",
"Format",
"Password",
WriteResPassword¶     77,
"IgnoreReadOnlyRecommended",
"Origin",
"Delimiter",
"Editable",
"Notify¶com_error":(-2147352567,
"Exception occurred.",
(0,
"Microsoft Excel",
"Open method of Workbooks class failed",
"xlmain11.chm",
0,
-2146827284),
"None)¶"

[jpscoralick]

@Felipe_Ribeir0 thanks a lot for this macro. It solved an old problem of mine. Valeu!!!

[arikachopra]

Error: MC_CONVERT_XLS_INTO_XLSX (2): Tool #3: ---------------------------------------------------------------------------
com_error Traceback (most recent call last)
c:\users\612895346\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in EnsureDispatch(prog_id, bForDemand)
529 try:
--> 530 ti = disp._oleobj_.GetTypeInfo()
531 disp_clsid = ti.GetTypeAttr()[0]
com_error: (-2147418111, 'Call was rejected by callee.', None, None)

During handling of the above exception, another exception occurred:
TypeError Traceback (most recent call last)
<ipython-input-5-ef6ef37a5915> in <module>
6 while (rowcount<max_rows):
7 print(str(rowcount) + " " + df.iloc[rowcount, df.columns.get_loc('FullPath')])
----> 8 save_xlsb_as_xlsx(df.iloc[rowcount, df.columns.get_loc('FullPath')])
9 rowcount = rowcount + 1
<ipython-input-4-55d254879232> in save_xlsb_as_xlsx(path)
10 def save_xlsb_as_xlsx(path):
11 # Opening Excel application
---> 12 excel = win32.gencache.EnsureDispatch('Excel.Application')
13 wb = excel.Workbooks.Open(path)
14
c:\users\612895346\appdata\local\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\gencache.py in EnsureDispatch(prog_id, bForDemand)
539 disp = disp_class(disp._oleobj_)
540 except pythoncom.com_error:
--> 541 raise TypeError("This COM object can not automate the makepy process - please run makepy manually for this object")
542 return disp
543
TypeError: This COM object can not automate the makepy process - please run makepy manually for this object

[arikachopra]

I got the above error. How to rectify this?

[Mbouamra]

yes same I got the above error too 

[Felipe_Ribeir0]

Hi @Mbouamra and @arikachopra 

Unfortunately i dont know what is causing this issue on your machine, so the only things that i can suggest without access to the machine to make some tests are the things that chatgpt told me: 

1. Ensure Excel is installed: Make sure that Microsoft Excel is installed on your colleague's machine. Without Excel, the code won't be able to automate Excel operations.

2. Check COM permissions: Ensure that the COM permissions are properly set up on your colleague's machine to allow automation of Excel. This may involve checking settings in DCOM Configurations.

3. Reinstall Excel and Alteryx/Python libraries: If Excel is installed but the issue persists, try reinstalling both Excel and the Python libraries (pywin32 or pypiwin32) on your colleague's machine.

4. Run Alteryx as Administrator: Try running the code with elevated privileges (Run as Administrator) to see if it resolves any permission-related issues.

5. Check for conflicting software: Ensure that there are no conflicting software installations or antivirus programs that might be interfering with the automation process.

6. Update Python and Libraries: Ensure that Python and all relevant libraries (pandas, pywin32) are up to date on your colleague's machine.

If this do not work, i would consider the use of another aproach. Another python library that i know that is capable of doing this is openpyxl.