Extract Data from large Excel file - Error Out of Memory

[RSHERRY]

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

[ewilson]

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

[RSHERRY]

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

[michaeloneil · Accepted solution]

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

[ThomasHutchins]

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

[RSHERRY]

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

[RSHERRY]

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

[michaeloneil]

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