GoExcel.FindRow alternative?

[mslosar]

We're getting a new ERP and as a result are getting all new part numbers so i have to go through Content Center and replace all the existing PN's with new ones, fun!

Anyhow, I can loop through the whole thing via ilogic but when i get to replacing the PN's it works with small families, but when there's nearly 800 members, it's sucking up all my system resources.  I have 32GB and it's taking that and 62GB of pagefile which errors the process out from taking up all the HD space via pagefile.

Is there anything that can be done to limit the resources this will take? Or will it have to be done family by family the hard way? (and by hardway, doing a vlookup table in excel and pasting it back in) (5 minutes per family or so)

oldPN = oRow.Item("PARTNUMBER").Value				
				excelRow = GoExcel.FindRow(oFullName, "Sheet1", "Part Number", "=", oldPN)
				
				If excelRow = -1 Then
					'do nothing
					Else						
					oRow.Item("Part Number").Value = GoExcel.CellValue(oFullName, "Sheet1", "E" & excelRow)			
				End If

[Ralf_Krieg · Accepted solution]

Hello

Maybe the massive calls fillup the undo cache?

I assume you have an Excel file with Column "A" is the old part number and column "B" is the new part number. A1 and B1 are Header, values start at A2 and B2 and end at A50 and B50.

I would suggest to get two ILists of the partnumber lists and query against them.

Hope this helps.

Dim oldPNList As IList = GoExcel.CellValues("C:\Temp\Source.xls", "Tabelle1", "A2", "A50")
Dim newPNList As IList = GoExcel.CellValues("C:\Temp\Source.xls", "Tabelle1", "B2", "B50")

'for each oRow in ....
	oldPN = oRow.Item("PARTNUMBER").Value

	Dim i As Integer = oldPNList.IndexOf(oldPN)
	If i > -1 Then
		oRow.Item("Part Number").Value = newPNList.Item(i)
	Else
		Logger.Debug("Old part number " & oldPN & " not found")
	End If
'next

[mslosar]

i'll give that a shot and see how it goes.

thanks.

[mslosar]

That seems to have done it, what crashed out due to lack of resources yesterday after 30ish minutes completed today in about 5 minutes barely requiring additional resources.

Thanks you!