Insert Excel data to oracle tables using OLEDB
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
16-11-22 09:43 AM
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
16-11-22 01:18 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
17-11-22 07:19 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-11-22 08:25 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-12-22 02:11 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-12-22 09:56 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
14-12-22 10:23 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-12-22 10:34 PM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
19-12-22 12:32 AM
Why don't you try to use sql*loader for Oracle Database.
This technique is popular in database-related projects.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
20-12-22 07:03 AM
