SQL query for insert data into excel using OLEDB

[vinodchinthakin]

Hi,

I have a Scenario where I am updating excel using write collection action from Excel VBO.
I want to achieve the same thing using OLEDB VBO with SQL Query.
The problem is I want to insert collection data into Excel from Cell Reference A5.
Can some one say how to achieve it?

Note; I am aware of set/open connection, Get and Insert queries of OLEDB. Just Need SQL query for above scenario..

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

You can write your insert query as shown below:

INSERT INTO [Sheet1$A5:B] VALUES('" & [Data1] & "','" & [Data2])"

Here, my sheet name is 'Sheet1' and my header starts from cell A5 and ends at column B. The data items Data1 and Data2 comprise of the value which I need to insert in the new record.

When you execute this query basically, new data values will be written under cell A6 and B6.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

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

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

[vinodchinthakin]

Hi Devneet,

Thanks for your solution. You Query mentions inserting of  data items. I got it.!
Can you tell me how to insert a collection which has around 20 columns.
With including headers and without including headers from collection

------------------------------
vinod chinthakindi
------------------------------

[vinodchinthakin]

Hi @devneetmohanty07

when am using Range in Select and Insert Queries, Bot throws following exception




Following Queries ​used
"SELECT * FROM [Sheet1$A2:K]"
INSERT INTO [Sheet1$A5:K] VALUES('" & [Coll1.Field1] & "','" & [Coll1.Field2])"

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

No changes at all, it's all the same. You need to prepare your query to accommodate all 20 columns and you need to also mention all 20 data items as well. What you can do is keep a string query template in a data item let say called as [Query Template] with the following initial value : "INSERT INTO [<SHEET_NAME>$] VALUES(<VALUE_LIST>)"

Then you iterate within your collection to prepare the list of data items which you can use to replace the <VALUE_LIST> keyword and store it in a temporary text data item and call the insert query again.

Basically, for insert query you need to keep looping within you data collection as the 'Execute' action only supports one record at a time and not your entire collection altogether. Also, you need to dynamically set up query string to have all the 20 column data which you can do with a combination of 'Get Fields' action to get column names and then iterate within that collection and use the 'Read Collection Field' action where you can supply the current field name along with a row index which can be a counter based data item of Number Type that will always increment just before your main collection loop is completed.

You workflow would be like:

Get Fields Action (Main Collection) -> Calculation Stage (Reset Row Count To 0) -> Loop Stage Start (Main Collection) -> Loop Stage Start (Fields Collection) -> Read Collection Value ( Pass Main Collection Field Name and Row Index) -> Calculation Stage (Prepare Value List) -> Loop Stage End (Fields Collections) -> Calculation Stage (Prepare Query String By Replacing Value List In Query Template) -> Execute Action (Pass Query String) -> Calculation Stage (Increment Row Count By 1) -> Loop Stage End (Main Collection)

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

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

This is happening because you have mentioned the range from column A to K which is in total 11 columns but you are passing value for only 2 columns only. If you do not have any values for other columns you need to pass in null values. I have worked on a sample query for your reference as shown below:



Here, the SQL query expression that I have used is as follows:

"INSERT INTO [" & [Excel Sheet Name] & "$A5:K] VALUES('" & [Data1] & "','" & [Data2] & "','','','','','','','','','')" ​

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

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

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

[vinodchinthakin]

Hi @devneetmohanty07

I tried your Insert query, but not worked as expected. 
I have a collection with two fields and two rows. see my below observations

1) "INSERT INTO [Sheet2$] VALUES('"&[Coll1.Field1]&"', '"&[Coll1.Field2]&"')"
Internal : Could not execute code stage because exception thrown by code stage: Number of query values and destination fields are not the same.​

2) "INSERT INTO [Sheet2$A3:B] VALUES('"&[Coll1.Field1]&"', '"&[Coll1.Field2]&"')"
Internal : Could not execute code stage because exception thrown by code stage: This table contains cells that are outside the range of cells defined in this spreadsheet.

Let me know if there is any syntax error in above of them.

3) can't we write a complete collection into excel without using a loop (using OLEDB)

4) Let me extend my Use Case. (using OLEDB)
I have three collections which has huge data (I cant merge them) which are needs to be updated in Excel one by one. can we have a solution without mentioning a range can we write three collections on excel without overwriting the data. so when second and third collections writing data into excel will update from last ref rowcol cell itself.


------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

The second query looks fine to me. Perhaps check your connection string once for the same:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={DATA_SOURCE};Extended Properties="Excel 12.0;HDR=YES;"

Also, which version of Access Runtime Engine have you installed? From my experience, Access Runtime Engine 2010 (32 bit) works fine with Blue Prism as I have seen issues with latest version. Moreover check your file once, there can be empty rows or columns outside the range that may not be visible. So I suggest you to clean the file once using CTRL+- key after selecting outer ranges.

2) We have to use a loop as per the design of how the OLEDB business object has been created. Even if you write a custom code stage that will also be used to anyhow internally iterate through the rows of your passed collection so it won't really make a very huge performance difference as OLEDB queries are usually pretty fast.

3) Insert query never can overwrite the data it always appends new rows. Only update queries can overwrite the data based on conditions. OLEDB can't work on a blank excel sheet as far as I know, so ideally you will always require to provide headers. And if you provide headers then all the queries that you do after that should consist of either exactly the same range or range less than that. For example, if my first row in excel has 4 columns let say A to D then subsequent queries can have the full range A:D or something like A:B or A:C. So if the collections you are merging together they have same columns or less columns you can still go with OLEDB but if a collection has a larger set of columns write that first then the rest.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

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

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

[SrujanaKoilakon]

Hi @devneetmohanty07,

Above query helps where you would like to insert rows one by one.

Can you help me with a query where we can write complete collection(which has got 60K+ rows) in to excel at a time.

Thanks,
Srujana​

------------------------------
Srujana Koilakonda
------------------------------

[PabloSarabia]

Hi @Srujana Koilakonda

​Unfortunately, there is no method for bulk copy in the System.Data.Oledb that use this solution.

If you need to massively insert rows, there are two alternatives

1.- Insert multiple rows in a single INSERT statement. This have a limitation of 999 rows per insert, so you will need to execute this several times. The syntax for this is simple, I will copy one of the examples above 

INSERT INTO [Sheet2$] VALUES('val1', 'val2')
, ('val3', 'val4')
, ('val5', 'val6')

2.- And the second one, use the action "Write Collection" that you will find in the MS Excel VBO. 


Hope this helps you!

See you in the Community, bye :)


------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------