ODBC Error: Excel Driver Requires User Input

[Todd Johnson 2]

To enable writeback to a file I am using the techniques shared here:

https://community.spotfire.com/s/article/Write-back-to-Excel-file-from-TIBCO-Spotfire-using-IronPython-via-ODBC

After setting up a System DSN (Excel Driver version 16.00.4999.1000) and configuring the script, I get the following error:

Exception: Failed to execute data source query for data source "".

Spotfire.Dxp.Data.Exceptions.ImportException: Failed to execute data source query for data source "". ---> Spotfire.Dxp.Data.Exceptions.ImportException: Failed to execute data source query for data source "". ---> System.InvalidOperationException: User input is need

The problem seems to be in this line of code:

dbSettings=DatabaseDataSourceSettings("System.Data.Odbc","DSN=myFile",sql)

Whether I use a valid DSN name or a non-existent DSN name, I get the same error. Is there an alternative connection string I should be using?

[Todd Johnson 2]

Update: I am able to connect to the ODBC DSN (myFile) from Excel VBA with an ADODB connection. The DSN is also visible in Windows registry at ComputerHKEY_LOCAL_MACHINESOFTWAREODBCODBC.INImyFile

Running 64-bit Windows 10 with 32-bit Office and 32-bit ODBC data sources.

Why is it not recognized by Spotfire?

[Fredrik Rosell]

Hello!

In case this issue is still unresolved, this - "Running 64-bit Windows 10 with 32-bit Office and 32-bit ODBC data sources." - is likely the source of the problem.

As documented in KB article "How to access 32-bit data files with 64-bit Spotfire on a 64-bit operating system" (https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-43035):

"TIBCO Spotfire now handles these connections "out-of-process." This means that the primary 64-bit Spotfire process launches a separate 32-bit process to handle these connections. This allows connections to Access, Excel and SAS files via the following method:
 
 
File > Add data table > Add > File > .accdb file
 
 
The connection via locally installed Data Providers (ODBC, OLE DB, etc.) is not implemented for out-of-process handling, and therefore does not allow you to use locally installed 32-bit Data Providers on 64-bit OS's with a 64-bit Spotfire instance, which would be performed with the following method: 
 
 
File > Add data table > Add > Other > Database > ODBC Data Provider, OLE DB Data Provider, etc."

So, I would try using a file data source instead, as is e.g. used in the following community script example for importing data from Excel: https://community.spotfire.com/s/article/How-to-import-multiple-Excel-spreadsheets-in-TIBCO-Spotfire-Using-IronPython-Scripting

Summarized example:

ds = Document.Data.CreateFileDataSource(filename)

myNewTable = Document.Data.Tables.Add(entry,ds)

Best Regards

Fredrik