Colleagues
I am trying run a Python query in Alteryx, and seem to be having a problem getting it to work. Not sure what I am doing wrong.
I changed my settings to Administrator, installed a package, and multiple other changes. Still cannot seem to get it to work without erroring. All I am trying to do is Open Excel, Refresh the data, and close. This was tested in Python 3.8 so I know it works, but just can't get it to work in Alteryx. Any ideas? If I replace win32com with pypiwin32 or pywin32, I get the same error. I even tried running as an Admin.
Thanks for any help you can provide.
# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Package
Package.installPackages(['pypiwin32'])
from ayx import Alteryx
import win32com.client
import shutil
filepath = Alteryx.read('#1')
SourcePathName = 'G:/Health Network Management\Medical Economics/BUDGET/2021 HEALTHCARE BUDGET/SUPPORTING SCHEDULES'
Filename= 'Budget Tables.xlsx'
# Open Excel
Application = win32com.client.Dispatch("Excel.Application")
# Show Excel. While this is not required, it can help with debugging
Application.Visible = 1
# Open Your Workbook
Workbook = Application.Workbooks.open(SourcePathName + '/' + Filename)
# Refesh All
Workbook.RefreshAll()
# Saves the Workbook
Workbook.Save()
# Closes Excel
Application.Quit()
This is the error I get:
-------------------------------
Collecting pypiwin32
WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),)': /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
WARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),)': /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
WARNING: Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),)': /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
WARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),)': /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
WARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),)': /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
ERROR: Could not install packages due to an EnvironmentError: HTTPSConnectionPool(host='files.pythonhosted.org', port=443): Max retries exceeded with url: /packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl (Caused by SSLError(SSLError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)'),))
---------------------------------------------------------------------------
CalledProcessError Traceback (most recent call last)
<ipython-input-1-088d2856dd66> in <module>
2 # script here (only missing packages will be installed)
3 from ayx import Package
----> 4 Package.installPackages(['pypiwin32'])
5 #Package.installPackages(['pandas','numpy'])
c:\users\_____\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Package.py in installPackages(package, install_type, debug)
200 print(pip_install_result["msg"])
201 if not pip_install_result["success"]:
--> 202 raise pip_install_result["err"]
c:\users\____\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\ayx\Utils.py in runSubprocess(args_list, debug)
118
119 try:
--> 120 result = subprocess.check_output(args_list, stderr=subprocess.STDOUT)
121 if debug:
122 print("[Subprocess success!]")
c:\users\____\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in check_output(timeout, *popenargs, **kwargs)
354
355 return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
--> 356 **kwargs).stdout 357
358
c:\users\_____\appdata\local\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\subprocess.py in run(input, timeout, check, *popenargs, **kwargs)
436 if check and retcode:
437 raise CalledProcessError(retcode, process.args,
--> 438 output=stdout, stderr=stderr) 439 return CompletedProcess(process.args, retcode, stdout, stderr)
440
CalledProcessError: Command '['c:\\users\\_____\\appdata\\local\\alteryx\\bin\\miniconda3\\envs\\jupytertool_venv\\python.exe', '-m', 'pip', 'install', 'pypiwin32']' returned non-zero exit status 1.
ModuleNotFoundError Traceback (most recent call last)
<ipython-input-2-b32cbdd5a3c0> in <module>
1 from ayx import Alteryx
----> 2 import win32com.client
3
4 import shutil
5 filepath = Alteryx.read('#1')
ModuleNotFoundError: No module named 'win32com'
NameError Traceback (most recent call last)
<ipython-input-3-5cec3146979b> in <module>
4
5 # Open Excel
----> 6 Application = win32com.client.Dispatch("Excel.Application")
7
8 # Show Excel. While this is not required, it can help with debugging
NameError: name 'win32com' is not defined
