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,

In my case I am having multiple excel files. KPIALL is a different excel file, Sheet1 is from different excel file. In that case What I was doing was Open Sheet1 excel files , set connection and then Get OLEDB connection. After that I was closing the connection. Then again Open sheet KPIALL , and set connection , then execute the SQL statement. At that time since I closed Sheeet1$  already before it is not recognizing Sheet1$.   how to excute SQL OLEDB when we have multiple excel files.

             

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

[devneetmohanty07]

Hi Marina,

Then most likely issue is with the connection string for the second file that you are using. Can you check once of you are using the correct file path in the second 'Set Connection' action right after using 'Close' action for first excel file.

If you can show us the parameter values as well it will help to figure out your issue.


Also, curious to know why you want to refer back the Sheet1 when you have retrieved all the data from it into a collection in the first place.
------------------------------
----------------------------------
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,

Let me try now. The error has gone. I will execute the query and let you know the result.


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

[MarinaDutta]

Thank You Pablo.

Thanks and Regards,
Marina

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

[MarinaDutta]

Hi Devneet ,

I am still getting the error after closing the first excel sheet on excecuting the SQL query. When I am trying to use update query it is not recognizing the first sheet Sheet$1 as I have already closed and open the another excel sheet KPIALL. I have given correct path in Set connection also. This error is happening after I use close connection to close the first excel sheet Sheet1$. Now when I am usin the first excel sheet Sheet1$ IN UPDATE STATEMENT  it is throwing error."Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."
path3 is for KPIALL sheet
Path 2 is for Shikeisyo_KPI (Sheet1$)



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

[MarinaDutta]

Hi Devneet,

I have an query to your above solution. How did you open Source KPI ALL Sheet: and the  Target Sheet: at the same time to execute the OLEDB Query "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]='@'"

Kindly help on this as I am stuck on this part for many days. As from OLEDB set connection we can open only one sheet at a time. How did you executed the Update statement. Can you kindly help on this.

Thanks and Regards,
Marina

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

[devneetmohanty07]

Hi Marina,

Sorry it has been a very busy week at my end due to some crunching deadlines and some personal errands recently so I could not actually see to the issue you were facing. Moreover, I think there are now 2-3 threads on the same issue which has been a bit difficult to keep a track of. But what I did was actually on a single excel file I had two sheets where this query did work. In one of your other threads, I gave you a custom code to copy the worksheet and paste in the same workbook. If you do that, then this solution should actually work as OLEDB has a limitation in Blue Prism to work with multiple file sessions.

Anyhow, I got your email as well. I will try to find some time today and give you a solution workflow both on the email and on your latest thread. Hope that will help you out 🙂

NOTE: I have done this kind of an implementation in other RPA tools as each OLEDB operation can be confined within multiple session variables, but in Blue Prism since you don't have a proper data item to hold the connection session data out of an action in any data type other than the Blue Prism native variable types (Text, Number, Flag etc.), I think that is one of the major issues to why you can't create a custom parameterized object for the same to get that session variable out like that. Perhaps there can be someway which I am missing but at this point creating a code to do same will not make it ideally reusable which I don't like while creating objects like that where the operations are confined to a particular use case only. Hence, I wont be going via the code way to show you that.

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

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

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

[devneetmohanty07]

Hi Marina,

As asked I have attached the sample files for you to check once, it will give you a fair idea about the use case solution. I haven't used any excel copy operation because we were only copying worksheets to use a single OLEDB connection instead I am using two separate OLEDB connections with the two files in order to do the same update operation in the file that I showed earlier to you.

Hope this helps you out!

NOTE: Please change the file paths as required in the imported environment variables.

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

Dear Devneet,

Thank You so much for the solution. I could not understand the CStr part in the query . Can you explain the CStr part in query?


"SELECT [Shikeisyo No], CStr([R&D Release Date]) AS [R&D Release Date], CStr([Release date in LM]) AS [Release date in LM], CStr([Started date in BOM]) AS [Started date in BOM], [INPUT] FROM [KPIALL$]"

Thanks and Regards,
Marina

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

[devneetmohanty07 · Accepted solution]

Hi Marina,

The CStr keyword is used to cast the data item as String rather than a date while reading. There is a known issue with OLEDB at times regarding date fields, where if you read the dates directly Blue Prism will read the dates as per the regional settings of your machine, so for example in my case , if I have a date say '01-01-2020', then what I got was '31-12-2019 18:30:00' which is incorrect, so what I did is to cast the value as a String so that the the automatic date conversion can't take place. If I want to do some operations on date, I can convert the field into date type in Blue Prism using some other logic which be under my control and not the regional settings.

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

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