topic RE: Extract Data from large Excel file - Error Out of Memory in Product Forum

Extract Data from large Excel file - Error Out of Memory

RSHERRY — Fri, 04 Nov 2022 11:11:00 GMT

Hi
I am trying to extract data from a large Excel file but i am getting the Out of Memory message.
There are approx 250000 records
I have used the GarabageCollection() before trying to extract the data but it has not made any difference,

I have also tried to extract the data in sections but again I get the error when trying to add the data to the collection.

I have extracted the data using a different RPA tool (Which is no longer available in work) and it worked fine . No Out of memory error.

Any help on this matter would be greatly appreciated.

------------------------------
Rachael Sherry
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

ewilson — Fri, 04 Nov 2022 11:25:00 GMT

Hi @RSHERRY,

When you tried doing this in sections you were still adding the data into the same collection, correct? If so, is it possible to adjust the process so that you fully process each section (i.e. chunk of the Excel) and then move to the next section with a clean collection?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

RSHERRY — Fri, 04 Nov 2022 12:12:00 GMT

I wish I could do that.
I am extracting data from a data base and then I iterate through this collection and check to see if the record exists in the excel file.
so I would need to have all the excel results in one collection

------------------------------
Rachael Sherry
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

michaeloneil — Fri, 04 Nov 2022 17:13:00 GMT

Hi @RSHERRY

If you are getting information from a database and search for each record in the excel file is there any reason to extract the information from the excel file? I would suggest for each database record you just use the Excel action Find to see if the record you are looking for exists in the excel file within the given column.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

ThomasHutchins — Mon, 07 Nov 2022 18:39:00 GMT

Hi,
You can use the Data - OLEDB VBO to create a connection from BP to the Excel file, then look up your database items in the Excel file using a simple SQL query in a loop. You don't even need to open the Excel file.

Hope this helps,

Hutch

------------------------------
Thomas Hutchins
Lead Developer
Discover
America/Chicago
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

RSHERRY — Tue, 08 Nov 2022 10:42:00 GMT

Hi @MichaelONeil

I have written code to get the Find working correctly.
Please see below for anyone who may want to use it in the future

This is my code
Input Workbook, Worksheet, Handle, FindMatch
Output CellAddress - Text ,

Dim wb, ws As Object
Dim excel, sheet As Object
Dim ValueFound as Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

'Check if the cell is found

ValueFound=sheet.Cells().Find(What:=FindMatch, _
LookIn:=-4123, _
LookAt:=1, _
SearchOrder:=1, _
SearchDirection:=1, _
MatchCase:=False)

If ValueFound is Nothing then
CellFound=False
CellAddress=""
ELSE

CellAddress=sheet.Cells().Find(What:=FindMatch, _
LookIn:=-4123, _
LookAt:=1, _
SearchOrder:=1, _
SearchDirection:=1, _
MatchCase:=False).Address
CellFound=True

END IF

Also I wanted to ask is there a way of returning the Column letter only? I have tried putting column at the end instead of Address but it gives an error.

Many thanks
Rachael

------------------------------
Rachael Sherry
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

RSHERRY — Tue, 08 Nov 2022 10:44:00 GMT

Hi Hutch

Thanks for your advice, Once I get the .Find working , I will take a look at the OLEDB VBO and see if I can get that to work.

Many thanks
Rachael

------------------------------
Rachael Sherry
------------------------------

RE: Extract Data from large Excel file - Error Out of Memory

michaeloneil — Tue, 15 Nov 2022 21:30:00 GMT

Hi @RSHERRY

Sorry for the late reply on this, if you want to search for text and get the cell address returned then below is the code I used for this. The inputs are Handle, Workbook, Worksheet, SearchText and StartingCell The starting cell is the cell you want to start the text search from e.g. A1. This will return the celladdress with column and row but once you get that you can use a calculation stage to remove the row so you only have the column. e.g. Cell address is A15 the calculation stage would be Mid([CellAddress],1,1) and this will remove 15 from the text and leave A.

Dim wb, ws As Object

Dim excel, sheet, range As Object

Dim startcell as Object

Dim newCell as object

Dim xlformulas as integer = -4123

Dim xlRows as Integer = 1

Dim xlNext as Integer = 1

Dim xlPart as Integer = 2

Try

wb = GetWorkbook(Handle, Workbook)

ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

sheet = excel.ActiveSheet.Range(StartingCell).Activate()

startcell = excel.ActiveCell

ws.Cells.Find(What:=SearchText, After:=startcell, LookIn:=xlformulas, _

LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate()

newCell = excel.ActiveCell

Success = True

Result = newCell.Address(False,False)

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

excel = Nothing

sheet = Nothing

range = Nothing

End Try

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------