?
Solved

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

Posted on 2005-03-16
1
Medium Priority
?
297 Views
Last Modified: 2008-02-01
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(

0
Comment
Question by:Milkus1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 27

Accepted Solution

by:
planocz earned 375 total points
ID: 13564383
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question