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

[MarinaDutta]

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

[devneetmohanty07]

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

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

[MarinaDutta]

Hi Devneet,

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


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

[MarinaDutta]

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

[PabloSarabia]

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

[devneetmohanty07]

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

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

[MarinaDutta]

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

[MarinaDutta]

Understood. Thank You PABLO For the explanation.

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

[devneetmohanty07]

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

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

[MarinaDutta]

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