First, we'll look at the basic TextFieldParser example:
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Projects\GLDOC.TXT")
'initialize
Dim currentRow As String()
Dim strVoucherNumber As String
myReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
myReader.Delimiters = New String() {"|"}
'Loop through all of the fields in the file.
'If any lines are corrupt, report an error and continue parsing.
While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
' Include code here to handle the row.
strVoucherNumber = currentRow(0)
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
End While
End Using
End Sub
That code is almost straight out of the Microsoft example.
Now, we'll add in the code to make this an integration. The difficulty is that we have to include two or more rows in the integration. How will we know when we're read enouth lines and it's time to ingetrate?
In our sample file (see above) the first two lines start out with 00001, and the third line is 00002. So, we need to read in the first two lines, and then stop and do the integration.
To do this, we're going to use two classes to represent the GL data. We'll read the data into the classes and then pass the classes to another routine to do the work.
The classes:
Public Class GLHeader
Public Reference As String
Public TransactionDate As Date
Public GLLines As New List(Of GLLines)
End Class
Public Class GLLines
Public AccountNumber As String
Public Amount As Decimal
End Class
This is the code that does the work. The first routine parses the text document into classes, the classes are passed to an integration routine that sends the data to Dynamics. Enjoy
Public Class TextFieldParserExample
Sub Parse()
'read the file into our TextFieldParser objet
Using myParser As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\temp\GLDOC.TXT")
'initialize
Dim currentRow As String()
Dim strVoucherNumber As String
Dim strReference As String
Dim dtDate As Date
Dim decAmount As Decimal
Dim strAccountNumber As String
Dim oGLHeader As eConnect11Lib.GLHeader = Nothing
'set up the parser object
myParser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
'our sample file is 'pipe' delimited
myParser.Delimiters = New String() {"|"}
'Loop through all of the fields in the file.
'If any lines are corrupt, report an error and continue parsing.
While Not myParser.EndOfData
Try
'call a row into the 'currentRow' array
currentRow = myParser.ReadFields()
'read the fields into variables
strVoucherNumber = currentRow(0)
strReference = currentRow(1)
dtDate = currentRow(2).ToString.Substring(0, 2) + "/" + currentRow(2).ToString.Substring(2, 2) + "/" + currentRow(2).ToString.Substring(4, 4)
decAmount = currentRow(3)
strAccountNumber = currentRow(4)
'if we don't have a header, then we need one
If oGLHeader Is Nothing Then
oGLHeader = New eConnect11Lib.GLHeader
oGLHeader.Reference = strReference
oGLHeader.TransactionDate = dtDate
End If
'create a 'lines' object
Dim oGLLines As New eConnect11Lib.GLLines
oGLLines.Amount = decAmount
oGLLines.AccountNumber = strAccountNumber
'add it to the header
oGLHeader.GLLines.Add(oGLLines)
'peek ahead one row. If the journal entry number is the same, we keep going
Dim strPeek As String = myParser.PeekChars(5)
If strPeek <> strVoucherNumber Then
'if it is a new journal, we need to process the current journal
Integrate(oGLHeader)
'clear out the header for the next pass
oGLHeader = Nothing
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is invalid. Skipping")
End Try
End While
End Using
End Sub
Public Sub Integrate(oGLHeader As GLHeader)
Try
Dim strReturnDoc As String
'get the Server and Database from the configuration file
Dim strSQLServer As String = System.Configuration.ConfigurationManager.AppSettings("SQLServer")
Dim strDabase As String = System.Configuration.ConfigurationManager.AppSettings("Database")
'declare our eConnect classes
Dim oeConnectType As New Microsoft.Dynamics.GP.eConnect.Serialization.eConnectType
Dim oGLTransactionType As New Microsoft.Dynamics.GP.eConnect.Serialization.GLTransactionType
'this is our eConnect common functions class, you can find it here: http://dyndeveloper.com/thread.aspx?Threadid=1117
Dim oeConnectFunctions As New eConnect11Lib.GP11.eConnectFunctions(strSQLServer, strDabase)
'============================================================================
'create the document details
'============================================================================
Dim intCurrentLine As Int16 = 0
For Each oGLLines As GLLines In oGLHeader.GLLines
'declare an object for the detail line
Dim otaGLTransactionLineInsert As New Microsoft.Dynamics.GP.eConnect.Serialization.taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert
'populate the detail line
With otaGLTransactionLineInsert
.BACHNUMB = "MYBATCH"
If oGLLines.Amount < 0 Then
.CRDTAMNT = Math.Abs(oGLLines.Amount)
Else
.DEBITAMT = Math.Abs(oGLLines.Amount)
End If
.ACTNUMST = oGLLines.AccountNumber
.DSCRIPTN = oGLHeader.Reference
ReDim Preserve oGLTransactionType.taGLTransactionLineInsert_Items(intCurrentLine)
oGLTransactionType.taGLTransactionLineInsert_Items(intCurrentLine) = otaGLTransactionLineInsert
End With
intCurrentLine += 1
Next
'============================================================================
'create the document header
'============================================================================
Dim otaGLTransactionHeaderInsert As New Microsoft.Dynamics.GP.eConnect.Serialization.taGLTransactionHeaderInsert
'populate the header data
'only the minimum fields are required
With otaGLTransactionHeaderInsert
.BACHNUMB = "MYBATCH"
.TRXDATE = oGLHeader.TransactionDate
.REFRENCE = oGLHeader.Reference
.TRXTYPE = 0
End With
'assign the header to the master
oGLTransactionType.taGLTransactionHeaderInsert = otaGLTransactionHeaderInsert
ReDim Preserve oeConnectType.GLTransactionType(0)
oeConnectType.GLTransactionType(0) = oGLTransactionType
'this is our eConnect common functions class, you can find it here: http://dyndeveloper.com/thread.aspx?Threadid=1117
strReturnDoc = oeConnectFunctions.CreateTransactionEntity(oeConnectType)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class