I am trying to load large flat files; > 250 MB. What is the best practice
for this? I have already read through everything I could find on Google, to
no avail.
I have a SQL DTS process that will load the file in about 7 minutes. My
code takes about an hour.
Here is a snippet:
Dim objSR As StreamReader = System.IO.File. OpenText(objOri gFile)
Dim curEncoding As System.Text.Enc oding = objSR.CurrentEn coding
Dim loopX As Integer = 0
Do While objSR.Peek > -1
loopX += 1
Dim strRec As String = objSR.ReadLine
arrA = Split(strRec, coldel)
Try
If trailerSigHas Then
If getRecordData(t railerSigPos - 1).ToString =
trailerSig Then
If loopX < 2 Then
Throw New Exception("Empt y extract file")
Else
Exit Do
End If
End If
End If
Dim tmpLngth As Integer = arrA.Length
Dim tmpColCount As Integer =
iNode.Item("col umns").ChildNod es.Count ' objDS.Tables(0) .Columns.Count
If tmpLngth > tmpColCount Then
Throw New Exception("Too many columns in extract
row.")
ElseIf tmpLngth < tmpColCount Then
Throw New Exception("Not enough columns in extract
row.")
End If
Dim newRow As DataRow = objDS.Tables(0) .NewRow
For x As Integer = 0 To objDS.Tables(0) .Columns.Count -
1
Dim tmpSqlCol As String =
objDS.Tables(0) .Columns(x).Cap tion
Dim tmpNode As XmlNode =
iNode.SelectSin gleNode("//columns/column[@sqlcol='" & tmpSqlCol & "']")
'"//columns/column[sqlcol='" & tmpSqlCol & "']")
Dim attrID As String =
tmpNode.Attribu tes("id").Inner Text
Dim xmlCol As Integer = Convert.ToInt32 (attrID)
Dim arrVal As Object = getRecordData(x mlCol - 1)
newRow(x) = GetObjectValue( x, arrVal) ' Does a
Convert to the target column type
Next
objDS.Tables(0) .Rows.Add(newRo w)
Catch ex As Exception
Dim newRow As DataRow = objDS.Tables(1) .NewRow
Dim finalX As Integer = 0
For x As Integer = 0 To objDS.Tables(1) .Columns.Count -
1
Try
Dim strColCaption As String =
objDS.Tables(1) .Columns(x).Cap tion
Dim tmpINode As XmlNode =
iNode.SelectSin gleNode("//columns/column[@sqlcol='" & strColCaption & "']")
Dim attr2ID As String =
tmpINode.Attrib utes("id").Inne rText
Dim sqlColInt As Integer =
Convert.ToInt32 (attr2ID)
If x = 0 Then
newrow(x) = Left(getRecordD ata(sqlColInt -
1).ToString, 8000)
Else
newrow(x) = Left(getRecordD ata(sqlColInt -
1).ToString, 250)
End If
Catch ' non-existent column
Finally
finalX = x
End Try
l001f:
Next
newrow(finalX - 4) = Left(ex.Message , 150)
newrow(finalX - 3) = DateTime.Now
newrow(finalX - 2) = tblName
newrow(finalX - 1) = strFileName
newrow(finalX) = loopX
objDS.Tables(1) .Rows.Add(newRo w)
End Try
Loop
objSR.Close()
objSR = Nothing
End If
objConn.Open()
Dim rowCount As Integer = objDS.Tables(0) .Rows.Count
Dim rowCount2 As Integer = objDS.Tables(1) .Rows.Count
Dim updCount As Integer
If objConn.State <> ConnectionState .Closed Then objConn.Close()
updCount = objDAmain.Updat e(objDS, tblName)
If objConn.State <> ConnectionState .Closed Then objConn.Close()
updCount = objDAerr.Update (objDS, tblName & "_err")
objConn.Close()
objDS = Nothing
Thank you very much,
Steve
for this? I have already read through everything I could find on Google, to
no avail.
I have a SQL DTS process that will load the file in about 7 minutes. My
code takes about an hour.
Here is a snippet:
Dim objSR As StreamReader = System.IO.File. OpenText(objOri gFile)
Dim curEncoding As System.Text.Enc oding = objSR.CurrentEn coding
Dim loopX As Integer = 0
Do While objSR.Peek > -1
loopX += 1
Dim strRec As String = objSR.ReadLine
arrA = Split(strRec, coldel)
Try
If trailerSigHas Then
If getRecordData(t railerSigPos - 1).ToString =
trailerSig Then
If loopX < 2 Then
Throw New Exception("Empt y extract file")
Else
Exit Do
End If
End If
End If
Dim tmpLngth As Integer = arrA.Length
Dim tmpColCount As Integer =
iNode.Item("col umns").ChildNod es.Count ' objDS.Tables(0) .Columns.Count
If tmpLngth > tmpColCount Then
Throw New Exception("Too many columns in extract
row.")
ElseIf tmpLngth < tmpColCount Then
Throw New Exception("Not enough columns in extract
row.")
End If
Dim newRow As DataRow = objDS.Tables(0) .NewRow
For x As Integer = 0 To objDS.Tables(0) .Columns.Count -
1
Dim tmpSqlCol As String =
objDS.Tables(0) .Columns(x).Cap tion
Dim tmpNode As XmlNode =
iNode.SelectSin gleNode("//columns/column[@sqlcol='" & tmpSqlCol & "']")
'"//columns/column[sqlcol='" & tmpSqlCol & "']")
Dim attrID As String =
tmpNode.Attribu tes("id").Inner Text
Dim xmlCol As Integer = Convert.ToInt32 (attrID)
Dim arrVal As Object = getRecordData(x mlCol - 1)
newRow(x) = GetObjectValue( x, arrVal) ' Does a
Convert to the target column type
Next
objDS.Tables(0) .Rows.Add(newRo w)
Catch ex As Exception
Dim newRow As DataRow = objDS.Tables(1) .NewRow
Dim finalX As Integer = 0
For x As Integer = 0 To objDS.Tables(1) .Columns.Count -
1
Try
Dim strColCaption As String =
objDS.Tables(1) .Columns(x).Cap tion
Dim tmpINode As XmlNode =
iNode.SelectSin gleNode("//columns/column[@sqlcol='" & strColCaption & "']")
Dim attr2ID As String =
tmpINode.Attrib utes("id").Inne rText
Dim sqlColInt As Integer =
Convert.ToInt32 (attr2ID)
If x = 0 Then
newrow(x) = Left(getRecordD ata(sqlColInt -
1).ToString, 8000)
Else
newrow(x) = Left(getRecordD ata(sqlColInt -
1).ToString, 250)
End If
Catch ' non-existent column
Finally
finalX = x
End Try
l001f:
Next
newrow(finalX - 4) = Left(ex.Message , 150)
newrow(finalX - 3) = DateTime.Now
newrow(finalX - 2) = tblName
newrow(finalX - 1) = strFileName
newrow(finalX) = loopX
objDS.Tables(1) .Rows.Add(newRo w)
End Try
Loop
objSR.Close()
objSR = Nothing
End If
objConn.Open()
Dim rowCount As Integer = objDS.Tables(0) .Rows.Count
Dim rowCount2 As Integer = objDS.Tables(1) .Rows.Count
Dim updCount As Integer
If objConn.State <> ConnectionState .Closed Then objConn.Close()
updCount = objDAmain.Updat e(objDS, tblName)
If objConn.State <> ConnectionState .Closed Then objConn.Close()
updCount = objDAerr.Update (objDS, tblName & "_err")
objConn.Close()
objDS = Nothing
Thank you very much,
Steve
Comment