Problem using BulkCopy to imput delimited text file into SQL table

[wisaunders]

the file I'm importing has > 200,000 records

I have one problem:

One of the columns in the .txt file (customerID) has almost all Inetger values except for about 30 records. Those 30 records have one letter in the customerID field (M123456). The field they are going into is VARCHAR(1000) . All of the integers go in correctly but for some reason the cutomerID values that stert with a letter (M123456) are NULL.

Any help? I'm stumped.

The code i am using is below

 Dim fieldDelimeter As String = TextBox3.Text
 
            Dim timeStamp As String = Now.ToString("yyyyMMddHHmmss")
            Dim importFile As New FileInfo(TextBox1.Text)
            If importFile.Exists Then
 
 
 
 
 
                Dim originalFileName As String = importFile.Name
                Dim newFileName As String = originalFileName.Replace(".", "") & ".txt"
                Dim path As String = importFile.DirectoryName
                File.Move(path & "\" & originalFileName, path & "\" & newFileName)
 
                Dim schemaFile As New FileInfo(importFile.DirectoryName & "\schema.ini")
 
                Dim schemaContents As String
                schemaContents = "[" & newFileName & "]" & System.Environment.NewLine
                schemaContents = schemaContents & "Format=Delimited(" & fieldDelimeter & ")" & System.Environment.NewLine
                If TextBox4.Text > "" Then
                    schemaContents = schemaContents & "TextDelimiter=" & TextBox4.Text
                Else
                    schemaContents = schemaContents & "TextDelimiter=none"
                End If
                Dim fs As FileStream = schemaFile.Create()
                fs.Close()
 
                Dim objReader As StreamWriter
                objReader = schemaFile.AppendText()
                objReader.Write(schemaContents)
                objReader.Close()
 
                Dim strCsvConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";" & _
                                "Extended Properties='text;FMT=Delimited;HDR=NO'"
 
 
 
                Dim cn As New OleDb.OleDbConnection(strCsvConn)
 
                Try
 
                    Dim myCommand As New System.Data.OleDb.OleDbCommand( _
                                                    "SELECT COUNT(*) FROM " & newFileName, cn)
                    cn.Open()
                    Dim records As Integer = 0
                    Dim newReader As OleDb.OleDbDataReader
                    newReader = myCommand.ExecuteReader()
                    newReader.Read()
                    records = newReader.GetInt32(0)
                    cn.Close()
 
                    myCommand = New System.Data.OleDb.OleDbCommand( _
                                "SELECT * FROM " & newFileName, cn)
 
 
                    cn.Open()
                    Dim i As Integer = 0
                    Dim columns As Integer = myCommand.ExecuteReader().FieldCount
                    Me.ProgressBar2.Maximum = records
                    Dim strCreateTable As String
                    strCreateTable = "CREATE TABLE " & ComboBox2.Text & "_" & timeStamp & " (" & System.Environment.NewLine
                    Do While i < columns - 1
                        strCreateTable = strCreateTable & "   [COLUMN " & i.ToString & "] VARCHAR(1000)," & System.Environment.NewLine
                        i += 1
                    Loop
                    strCreateTable = strCreateTable & "   [COLUMN " & i.ToString & "] VARCHAR(1000)" & System.Environment.NewLine
                    strCreateTable = strCreateTable & ")" & System.Environment.NewLine
                    cn.Close()
 
                    Dim notes As New DataSet
                    notes = QueryDatabase(strCreateTable, Me.sourceDatabaseName.Text)
 
 
                    cn.Close()
 
 
 
                    cn.Open()
 
                    Dim dr As OleDb.OleDbDataReader = myCommand.ExecuteReader
 
 
                    Using bulkCopy As New SqlClient.SqlBulkCopy("server=" & Me.sourceServerName.Text & ";user=" & Me.sourceUserID.Text & ";password=" & Me.sourcePassword.Text & ";database=" & Me.sourceDatabaseName.Text)
 
 
                        AddHandler bulkCopy.SqlRowsCopied, AddressOf OnSqlRowsCopied
                        bulkCopy.BulkCopyTimeout = 1200
                        bulkCopy.NotifyAfter = 100
                        bulkCopy.DestinationTableName = ComboBox2.Text & "_" & timeStamp
                        bulkCopy.WriteToServer(dr)
                        cn.Close()
                        File.Move(path & "\" & newFileName, path & "\" & originalFileName)
                        schemaFile = New FileInfo(importFile.DirectoryName & "\schema.ini")
                        schemaFile.Delete()
                        populateRawDataTablesGrid()
 
                        Me.ProgressBar2.Value = 0
                        MessageBox.Show("Data successfully imported.")
                    End Using
                Catch ex As Exception
                    MessageBox.Show(ex.ToString)
                    cn.Close()
                    File.Move(path & "\" & newFileName, path & "\" & originalFileName)
                    schemaFile = New FileInfo(importFile.DirectoryName & "\schema.ini")
                    schemaFile.Delete()
                    Me.ProgressBar2.Value = 0
                End Try
            Else
                Throw New FileNotFoundException()
            End If