topic Re: OLE DB - Excel Query in Product Forum

OLE DB - Excel Query

RitaReis_Cabrit — Wed, 14 Apr 2021 17:14:28 GMT

Hello Community,

Hope you're fine! Just started to learn applying OLE DB in BP... I've studied online how to configure queries and I think that I'm understanding it well. However, this is not working and BP answers with this error: "Internal: Could not execute code stage because exception thrown by code stage: Invalid argument."

What I want to do is to extract a single column without headers from excel file... Is anything worng in my query? Could you guys help me out here?

Also, if you know some tutorial content that I should see, I appreciate it!

Thanks for your atention,

Rita

Re: OLE DB - Excel Query

PabloSarabia — Wed, 14 Apr 2021 17:38:53 GMT

Hello!

If you can share a photo or something about your code, we can help you better.

If is your first time using OLEDB libraries, here you have any easy steps to make a simple query in Excel

First one, you need to install the Microsoft OLEDB library, here you have the exe to install it: https://www.microsoft.com/es-es/download/confirmation.aspx?id=13255
(One alternative of this is to donwload externally the DLL)

Then in the Blue Prism Object:
- add the DLL reference System.Data.OleDb.dll
- add the Space Name System.Data.OleDb (el de la libreria)
- And, in a new page, add a piece of code:

I recomend you to insert in this code, as input, the name of your Excel File (variable name: excelFile), and the name of the Sheet (variable name: sheetName). And return a collecion (Datatable)

And here is a simple code

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";");
connection.Open();

OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM " + sheetName;

DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());

connection.Close();

Result = dt;

In the query, replace "*" for the column name that you want to ask.

Hope this help you.

Bye 🙂

Re: OLE DB - Excel Query

RitaReis_Cabrit — Wed, 14 Apr 2021 19:59:10 GMT

Hello Pablo,

Thanks for your answer and advises!! I'm working for a company that has its' own OLE DB code, so I can not change it... I thought that I had inserted the screenshot, my bad -.-

I'm doing as you said, "In the query, replace "*" for the column name that you want to ask." But somehow the error stills...

Re: OLE DB - Excel Query

PabloSarabia — Thu, 15 Apr 2021 07:01:41 GMT

Hello Rita,

Sorry, but i can't see the screenshot.

But, I understand that you are making correctly the query.

The problem I think is not in the code, its on the "Piece of code Object". In the code editor you have an error and warning list, can you see your error in that list?

Bye 🙂

Re: OLE DB - Excel Query

RitaReis_Cabrit — Thu, 15 Apr 2021 08:59:13 GMT

Hello Pablo,

I don't know what is going wrong with screenshot... Im'm sorry... but basicaly my query is: "SELECT * FROM [sheetName$]"
The original BO that I'm using of OLE DB has no errors or warnings, so looks that everything is fine...

I have some questions:
1) are these steps really necessary? I haven't found those settings in BP System [- add the DLL reference System.Data.OleDb.dll; - add the Space Name System.Data.OleDb (el de la libreria)]

2)I have created a new BO with Set Connection, Open, Get Collection and Execute. Then added the code that you told me, but it appeared 43 errors... apparently a lot of that code strings are not "declared" and due to the high level of protection... what do you think?

Thanks again for your help!!

Re: OLE DB - Excel Query

PvD_SE — Tue, 20 Apr 2021 11:56:36 GMT

To use OLEDB you do not need to do any coding other than creating the SQL statement, which you already have.
Also, you do not have to create a new object to work with OLEDB, everything you need is already in BP.

Steps to use OLEDB via object Data - OLEDB Extended:

Action: Set Connection
Uses a connection string that might look similar to this:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;";
Note that you have to replace "C:\MyExcel.xlsx" with the path to you Excel.
Google 'oledb excel connection string' to find out more about the connection string and its parameters
Action: Open
Opens the file for access
Action: Get Collection
In: your SQL, format your sheetname with [] and $, such as you already had: [sheetName$]
Action: Close

When I use OLEDB, I restrict the size of the excel to 15k rows. More that this and OLEDB will crash for me. Obviously, the max number of rows varies depending on the number of columns and perhaps memory in the server/computer where you run your process.

Notes:

Your SQL "SELECT * FROM [sheetName$]" should do fine, you can have the * in it and do not have to replace it with the column name(s).
The above shows the essentials of OLEDB, for more info you can search this forum.