Validation list with ODBC and Excel

[jakub.markowski93]

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
------------------------------

[Denis__Dennehy]

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.

[michaeloneil]

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
------------------------------

[BenLyons · Accepted solution]

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
------------------------------