topic RE: Validation list with ODBC and Excel in Product Forum

Validation list with ODBC and Excel

jakub.markowski93 — Wed, 29 Nov 2023 13:29:00 GMT

Hi,

I see in Batch types there is a setting called Validation lists which allows to use Odbc to connect to database. In my case I want to use Excel file. I use following connection string:

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=C:\Users\JMarkowski\Desktop\test.xlsx;

When I test connection, it says it's successful, however when I go to document form definition I don't see there any data.

Does anyone have experience with using Excel file for creating Validation list and Dynamic list?

------------------------------
Jakub Markowski
------------------------------

RE: Validation list with ODBC and Excel

Denis__Dennehy — Tue, 05 Dec 2023 11:56:00 GMT

I've never used validation lists (not used the BP product for about 6 years) - but have used OLEDB to interface with Excel a number of times.
The way that I did it that always worked was to use the Data - OLEDB interface object (it is available on the digital exchange) and follow the old OLEDB guide that can be found here: Blue Prism - Data OLEDB

If BP have now built OLEDB into the core product I am not sure why, there was a perfectly easy to use way of doing it with the object and lots of more useful things on the product backlog that have been outstanding for years.

RE: Validation list with ODBC and Excel

michaeloneil — Tue, 05 Dec 2023 15:10:00 GMT

Hi @Jakub Markowski

Try changing your connection to string to Provider=Microsoft.ACE.OLEDB.12.0;Data Source=+ File_Path +;Extended Properties='Excel 12.0;HDR=YES;'

I havent used validation lists in BP before but thats how i would normally connect to an excel file using OLEDB.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Validation list with ODBC and Excel

Ben.Lyons1 — Tue, 05 Dec 2023 15:12:00 GMT

Hi All,

Decipher doesn't currently support the use of OLEDB to connect to an Excel or csv file.

The ODBC connector is for connecting to databases only, at this time.

Thanks

------------------------------
Ben Lyons
Senior Product Specialist - Decipher
SS&C Blue Prism
UK based
------------------------------