Using Python script to refresh excel data connection on Alteryx server

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?

Workflow

Python

Connectors

Server

Sharepoint

Accepted answers

All comments

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

VijayAnaparthi

Anyone resolved this xlwings issue. If you are resolved then please let me know here.

FreeRangeDingo

I was able to refresh an Excel file using a Python script from my desktop but not from the Gallery. I went thru Alteryx support, and they told me it isn't possible because of how Excel is designed. They said the Excel task requires an active user session. When running on desktop, you get an active user session. But, for security, Alteryx Gallery only runs as a non-interactive session that does not allow for interactive processes. Therefore, it didn’t matter what package or code I used. It would not work, and you cannot perform this task from a workflow run from the Alteryx gallery.

I wrote about it here - https://www.bigmountainanalytics.com/automation-with-powerautomate-alteryx-powerbi-excel/.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703