topic RE: OLEDB INSERT Statement in Product Forum

OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 02:43:00 GMT

Dear All,

I have two excel files which I am opening using OLEDB.

I want to put filter at a column [INPUT]=@ and copy the datas from Column B,C,D,E ,F from the source excel sheet to another excel sheet B,C,D,E

How to write the Insert statement . Is it something like this below.

"INSERT INTO [Sheet1$] ([A],,[C],[D],[E])VALUES([Output.A]&"",[Output.B]&"",[Output.C],[Output.D],[Output.E])"

where Sheet1 is the name of my destination sheet where data will be pasted.

Source Excel Sheet
Destination Sheet

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Tue, 01 Feb 2022 07:08:00 GMT

Hi Marina,

In order to write the data in the second sheet from your screenshot, you can't directly use that insert query as you data range does not starts from the beginning of the excel sheet which is 'A1'.

From what I can see, the first excel row is going to be blank and the data also needs to be written specifically from B2 cell to E column. Hence your query will get modified like this: "INSERT INTO [Sheet1$B2:E] VALUES('" & [Orders.B] & "','" & [Orders.C] & "','" & [Orders.D] & "','" & [Orders.E] & "')"

Also, keep in mind while using this query you need to loop within the Orders collection and with the loop use this query. The mistake a lot of people do here is that they sometimes even add the Open and Close action within the loop stage. Please avoid that so that you don't face unexpected errors while executing this action.

I have attached a sample workflow for your reference:

Result after execution:

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

PabloSarabia — Tue, 01 Feb 2022 09:01:00 GMT

Hi,

Exists a quick form to do this.

If you want to copy all form Sheet1 to Sheet2 use the "INSERT INTO SELECT" statement. You can also filter it.

Here is the syntax:

INSERT INTO [Sheet1$] (A, B, C, D, E) SELECT A,B,C,D,E FROM [Output] WHERE INPUT = '@'

Only need to modify the column names

Try it

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 10:14:00 GMT

Dear Pable,

Thank You so much for your answer.

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 10:18:00 GMT

Dear Devneet,

Thank You so much for your answer. I had one more question. If I want to select only coulmn names from sheet name can I write below.

"SELECT [RequestNo ,[OrderNo, [Parts Name] FROM [Sheet1]" where RequestNo, Order No, Parts Name are column names and Sheet1 is sheet name. Can I use Get Collection to select the columns.

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Tue, 01 Feb 2022 10:51:00 GMT

Hi Marina,

Glad your issue got resolved. Definitely that will also work for you, the query will be like: "SELECT [RequestNo] ,[OrderNo], [Parts Name] FROM [Sheet1$]"

Just remember the above query always will work whenever you data is starting from the beginning of the sheet, that is "A1" cell. In case your headers, fall somewhere at the middle, the you need to modify the From part of the query.

Let us suppose if the headers go from B2 till E then the query would have been modified as:

 "SELECT [RequestNo] ,[OrderNo], [Parts Name] FROM [Sheet1$B2:E]"

Similarly, if you want to put a filter as well while selecting the columns you can do by using a WHERE clause, then your query gets modified like this:

"SELECT [RequestNo] ,[OrderNo], [Parts Name] FROM [Sheet1$] WHERE [INPUT] = '@'"

Considering that INPUT is the name of the column where this value '@' resides.

Hope that helps to clear your doubts 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 11:21:00 GMT

Hi Devneet,

When I tried using that I am getting following error.

Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameters.

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Tue, 01 Feb 2022 12:16:00 GMT

Hi Marina,

The first row is blank in your sheet and the headers are starting from A2 cell, can you update the Sheet name parameter in your query as: [Sheet1$A2:<Last Column of your Table>]

In the screenshot I can't see which column it is, if it is J then provide [Sheet1$A2:J]

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 12:28:00 GMT

Hi Devneet,
I have selected below three columns only because I wanted only three columns A, B,C . Can I just select only three columns instead of entire columns.

"SELECT [sikeisho担当] ,[依頼NO], [試計書番号] FROM
[Sheet2$A2:C]"

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Tue, 01 Feb 2022 12:55:00 GMT

