Using Python script to refresh excel data connection on Alteryx server

[Jes]

In order to automate data sourcing, I have an excel file that is connected to an internal Sharepoint list that updates with new data when the data connection is refreshed. The connection to the Sharepoint list is authenticated by using my Windows login. (Had to use excel file as an intermediary, because I couldn't figure out how to connect to Sharepoint list directly from Alteryx)

I want this excel file to refresh every day at 8am and am looking to automate this.

I wrote Python script using XLwings library to use API connection to refresh the excel data connection, as seen below. 

The Python script runs fine on my local device but when I load the code into Alteryx server using the python tool, the workflow errors out when it comes to reading the excel file.

Below is the python script.

Anyone have success using python to refresh excel data connections in Alteryx server? 

[Thableaus]

Hi @Jes 

Could you provide more info on the error message you're seeing and how you're implementing this script?

Is it a workflow with a single python tool?

Cheers,

[Jes]

@Thableaus 

Yes, single python tool & below is the error message:

[Thableaus]

@Jes 

Would you happen to know if you have the library versions of XLwings match in both environments?

The error message is kinda hard to read but I'd investigate further. Maybe try to run the workflow using Designer inside the Server environment (or ask the Server admin to do that).

Cheers,

[Jes]

@Thableaus 

Sorry for that. 

This is the error message:

--------------------------------------------------------------------------- com_error Traceback (most recent call last) <ipython-input-1-b23b9c364551> in <module> 1 import xlwings as xw ----> 2 wb = xw.Book(r"Connection Sharepoint List Sample.xlsx") 3 wb.api.RefreshAll() d:\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\xlwings\main.py in __init__(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load, impl) 538 if len(candidates) == 0: 539 if not app: --> 540 app = App(add_book=False) 541 impl = app.books.open(fullname, update_links, read_only, format, password, write_res_password, 542 ignore_read_only_recommended, origin, delimiter, editable, notify, converter, d:\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\xlwings\main.py in __init__(self, visible, spec, add_book, impl) 208 def __init__(self, visible=None, spec=None, add_book=True, impl=None): 209 if impl is None: --> 210 self.impl = xlplatform.App(spec=spec, add_book=add_book, visible=visible) 211 if visible or visible is None: 212 self.visible = True d:\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\xlwings\_xlwindows.py in __init__(self, spec, add_book, xl, visible) 311 if xl is None: 312 # new instance --> 313 self._xl = COMRetryObjectWrapper(DispatchEx('Excel.Application')) 314 if add_book: 315 self._xl.Workbooks.Add() d:\alteryx\bin\miniconda3\envs\designerbasetools_venv\lib\site-packages\win32com\client\__init__.py in DispatchEx(clsid, machine, userName, resultCLSID, typeinfo, UnicodeToString, clsctx) 111 serverInfo = (machine,) 112 if userName is None: userName = clsid --> 113 dispatch = pythoncom.CoCreateInstanceEx(clsid, None, clsctx, serverInfo, (pythoncom.IID_IDispatch,))[0] 114 return Dispatch(dispatch, userName, resultCLSID, typeinfo, clsctx=clsctx) 115 com_error: (-2147221005, 'Invalid class string', None, None)

When you said "try to run the workflow using Designer inside the Server environment " --> wondering what this means? As in align Alteryx server's package version with my local python package version?

[Thableaus]

Hey @Jes 

That's correct. The error seems to be related with the XLwings library, so they might be out of sync.

Cheers,

[danilang]

Hi @Jes 

Depending on the version of Alteryx that you're using, you should be able to directly connect to the list using the SharePoint List Input tool(Pre 2021.3) or the SharePoint Files Input (2021.4 or higher).   This may be easier than trying the diagnose your python library issue.

If you have a specific issue with either SharePoint tool, lets us know here. 

Dan

[FreeRangeDingo]

I am using this same script/method and am getting the error that my file isn't found.  I've checked the file path, and what I copy into the script I can put into Windows Explorer and the file opens, so I know it's there and the path is correct.  Is there anything specific to the syntax for how the file needs to be specified?  I am connected to a file on our network.  I am not using a mapped drive.  I am using the full network path and starting the network path with "\\".  I am using single quotes around the file path....something like this.

wb = xw.Book('\\network\network folders\subfolders\filename.xlsx')

Thanks,

[FreeRangeDingo]

A coworker showed me that I was missing an "r".  I was using a file path like this...

'\\location\folder\subfolder\file.xlsx'

I needed to do this to get python to read the "\\" correctly.

r'\\location\folder\subfolder\file.xlsx'

[FreeRangeDingo]

I get the same error when running on server.  I'm working with our admin to run it in the server environment and to check the package version.  Was there ever a resolution on this??? @Jes