VB newbie: text file manipulation

[KCcasey]

Hi All,

First off, thanks in advance for your time!

I'm a VB newbie, and I'm struggling with my first app.

The scenario: I'm trying to manipulate a tab-delimited text file based on the contents of a MS Access table.

My Approach: I'm at the point where I've imported the text file and the MS Access table into separate datatables.

My Question(s): I need to update the text file datatable based on values in the MS Access datatable, but I'm not sure how to progress. I can't use a relationship, because the text file contains both header and line data (the header data breaks the parent child relationship). What would the most sensible approach be from here?

Should I try :

A) split the text file into header and line datatables?
B) Should I loop through the text file looking for line data and then loop through the MS Access datatable looking for a match?
C) taking a VB.NET course? ;)

Any pointers would be much appreciated. Also, critique of my entire approach would be good - - is there a better/easier way to achieve my goal?

I've attached a sample of my text file, and a .doc of my MS Access table.

Heres my relevant code (sorry its messy):

Private Function loadTheLookupTable()

        '1) Create a CONNECTION
        Dim cnConnection As New OleDb.OleDbConnection
		
        '2) Set the connection
        cnConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\work\blahblah\CBA_SAP_AccountsLookup.mdb"

        '3) Create a DATASET

        '4) Create a DATA ADAPTOR
        Dim daDataAdaptor As New OleDb.OleDbDataAdapter
        daDataAdaptor = New OleDb.OleDbDataAdapter("select * from qryGroupedLookup", cnConnection)
        '5) Fill a DATASET using the DATA APAPTOR
        daDataAdaptor.Fill(dsLookupData, "CBA_SAP_Lookup")

        'Open the connection
        'Close the connection
        cnConnection.Close()


    End Function

    Private Function loadTheTextFile()

        Dim file As String = "C:\work\blahblah.txt"
        Dim cmdtxt As String = ("SELECT * FROM " & file & "")

        '1) Create a CONNECTION
        Dim cnConnection As New Odbc.OdbcConnection

        '2) Set the connection
        cnConnection.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\work\;Extensions=asc,csv,tab,txt;"

        '4) Create a DATA ADAPTOR
        Dim daDataAdaptor As New Odbc.OdbcDataAdapter(cmdtxt, cnConnection)
        ''daDataAdaptor = New Odbc.OdbcCommand(cmdtxt, cnConnection)

        ' Create a DataTable. 
        Dim myTable As DataTable = New DataTable("TextFile")

        ' Create a DataColumn and set various properties. 
        Dim myColumn As DataColumn = New DataColumn
        myColumn.DataType = System.Type.GetType("System.String")
        myColumn.AllowDBNull = False
        myColumn.Caption = "CbaAccount"
        myColumn.ColumnName = "CbaAccount"
        myColumn.DefaultValue = ""

        ' Add the column to the table. 
        myTable.Columns.Add(myColumn)

        '5) Fill a DATASET using the DATA APAPTOR
        daDataAdaptor.Fill(dsLookupData, "TextFile")

        Dim myRow As DataRow
        Dim col As DataColumn
        Dim count As Integer = 0
        Dim currentRow As String
        Dim IsNull As Boolean

        For Each col In dsLookupData.Tables("TextFile").Columns

            IsNull = IsDBNull(dsLookupData.Tables("TextFile").Rows(count).Item(0))

            'Test for blank lines in the text file
            If IsDBNull(dsLookupData.Tables("TextFile").Rows(count).Item(0)) = False Then

                myRow = myTable.NewRow()
                currentRow = dsLookupData.Tables("TextFile").Rows(count).Item(0)

                myRow("CbaAccount") = currentRow

                ' Be sure to add the new row to the DataRowCollection. 
                myTable.Rows.Add(myRow)

                'update the rows contents
                dsLookupData.Tables("TextFile").Rows(count).Item(0) = dsLookupData.Tables("TextFile").Rows(count).Item(0).Substring(0, 2)

                'increment the row count
                count = count + 1
            Else
                'increment the row count
                count = count + 1
            End If
        Next

        'Rename the column to a meaningful name instead of the arbitary first line value
        dsLookupData.Tables("TextFile").Columns.Item(0).ColumnName = "CbaAcc"

    End Function

Thanks for your help!

Casey.