How do I parse a comma delimited file into a dataset using ado

The issue I have is that the file is labelled .log, but is basically a csv file. I was hoping I could just read it straight into a dataset.
I am not to sure what the best method is, i suspect at this stage that extra commas and quoting isnt an issue(...yet)

I have tried this connection...

strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & qStrSQL(gcConfig.CounterLog) & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""

but it doesnt seem to like it :o(

Milkus1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
Sample code....


'FORM 1

Imports System.IO
Imports System.Data.OleDb
Public Class frmAddTable
    Inherits System.Windows.Forms.Form
    Dim strFlds As String
    Dim strPath As String = Application.StartupPath
    Dim strFile As String = "Test.txt"      '<---- Input file
    Dim sSQL As String
    Dim sPathtoMDB As String = "C:\Visual Studio Projects\TestArea\Temp.mdb"     '<----Make a temp database
    ' Dim Con1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strPath & "\" & strFile & ";")
    Dim Con1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPathtoMDB & ";")

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents ListView1 As System.Windows.Forms.ListView
    Friend WithEvents Button3 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.Button2 = New System.Windows.Forms.Button
        Me.ListView1 = New System.Windows.Forms.ListView
        Me.Button3 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(20, 144)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(112, 23)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "1. CreateSchema"
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(144, 144)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(120, 23)
        Me.Button2.TabIndex = 1
        Me.Button2.Text = "2.  Add Table"
        '
        'ListView1
        '
        Me.ListView1.Location = New System.Drawing.Point(20, 8)
        Me.ListView1.Name = "ListView1"
        Me.ListView1.Size = New System.Drawing.Size(452, 108)
        Me.ListView1.TabIndex = 2
        '
        'Button3
        '
        Me.Button3.Location = New System.Drawing.Point(288, 144)
        Me.Button3.Name = "Button3"
        Me.Button3.Size = New System.Drawing.Size(152, 23)
        Me.Button3.TabIndex = 3
        Me.Button3.Text = "3.  Read to ListView"
        '
        'frmAddTable
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(524, 185)
        Me.Controls.Add(Me.Button3)
        Me.Controls.Add(Me.ListView1)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.Button1)
        Me.Name = "frmAddTable"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region
#Region "Click Events"
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        CreateSchema("Test", 4)
        'or
        ' CreateSchema("Test", 4, "", "CustName,CustNumber,CustPhone,Product")
        'The name of the table is assumed to be the same name as the file,
        'and all files are assumed to be in the same path (which is how all my data is set up).
        MessageBox.Show("Create Schema Done")
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        AddTable("Test")
    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ReadTextTODataset()
        PopulateListFromDS(DS)
    End Sub
