Help! Data-type changing during text import

[KCcasey]

Hi All,

I've got a strange problem (at least i think so). I'm importing the contents of a text file to a dataset, and the datatype of the destination column is changing from string to decimal for no reason that I can determine...

When I pass a text file with 13 rows, the datatype of the first column is a string, when I pass 14 rows the datatype is decimal (!?).

Anyone got any ideas?

Heres my code for loading the text file:

Private Function loadTheTextFile()

        Dim file As String = "C:\Work\BlahBlah\blahblah2.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

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

                myRow = myTable.NewRow()
                myRow("CbaAccount") = currentRow

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

            '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"

        Console.WriteLine(dsLookupData.Tables("TextFile").Columns.Item(0).DataType())

    End Function

Heres my textfile:

PayGlobal
Moffat Ltd.
GL Journal
Pay Sequence : 1119 (Period : 31) Period End Date : 03/11/2007
21/11/2007 3:15:30 p.m. Page 1

GL Code Description This Export
**.**.**.3080 250.39
11.03.01.3010 1038.46
11.03.01.3090 9.96
11.03.02.3010 861.54
11.03.02.3090 19.95
11.05.12.3020 1692.30
11.05.12.3090 27.61

 

[NoCoolHandle]

If it ether fixed with or tab delimited, isn't it easier to just open a stream reader and then parse it 1 line at a time.