I have an Excel sheet and its humongous.. Around like 200000 rows that needs to be processed. All I have to do is read it and process them with a query on a DB2 table. I have written the program where its more than 8 hours to process 5000 rows.
Is there a way where I can simultaneously read the excel and execute the query. I want them to be independent of the process. I cannot use Parallel.for as reading and creating so many instance of threads is no advantage. ANy pipes and queues are of no use. THis is a dom method using and it does not read a row, it reads a string.. if there is a null value on the row, it executes the row and throws an null exception. I am well with Background workers and TPL's. Any idea or code would be appreciated. No DLL can be used apart from OPENXML
Ideally I do not want to add to array,, I want it in 2 diff variables and process them when read..
- Read a row( only 2 columns, ignore other cols
- create a thread to execute the row and in Parallel, execute the read row.
- Merge into one single table.
- display results.. Sounds simple but there are challenges.
.
Try
Using spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Open(fileName, False)
Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()
Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
For Each r As Row In sheetData.Elements(Of Row)()
For Each c As Cell In r.Elements(Of Cell)()
Dim text As String
Try
text = c.CellValue.Text.ToString
Debug.Print(text.ToString)
If text IsNot Nothing AndAlso Trim(text).Length > 0 Then
Arr.Add(text.ToString)
End If
text = Nothing
j += 1
Catch
End Try
Next
text = Nothing
Next
End Using
Catch ex As Exception
MsgBox("Exception caught: " + ex.Message)
Debug.Print(ex.Message.ToString)
End
End Try
myArr = CType(Arr.ToArray(GetType(String)), String())
This is the process which is dividing the data into 2 parameters
For i As Integer = 2 To myArr.Count - 1 Step 2
If i And 1 Then
i = i - 1
Else
dstr = DateTime.FromOADate(Double.Parse(myArr(i).ToString())).ToShortDateString()
'Debug.Print(dstr.ToString & "----->" & i.ToString & "TCID--->" & myArr(i + 1).ToString)
DQueue.Enqueue(DateTime.FromOADate(Double.Parse(myArr(i).ToString())).ToShortDateString())
Tqueue.Enqueue((myArr(i + 1).ToString()))
TCArr.Add((myArr(i + 1).ToString()))
dc.Merge(ProcessQueryODBC(dstr, myArr(i + 1).ToString))
If dc.Rows.Count > 0 Then
dt.Merge(dc)
Else
nFound.Merge(CreateDT(dstr, myArr(i + 1).ToString()))
End If
End If
Next