Hi Marina,

If they are continuous columns and they fall within the range from column A to column C then yes you can use the query. Let me try to explain you this way, suppose you need column A, B, E and J then in that case your query will be SELECT [A], , [E] and [J] FROM [Sheet1$A:J]. So essentially, your sheet range should comprise the starting column and the ending column range whereas in the SELECT part you only provide the columns that you want to select.

Hope that clears your doubt.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Tue, 01 Feb 2022 13:03:00 GMT

Hi Devneet,

Thank You for the explanation. In this case I only want column A,B, C hence i have used "SELECT [sikeisho担当] ,[依頼NO], [試計書番号] FROM
[Sheet2$A2:C]"

where sikeisho担当=column A
依頼NO=Column B
試計書番号= Column and my first column header starts from A2 but i am getting error below

Internal : Could not execute code stage because exception thrown by code stage: No value given for one or more required parameters. I have correctly given three column names still getting error

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Tue, 01 Feb 2022 23:37:00 GMT

Hi Marina,

Upon executing the same query on my excel I am getting the result for the query: "SELECT [sikeisho担当] ,[依頼NO], [試計書番号] FROM [Sheet1$A2:C]" as follows:

I would suggest you to carefully check each parameter of you query including Sheet Name, Column ranges and column names. Something is definitely not correct that is why you are getting this error.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Wed, 02 Feb 2022 04:47:00 GMT

Hi Devneet,

some format error was in excel. Now I got the output .Thank You so much.

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Wed, 02 Feb 2022 08:47:00 GMT

Hi Devneet,

Regarding INSERT statement I wanted to ask what if my INSERT INTO [Sheet1$B2:E] B2 is variable . I mean the row is not starting from B2 as already there is data and the bot has to fill from the next empty row. I already got the next empty row and stored in variable.Suppose the variable value is B2740 and is stored in CellRowAcolumn. How to write the INSERT INTO statement with variable row count when everytime the bot first runs and finds the empty B row and then inserts the data into Excel sheet. Kindly help on this part

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

PabloSarabia — Wed, 02 Feb 2022 09:14:00 GMT

Hi,

In the INSERT INTO statement you can only put the column name and the values. When you referrer the table name (the sheet name) you can put of course where start the table (In your case is correct), but the column names must to be equals to your Excel.

And by default, the INSERT put the lines at the end of the table.

To understanding this well, Can you have more rows under the first row with your B column empty? Or, the first B column that you find is also the last line?

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Wed, 02 Feb 2022 11:23:00 GMT

Hi Marina,

When you define the range B2, it means that where the headers are actually starting from. The only thing to keep in mind is that the headers should be continuous not discontinued. If you know the start of your range, INSERT query will automatically add the row elements in the next available empty row, you do not need to worry about that.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Wed, 02 Feb 2022 12:22:00 GMT

Hi Devneet,

Insert statement is inserting the data but the problem is it should insert from next empty row but it is leaving some blank rows and then inserting data.Something like this

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Wed, 02 Feb 2022 12:38:00 GMT

Understood. Thank You PABLO For the explanation.

------------------------------
Marina Dutta
------------------------------

RE: OLEDB INSERT Statement

devneetmohanty07 — Wed, 02 Feb 2022 12:49:00 GMT

Hi Marina,

This issue usually comes where there are certain rows with null values and excel does not actually treats them as a blank value. The thing here you can do is probably to first delete all the rows manually below the headers by selecting all rows and then pressing 'Ctrl' + '-' key. This should trim all the rows in your excel file properly. Then try using your automation solution on top of it.

Let use know if the rows are being filled properly once you delete all rows manually or not.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: OLEDB INSERT Statement

MarinaDutta — Wed, 02 Feb 2022 13:45:00 GMT

Hi Devneet,

The data got pasted after removing the NULL rows. Thank You very much for your guidance. One question , the INSERT and SELECT statement works only for continuous columns ? What if I want columns A,B,C,D and column J,K,N. Will it work in that time also?

------------------------------
Marina Dutta
------------------------------