OLEDB INSERT Statement

[MarinaDutta]

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
------------------------------

[devneetmohanty07]

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

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

[PabloSarabia]

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
------------------------------

[MarinaDutta]

Dear Pable,

Thank You so much for your answer.

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

[MarinaDutta]

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
------------------------------

[devneetmohanty07]

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

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

[MarinaDutta]

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
------------------------------

[devneetmohanty07]

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

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

[MarinaDutta]

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
------------------------------

[devneetmohanty07]

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

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