#End Region
#Region "Make Data"
    Private Sub CreateSchema(ByVal strTable As String, ByVal intDesc As Integer, Optional ByVal strDlmt As String = "", Optional ByVal strHdrs As String = "")
        Dim sr As StreamReader, sw As StreamWriter
        Dim strPath As String = Application.StartupPath
        Dim strFile As String = strTable & ".txt"
        'Dim strFile As String = strTable & ".csv"
        Dim x As Integer
        Dim arrHdrs As String()

        'Read the headers from the text file, if none were specified...
        If strHdrs = "" Then
            sr = New StreamReader(strPath & "\" & strFile)
            If strDlmt = "" Then
                arrHdrs = Split(sr.ReadLine, vbTab)
            Else
                arrHdrs = Split(sr.ReadLine, strDlmt)
            End If
            sr.Close()
            sr = Nothing
        Else
            arrHdrs = Split(strHdrs, ",")
        End If

        ' create the Schema.ini file and write the appropriate lines to it...
        sw = New StreamWriter(strPath & "\" & "Schema.ini", False)
        sw.WriteLine("[" & strFile & "]")
        If strHdrs = "" Then
            sw.WriteLine("ColNameHeader=False")   'True
        Else
            sw.WriteLine("ColNameHeader=True")    'False
        End If
        If strDlmt = "" Then
            sw.WriteLine("Format=TabDelimited")
        Else
            sw.WriteLine("Format=Delimited(" & strDlmt & ")")
        End If
        sw.WriteLine("MaxScanRows=0")
        ' loop through the headers and create a Col line in the schema.ini file including the data type
        ' also, add the headers to a string variable which will be used to create the file in the DB (using SQL)
        For x = 0 To UBound(arrHdrs)
            If arrHdrs(x) = "" And x = UBound(arrHdrs) Then
                Exit For
            ElseIf arrHdrs(x) = "" Then
                arrHdrs(x) = "F" & x
            End If
            If x < intDesc Then
                'USE this structure if you know the field length you want
                'Select Case x
                '    Case 0 'Col1=DirPermissions Text Width 10
                '        sw.WriteLine("Col1=DirPermissions Text Width 10")
                '    Case 1 'Col2=Users          Text Width 20
                '        sw.WriteLine("Col2=Users          Text Width 20")
                '    Case 2 'Col3=FileSize       Text Width 12
                '        sw.WriteLine("Col3=FileSize       Text Width 12")
                '    Case 3 'Col4=DateTime       Text Width 12
                '        sw.WriteLine("Col4=DateTime       Text Width 12")
                '    Case 4 'Col5=FileName       Text Width 30
                '        sw.WriteLine("Col5=FileName       Text Width 30")
                '    Case 5 'Col6=DirLevel       Text Width 5
                '        sw.WriteLine("Col6=DirLevel       Text Width 5")
                'End Select

                'strFlds += "[" & Trim(arrHdrs(x)) & "] char(20), "
                'sw.WriteLine("Col" & x + 1 & "=""" & Trim(arrHdrs(x)) & """ Text Width 20")

                'USE this structure if you do NOT know the field length adds length 10
                strFlds += "[" & Trim(arrHdrs(x)) & "] char(" & Trim(arrHdrs(x).Length) + 10 & "), "
                sw.WriteLine("Col" & x + 1 & "=""" & Trim(arrHdrs(x)) & """ Text Width " & Trim(arrHdrs(x)).Length + 10)
            Else
                strFlds += "[" & Trim(arrHdrs(x)) & "] Single, "
                sw.WriteLine("Col" & x + 1 & "=""" & Trim(arrHdrs(x)) & """ Decimal")
            End If
        Next
        strFlds = "(" & strFlds.Substring(0, strFlds.Length - 2) & ")"
        sw.Close()
        sw = Nothing
    End Sub
    Private Sub AddTable(ByVal strTable As String)
        Try
            ' delete the old table
            RunSQL("DROP TABLE [" & strTable & "]")
            ' create the new table (using the string variable)
            RunSQL("CREATE TABLE [" & strTable & "] " & strFlds)
            ' insert the contents of the text file into the table
            ' (the insert will fail if the Schema.ini file is not
            ' present in the same directory as the text file)
            RunSQL("INSERT INTO [" & strTable & "] SELECT * FROM [Text;Database=" & strPath & "].[" & strFile & "]")
            MessageBox.Show("Added Table")
        Catch exp As Exception
            MsgBox(exp.Message, MsgBoxStyle.OKOnly)
            End
        End Try
    End Sub
    Private Sub RunSQL(ByVal strSQL As String)
        Dim Cmd1 = New OleDb.OleDbCommand(strSQL, Con1)
        Try
            Cmd1.Connection.Open()
            Cmd1.ExecuteNonQuery()
            Cmd1.Connection.Close()
        Catch e As OleDb.OleDbException
            Dim errorMessages As String
            Dim i As Integer

            For i = 0 To e.Errors.Count - 1
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                               & "Message: " & e.Errors(i).Message & ControlChars.Cr _
                               & "NativeError: " & e.Errors(i).NativeError & ControlChars.Cr _
                               & "Source: " & e.Errors(i).Source & ControlChars.Cr _
                               & "SQLState: " & e.Errors(i).SQLState & ControlChars.Cr
            Next i

            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
            log.Source = "My Application"
            log.WriteEntry(errorMessages)
            MessageBox.Show("An exception occurred. Please contact your system administrator.")
        Catch exp As Exception
            Cmd1.Connection.Close()
        End Try
    End Sub
    Private Sub ReadTextTODataset()
        'this method uses the connection syntax and a Schema.ini file to control the format of the text file
        'Dim oCon As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection( _
        ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" & _
        ' "Extended Properties='Text;HDR=NO;FMT=FixedLength'")

        'this method uses a Schema.ini file to control the format of the text file
        Dim oCon As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection( _
             "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" & _
             "Extended Properties='Text;'")
        Try
            ADOAdapter = New OleDb.OleDbDataAdapter("select distinct * from " & strFile, oCon)
            ADOAdapter.AcceptChangesDuringFill = False
            ADOAdapter.Fill(DS)
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub
    Private Sub PopulateListFromDS(ByVal DS)
        Dim s As String
        Dim i As Integer

        With ListView1
            .Columns.Add("Name", 80, HorizontalAlignment.Right)
            .Columns.Add("Number", 50, HorizontalAlignment.Left)
            .Columns.Add("Phone", 80, HorizontalAlignment.Right)
            .Columns.Add("Product", 90, HorizontalAlignment.Left)
        End With

        For Each myDataRow In DS.Tables(0).Rows
            With ListView1
                .Items.Add(myDataRow(0).ToString())
                .Items(iCounter).SubItems.Add(UCase(myDataRow(1).ToString()))
                .Items(iCounter).SubItems.Add(UCase(myDataRow(2).ToString()))
                .Items(iCounter).SubItems.Add(UCase(myDataRow(3).ToString()))
                iCounter += 1
            End With
        Next
        ListView1.View = View.Details
    End Sub
#End Region
End Class
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.