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,

The only problem was some places there were actual data which were needed and some rows were NULL. If I press control key + '-' all the wanted data data also got removed . It became empty sheet and data got pasted. However it is also very difficult to search particular NULL rows and delete those NULL rows as my excel sheet contain 4000 rows of data in between some are NULL rows. Only option for me is to remove all the data and make it a blank sheet by pressing control key +'-'  AND RUN THE insert COMMAND. 

Thanks and Regards,
Marina

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

[MarinaDutta]

Hi Devneet,

I have a use case where  I have to  two Excel files  and need to  paste the data in file. Below is my file 1. What ever data is present in column B,C,E,AF( of file1 ,I need to match with the columns B,C,K ,O of file 2 and if they are equal I should copy the data of column D,E,F,G,H,I,J  of file 2  in my file 1 (Columns QRSTUVW). How to make a key in both the files so that I  can compare and if they are equal I can paste data.

(File 1)

File 2


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

[PabloSarabia]

Hi!

The INSERT INTO SELECT works correctly with for non-continued columns.

In this instruction you only have two requirements:

- The column name must be the same (8 columns in the INSERT and 8 columns in the SELECT)
- The data type must be the same for selected and inserted.

So... you can make something like this:

INSERT INTO [Sheet1$] (field1, field2, field3, field4) 
SELECT B,D,J,K FROM [Sheet2$] WHERE A='@'


Bye 🙂

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

[MarinaDutta]

Hi Pablo,

I did something like this below but getting error.
"INSERT INTO [Sheet1$J1:L] VALUES('" & [KPI_ALL_Values.R&D Release Date] & "','" & [KPI_ALL_Values.Release date in LM] & "','" & [KPI_ALL_Values.Started date in BOM]&"') FROM [KPIALL$] WHERE [KPI_ALL_Values.Shikeisyo No]=[Master_Sheet.Prototype] AND [KPI_ALL_Values.Input]='@'"

I want to insert values in Sheet1 Columns (R&D Release Date,Release date in LM,Started date in BOM)  from Sheet "KPIALL"  COLUMN values  (R&D Release Date,Release date in LM,Started date in BOM and with condition  (KPI ALL SHEET COLUMN ".Shikeisyo No"  value should be equal to Sheet1 column Shikeisyo No) and also KPIALL column INPUT =@


1)Basically First I should filter  sheet KPI ALL with column [INPUT] = @
2) Match KPI ALL sheet column [Shikeisyo No] with Sheet1 column [Prototype] . If both values match then insert  data from KPI ALL sheet column values (R&D Release Date,Release date in LM,Started date in BOM) into Sheet1 columns(J,K,L)
Source Sheet(KPIALL)



Target Sheet (Sheet1)

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

[PabloSarabia]

Hi Marina,

In your case, the correct basic syntax looks like this:
    
INSERT INTO [Sheet1$J1:L]
SELECT [KPIALL$].[R&D Release Date]
        , [KPIALL$].[Release date in LM]
        ,[KPIALL$]. [Started date in BOM]
FROM [KPIALL$]
        INNER JOIN [Sheet1$] ON [Shikeisyo No]=[Prototype]
WHERE [Input]='@'

Explaining the query....

On the top you have the INSERT INTO statement
Then you have a full SELECT query. The idea of this kind of combine statements is to move easly data between tables.
To select only the results that match KPI ALL sheet column [Shikeisyo No] with Sheet1 column [Prototype] need to use INNER JOIN to "mix" the tables in the same FROM


Try with this and let us know if the problem is solve


Bye 🙂


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

[MarinaDutta]

Hi Pablo ,

Thank You for your reply but when excuting the SQL statement I got the following error.I also tried the following. The problem which I am facing is I need to open both the KPIALL excel sheet and Sheet1 Excel sheet . If I close Sheet1 and try to open KPIALL using set conection I am getting error . How to open both the excel sheets in the OLEDB and excute the sql query. How to include in SET connection so that I can open bothe the excel sheets simultaneously.
"INSERT INTO [Sheet1$J1:L]
SELECT [KPIALL$].[R&D Release Date]
, [KPIALL$].[Release date in LM]
,[KPIALL$].[Started date in BOM]
FROM [KPIALL$]
INNER JOIN [Sheet1$] ON [KPIALL$].[Shikeisyo No] = [Sheet1$].[Prototype] WHERE [INPUT] ='@'"





Thanks and Regards,
------------------------------
Marina Dutta
------------------------------

[PabloSarabia]

