topic Excel as Database in Product Forum

Excel as Database

BhawanaDaultani — Wed, 20 Oct 2021 09:47:00 GMT

Dear All,

I am trying to connect excel as database so that in case of bulk volume, it does not take much time.
I have ODBC driver installed in my machine.
Can you please help me which VBO should I use along with connection string and provider details.

TIA.

------------------------------
Bhawana Daultani
------------------------------

RE: Excel as Database

Denis__Dennehy — Wed, 20 Oct 2021 10:54:00 GMT

https://digitalexchange.blueprism.com/dx/entry/3439/solution/data---oledb

------------------------------
Denis Dennehy
Head of Professional Services, EMEA
Blue Prism Ltd
Europe/London
------------------------------

RE: Excel as Database

michaeloneil — Wed, 20 Oct 2021 11:03:00 GMT

Hi Bhawana

The vbo that Denis has provided the link to would do what you need it to but if you still need the connection string details I've got them below, please note the File_Path is the input variable for the action.

System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + File_Path + "';Extended Properties='Excel 12.0;HDR=YES;';")

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

RE: Excel as Database

EmersonF — Wed, 20 Oct 2021 12:20:00 GMT

I don't recommend using Excel as a DB, but if you don't have a choice, I believe that Michael's solution will work well, I already needed to use it and this works for me

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: Excel as Database

BhawanaDaultani — Wed, 20 Oct 2021 12:24:00 GMT

Thank you Denis.

------------------------------
Bhawana Daultani
------------------------------

RE: Excel as Database

BhawanaDaultani — Wed, 20 Oct 2021 12:27:00 GMT

So in case of huge data still we should use Excel VBOs for filtering the data/collection?

I guess sql query is mush faster than Excel Activities.

------------------------------
Bhawana Daultani
------------------------------

RE: Excel as Database

michaeloneil — Thu, 21 Oct 2021 16:26:00 GMT

Are you just storing the info in excel for later use or are you wanting to increment the data and add to it? if you are using large data sets but you are only inputting the info to excel then saving the file for use by someone, a team or reporting purposes then no issues with using oledb as its fast to insert data to an excel. If you want to create a single file and continually add data to this then you might have issues as excel with be limited in how much can be inserted i.e. row limits. It would be better to use a large scale database such as oracle or azure but microsoft access might be suitable for your needs.

If you dont have access to any databases at all and need to use excel you might want to consider what to do once you reach excels row limit e.g. archive the current file and create a new to add new data to. Or create a new file for each new week or month.

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