OLEDB, yay or nay?

[remko.bouwens]

In the past I had to find ways to work with large CSV/Excel files in Blue Prism, without an option to get/retrieve the data via other channels. From these files I only needed some specific data.
To better enable Blue Prism to cope with this data, I needed to filter before reading thousands of rows into memory.
To do this, I used OLEDB techniques to retrieve only the specific data using SQL query techniques.
OLEDB is easy to use and fast to implement.
Of course a bit more technical skills are required (SQL). But only using basic query's can help you a lot.

What are your thoughts about this?
Do more people have (positive of negative) experiences using this?

Interested in using OLEDB? Read the guide for more information.

#BPTechTips​

------------------------------
Remko Bouwens
RPA Specialist
Boels Rental
Europe/Amsterdam
------------------------------

[michaeloneil]

Hi Remko

I think it depends on the situation but OLEDB can be useful in situations requiring large datasets. I'm working on a project now where there are several database queries to be run which write to a spreadsheet but each query returns thousands of rows and once in a collection writing these to a spreadsheet is very slow. I'm changing this to run the query and directly write the returned data to a temp spreadsheet where I can just copy the range and paste into the working file without storing anything in a collection. Things like this are extremely useful with OLEDB but usually its faster and easier to use the standard excel actions if the process is using manageable data volumes.

Regards

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[EmersonF]

I also had some positive experiences with OLEDB, it helped me a lot to keep the speed even when it comes to large files, I could only harvest what interested me.

However I prefer to capture items directly from a database, it gives me more security and traceability

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
If my answer helped you? Mark as useful!
------------------------------

[PaulaSmolarek]

Definitely a yay for me but only for large datesets. I started using OLEDB when working with a file so big that it started to cause the excel VBO to time out.
I think the main advantage of OLEDB is that you can filter the data prior to fetching it into BP therefore you do not have to import the data obsolete from your process perspective.

------------------------------
Paula Smolarek
RPA Developer
Digital Workforce
Europe/Warsaw
------------------------------