Insert Excel data to oracle tables using OLEDB

[Preranajain]

Hi,

I am trying to insert data from an excel file to oracle table using OLEDB.

Since the data in the excel in large count (100000+), the insert fails.

Can someone help me with this ?

[michaeloneil]

Hi @Prerana jain

I think with this the best thing to do would be to chunk the information and do multiple inserts. I would suggest from the excel try getting the rows 1 - 20,000 as a collection and run the insert for this. Once complete get rows 20,001 - 40,000 as collection and insert. Repeat this until everything is inserted, the problem with inserts to db is that blue prism often throws a memory error so you might need to adjust the number of rows you can insert at one time until you get the maximum number you can insert at once without sacrificing speed and performance.​

[Preranajain]

Hi @Michael ONeil

Thanks for the reply.

I tried splitting the excel file in the rows of 1-20000 and even 1-1000 per insert.

Still after some insertions it gives the memory error.

​

[michaeloneil]

Is it an oracle database you are inserting records into? I think if its struggling to insert a volume as small as 1000 records then it might be worth checking the sql query you are using if this could be the issue. The other option could be to extract each line from the excel and add it to a work queue, once they exist in BP queue you can get each record idividually and do the insert query for each one. It sounds like it would be slow to do but you just need to do a loop with the get each work item in turn and sinces its taking it from the work queue then it will work pretty fast.

[Preranajain]

Hi @Michael ONeil,

The add to work queue solution worked.

Since, the data in excel is 100k+​, the work queue takes more than 5 minutes to add, because of which the process goes into warning stage when run through control room.

[michaeloneil]

Hi @Prerana jain

I'm glad this worked for you, I would say the warning message isnt a big concern if you are confident it will finish in 5 minutes or so. I have a few processes that show warning messages as they are working with extremely large, slow excel workbooks but they complete just fine. If you are looking for a way to get the data a little faster into the work queue you could always try using an SQL query to extract the data from the workbook instead of the get worksheet actions. You can do this with the OLEDB VBO actions you just need to provide a different connection string for excel than for an oracle database then you can use SQL to extract the data if you know the columns/rows you need to extract. There is another thread on here that should help if you wanted to try it SQL query for insert data into excel using OLEDB | Blue Prism Product​

[Preranajain]

Hi @Michael ONeil

I ​tried the steps suggested.

Still the queue takes time to add. Also, when I try to insert those the excel data into db table.

When the process goes on "Get next item"​.

It gives internal error  "Internal : Invalid item ID"

Attaching the screenshot.

[michaeloneil]

Hi @Prerana jain

Sorry for the late reply, I'm not sure what you mean in regards to the error. The Get Next Item action for work queues doesnt require an Item ID as an input, it does have an Item ID as an ouput, have you created a data item for this to be put the item id into when you get next item?​

[BohyonHwang]

I'm not sure OLEDB is the proper way for bulk data insertion on any db.
Why don't you try to use sql*loader for Oracle Database.
This technique is popular in database-related projects.

[Preranajain]

Hi @Michael ONeil,

Yes, I ​have created a data item when I get next item.