Community Chat

last person joined: 4 days ago 

A space for discussion around the Community site and programs.

  • 1.  Excel as Database

    Posted 10-20-2021 10:47
    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
    ------------------------------


  • 2.  RE: Excel as Database

    Posted 10-20-2021 11:54
    https://digitalexchange.blueprism.com/dx/entry/3439/solution/data---oledb

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



  • 3.  RE: Excel as Database

    Posted 10-20-2021 13:24
    Thank you Denis.

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



  • 4.  RE: Excel as Database

    Posted 10-20-2021 12:03
    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
    ------------------------------



  • 5.  RE: Excel as Database

    Posted 10-20-2021 13:20
    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!
    ------------------------------



  • 6.  RE: Excel as Database

    Posted 10-20-2021 13:27
    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
    ------------------------------



  • 7.  RE: Excel as Database

    Posted 10-21-2021 17:26
    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
    ------------------------------



Welcome to Community Chat!

A space for non-technical chat, tips for using the Community, and news from the Blue Prism Community Team.

This is not a Community for technical or Product related discussion. For a list of available communities, please visit our site map.

Introduce YourselfCommunity Quick TipsWomen in RPA