Hi,

So stranger.. it's common to use INNER JOIN in the clause FROM.

But, never mind, just try this:

INSERT INTO [Sheet1$J1:L]
SELECT [KPIALL$].[R&D Release Date]
        , [KPIALL$].[Release date in LM]
        , [KPIALL$]. [Started date in BOM]
FROM [KPIALL$]
WHERE [Input]='@'
        AND [KPIALL$].[Shikeisyo No]IN(SELECT [Prototype] FROM [Sheet1$])

Hope this finally solve the problem hahaha


Bye 🙂

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

[devneetmohanty07]

Hi Mariana,

Sorry I wasn't keeping well, so could not reply earlier to this thread. I have one doubt over your query. You said you are comparing values between Sheet1 and KPI All sheet and if some column values match between the two you "insert a new row with the values in the Sheet1 sheet which is being used for comparison". 

My question was, is this an insert operation or an update operation as if you are inserting the values in the same sheet against what you are comparing wouldn't it result in different rows with blank prototype values? Which means a new row will be added but with only three column values. The result will be something as shown below:



As per my understanding it should be an update query rather an insert query if you are pasting the values in the same sheet as you are comparing:

"UPDATE [Sheet1$], [KPIALL$] SET [Sheet1$].[R&D Release date] = [KPIALL$].[R&D Release Date],[Sheet1$].[Release date in LM] = [KPIALL$].[Release date in LM],[Sheet1$].[Started date in BOM] = [KPIALL$].[Started date in BOM] WHERE [KPIALL$].[Shikeisyo No] IN (SELECT [Prototype] FROM [Sheet1$]) AND [Sheet1$].[Input]='@'"


The result of this operation will be like this:

Source KPI ALL Sheet:


Target Sheet:



Post Execution - Target Sheet:



Here you can see that for Prototype No: 111, we had a match in source sheet and also the input column was '@', hence the appropriate column values got updated.

If your requirement is to insert a new row then use the following query:

"INSERT INTO [Sheet1$] SELECT [KPIALL$].[R&D Release Date],[KPIALL$].[Release date in LM],[KPIALL$].[Started date in BOM] FROM [KPIALL$] WHERE [KPIALL$].[Shikeisyo No] IN (SELECT [Prototype] FROM [Sheet1$]) AND [Input]='@'"



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

Thank You Devneet once again for explaining so clearly . I have just two questions.
 How to open two excel sheets simultaneously in OLEDB. The problem is first I am opening Target sheet (Sheet1$)using SET connect and using GET collection I am trying to extract all the data in collection . Then closing the OLEDB connection. Again using SET Connection and OLEDB Open for the second sheet. (KPIALL$)Since I am already closing the first sheet(Sheet1$) I am getting error 

Internal : "Could not execute code stage because exception thrown by code stage: 'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

Though Sheet1$ IS VALID NAME.


Thanks and Regards,
Marina

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

[devneetmohanty07]

Hi Marina,

Glad to know that I was able to clarify the question earlier. Coming to the second question, you should not set up connection multiple times if your sheets are present in the same excel file. Ideally, if you are using the update query that I have given you all the operations will be done in a single Execute action. The line of actions will be as:

First use 'Set Connection' action, then go ahead and use the 'Open' action, then use 'Execute' action and at the end use the 'Close' action.

Now if you are using the queries separately as it seems from your latest thread, then you only need use 'Set Connection' action once then use an 'Open' action. Post this you can use 'Get Collection' action and then again use 'Execute' action within a loop if your query executes only for a single row at a time and then go ahead with 'Close' action.

If you had multiple excel files like one excel file had a source sheet and the target sheet was included in some other excel file then such kind of a logic can be used where you can open and close the connections individually.

Also, one thing to note if you are opening and closing connections consecutively add a delay in between in form of a 'Sleep' action from 'Utility-Environment' VBO. Sometimes the closing a connection takes time and if you try to open the connection again by the time it hasn't been closed some unknown issues might occur.

The error you are indicating can be due to multiple reasons, like some error with the connection string, if it is in a wrong format or of the file mentioned is not proper. Error in the Query format if the name is not written within proper brackets or there is a issue with syntax.
Also as I said just in the above paragraph, this can also occur because of consecutive opening and closing of the connection with the same excel file without any delay in between.

Since you haven't provided the screenshots or the snippets of both your connection string or query format, it is bit difficult to point the exact source of the issue.

But all in all to answer your question, try doing all this operation within one open and close connection block.

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

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