odbc reader for csv in vb.net

Question

I'm writing a program that should handle million of datasets in csv in a short time, my idea was to use odbc because of performance reasons, therefore i read all the data with odbc and save it in memory, thereafter i add parameters and insert it in sql db, here is my code so far:

Using connection As New OdbcConnection("jdbc:odbc:Driver={Microsoft Text Driver (*.txt; *.csv)};" & filePath & "Extensions=csv;Persist Security Info=False;")
                Dim reader As OdbcDataReader

                Dim i As Integer
                Dim r As SeekZeilen

                Dim TextFileTable As DataTable = Nothing


                Dim line As String = reader.Read()
                Me.ParseString(line)

                Dim memStream As New MemoryStream(Encoding.Default.GetBytes(line))

                Using TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(memStream)
                    TextFileReader.TextFieldType = FileIO.FieldType.Delimited
                    TextFileReader.SetDelimiters(";") 
                    r.erste_Zeile = TextFileReader.ReadFields()

                    If TextFileTable Is Nothing Then
                        TextFileTable = New DataTable("TextFileTable")

                        For i = 0 To r.erste_Zeile.Length - 1
                            Dim Column As New DataColumn(r.erste_Zeile(i))

                            Column.ReadOnly = True
                            TextFileTable.Columns.Add(Column)
                        Next
                    End If
                    DataGridView1.DataSource = TextFileTable
                End Using

                While reader.HasRows
                    line = reader.Read()
                    Me.ParseString(line)
                    memStream = New MemoryStream(Encoding.Default.GetBytes(line))

                    Using TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(memStream)
                        TextFileReader.TextFieldType = FileIO.FieldType.Delimited
                        TextFileReader.SetDelimiters(";") 

                        DataGridView1.DataSource = TextFileTable
                        Try
                            r._Rest = TextFileReader.ReadFields()


                            ReplaceChars(r._Rest)

                            If Not r._Rest Is Nothing Then
                                Dim oSQL As New DBUmgebung.cdb.SQL()
                                oSQL.init()
                                AddParameters(oSQL, r)
                                oSQL.ausfuehrenSQL(DBUmgebung.cdb.KSQLCommand.INSERT, _table, "")
                                Dim dtRow As DataRow = TextFileTable.NewRow

                                For i = 0 To r._Rest.Length - 1

                                    dtRow(i) = r._Rest(i).ToString()
                                Next

                                TextFileTable.Rows.Add(dtRow)
                                DataGridView1.Refresh()
                                Application.DoEvents()
                            End If
                        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                            MsgBox("Error! " & ex.Message & _
                            "")

                        Catch sqlEx As SqlException
                            MessageBox.Show(sqlEx.Message)

                            rtbSql.Focus()
                            Exit For
                        Catch ex As Exception
                            MessageBox.Show(ex.Message)
                            rtbSql.Focus()
                            Exit For

                        End Try
                    End Using
                End While
                reader.Close()
            End Using

the problem is that i get null pointer exception for a unknown reason, does anyone have idea what i did wrong? is it probably because my odbc reader is not properly initialized?

Answer 1

Try this. This will read the csv file as all text into a datatable. Once in the Datatable you could then insert the records into SQL. You can always adjust this to handle multiple csv files.

Friend Shared Function GetExcelFile(ByVal strFileName As String, ByVal strPath As String) As DataTable

    Try

        Dim dt As New DataTable

            Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""Text;HDR=Yes;FMT=Delimited\"""
            Dim conn As New OleDb.OleDbConnection(ConStr)
            Dim da As New OleDb.OleDbDataAdapter("Select * from " & strFileName, conn)
            da.Fill(dt)

        Return dt

    Catch ex As Exception
        Return Nothing
    End Try

End Function