I am using the Python tool in Alteryx Designer Desktop to open a file, create a named range for that file, save the file, & close the file. The named range is used to import the file into OneStream (software we use for uploading and tracking account reconciliations). When I go to import the file after being manipulated by the Python tool, it is rejected by OneStream. However, when I create the named range manually in Excel (not using the Python tool in Alteryx), I can upload the file into OneStream without any issues. The strange thing is that the named range and scope are identical in both cases. Any ideas on what may be causing OneStream to not accept the file with the named range created by the Python tool? The two files with named ranges are attached. The Python code I'm using and OneStream error code are below.
Python code:
from ayx import Package
import os
from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName
# Define the directory path with the updated folder name
directory_path = '\\\\servername\\foldername'
# Loop over files in the directory
for filename in os.listdir(directory_path):
if 'abc' in filename and filename.endswith('.xlsx'): # Updated condition
# Construct the full file path
full_path = os.path.join(directory_path, filename)
# Load the Excel workbook
workbook = load_workbook(full_path)
# Access the "1.Reconciliation" worksheet
worksheet = workbook["1.Lead"]
# Define the range coordinates for the named range with absolute references
range_start = "$A$15"
range_end = "$O$29"
# Create the named range "xftRecon" for the worksheet and set the scope
named_range = DefinedName(
name="xftRecon",
attr_text=f'\'{worksheet.title}\'!{range_start}:{range_end}',
localSheetId=0, # Set the scope to 1.Lead
)
# Add the named range to the workbook
workbook.defined_names.add(named_range)
# Save the modified workbook
workbook.save(full_path)
OneStream error code:
.
Source code: line 0, method ExecuteRetries.
.
Source code: line 0, method ExecuteRetriesStartingWithPreferredAppServerIndex.
.
Source code: line 0, method TryExecuteAction.
.
Source code: line 0, method StartMethodUsingXFCallState.
.
Source code: line 0, method ExecuteSelectionChangedServerTask.
.
Source code: line 0, method ProcessSelectionChangedServerTask.
.
Source code: line 0, method ExecuteDashboardExtenderBusinessRule.
.
Source code: line 0, method Execute.
Unable to execute Business Rule 'RCM_ImportExportHelper'.
Source code: line 0, method Execute.
.
Source code: line 0, method Main.
.
Source code: line 0, method OnButtonClick.
.
Source code: line 0, method ImportDetailDoc.
.
Source code: line 0, method ReadExcelTableRangeContent.
.
Source code: line 0, method LoadExcelFile.
.
Source code: line 0, method GetTableRanges.
.
Source code: line 0, method CreateTableRangeContentFromXFSpreadsheetTable.
.
Source code: line 0, method SetTableFieldNames.
.
Source code: line 0, method GetValidFields.
.
Source code: line 0, method CreateDBConnInfoForTableRange.
.
Source code: line 0, method CreateExternalDbConnInfo.
External database '' was not found in the application server configuration settings.
Source code: line 0, method CreateExternalDbConnInfo.
User Interface Stack Trace:
Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at OneStream.Client.SharedUI.DashboardsAjaxServiceReference.DashboardsAjaxService.StartExecuteSelectionChangedServerTaskCompressed(SessionInfo si, Boolean isSystemLevel, Guid primaryDashboardID, Guid embeddedDashboardID, Guid componentID, PageInstanceInfo pageInstanceInfo, XFSelectionChangedServerTaskInfo serverTaskInfo)
at OneStream.Client.SharedUI.DashboardWcfHelper.ExecuteSelectionChangedServerTaskAsync(SessionInfo si, Boolean isSystemLevel, Guid primaryDashboardID, Guid embeddedDashboardID, Guid componentID, PageInstanceInfo pageInstanceInfo, XFSelectionChangedServerTaskInfo serverTaskInfo, OnAfterExecuteSelectionChangedServerTaskDelegate onAfterExecuteSelectionChangedServerTask, Object userState)
