How do I populate a dataset from a .dat file. 2

[chandlm]

I have a number of files with a .dat extension. I want to be able to populate a dataset with a the contents of the files which are comma separated.

I have tried using the Microsoft Text Driver without success.

Can anyone give any pointers.


Hope this helps.

Matt

 

[RiverGuy]

Which program generated the .dat file?

Does it how column headers? Are there any other sort of headers?

Can you provide a sample of what the data in the file looks like?

 

[chandlm]

file looks like this and appears to be just a normal comma delimited file.

"H",19,04032009081510,"INS"
"D","INS:116469008","15230000073",04032009080000,"CANCEL","NOACCESS"
"D","INS:118747017","16067000309",04032009080200,"JOBSTARTED","Job Has Started"
"D","INS:105075009","11567000052",04032009080300,"JOBSTARTED","Job Has Started"
"D","INS:105075009","11567000052",04032009080300,"APPTMET","Appointment Met."
"D","INS:110508010","13206000105",04032009080300,"JOBSTARTED","Job Has Started"
"D","INS:110508010","13206000105",04032009080300,"APPTMET","Appointment Met."
"D","INS:110508010","13206000105",04032009080400,"CANCEL","NOACCESS"
"D","INS:124401035","18011001131",06022009080000,"JOBSTARTED","Job Has Started"
"D","INS:124401035","18011001131",26022009150000,"COMPLETE","Job is Completed."
"D","INS:108270012","12564000699",04032009080600,"JOBSTARTED","Job Has Started"
"D","INS:113221011","14046000367",04032009080600,"JOBSTARTED","Job Has Started"
"D","INS:113221011","14046000367",04032009080600,"APPTMET","Appointment Met."
"D","INS:101503007","10496000038",13022009080000,"JOBSTARTED","Job Has Started"
"D","INS:101503007","10496000038",02032009150000,"COMPLETE","Job is Completed."
"D","INS:110113007","13121001077",04032009081100,"JOBSTARTED","Job Has Started"
"D","INS:122643022","17383000055",04032009081400,"JOBSTARTED","Job Has Started"
"D","INS:122643022","17383000055",04032009081400,"APPTMET","Appointment Met."
"D","INS:127792056","00004912453",04032009081500,"JOBSTARTED","Job Has Started"
"D","INS:127792056","00004912453",04032009081500,"APPTMET","Appointment Met."


I would like to be able to add this into a dataset as I need to be able to look at each record individually




Hope this helps.

Matt

 

[RiverGuy]

I think the first record is a "Header" record and the ones below are "Details."

You won't be able to read it into a DataSet as is because the header record appears to have four columns whereas the details appear to have six.

So you may end up reading the file into strings, parsing it, and loading header and detail DataTables from code.

Look at the functions
System.IO.File.ReadAllText and
System.IO.File.ReadAllLines

So you might end up with something like:

        'Create a Header DataTable
        Dim Header As New DataTable
        Header.TableName = "Header"
        Header.Columns.Add("Column1", System.Type.GetType("System.String"))
        Header.Columns.Add("Column2", System.Type.GetType("System.String"))
        Header.Columns.Add("Column3", System.Type.GetType("System.String"))
        Header.Columns.Add("Column4", System.Type.GetType("System.String"))

        'Create a Detail DataTable
        Dim Details As New DataTable
        Details.TableName = "Details"
        Details.Columns.Add("Column1", System.Type.GetType("System.String"))
        Details.Columns.Add("Column2", System.Type.GetType("System.String"))
        Details.Columns.Add("Column3", System.Type.GetType("System.String"))
        Details.Columns.Add("Column4", System.Type.GetType("System.String"))
        Details.Columns.Add("Column5", System.Type.GetType("System.String"))
        Details.Columns.Add("Column6", System.Type.GetType("System.String"))

        'Read the lines from your text file
        Dim lines() As String
        lines = System.IO.File.ReadAllLines("somepath")
        For i As Integer = 0 To lines.Length - 1
            Dim columns() As String = lines(i).Replace(Chr(34), "").Split(",")
            If lines(i).Substring(1, 1) = "H" Then 'This is a header record
                Dim dr As DataRow = Header.NewRow
                dr.Item(0) = columns(0) : dr.Item(1) = columns(1) : dr.Item(2) = columns(2) : dr.Item(3) = columns(3)
                Header.Rows.Add(dr)
            ElseIf lines(i).Substring(1, 1) = "D" Then 'This is a detail record
                Dim dr As DataRow = Details.NewRow
                dr.Item(0) = columns(0) : dr.Item(1) = columns(1) : dr.Item(2) = columns(2) : dr.Item(3) = columns(3)
                dr.Item(4) = columns(4) : dr.Item(5) = columns(5)
                Details.Rows.Add(dr)
            End If
        Next

        'Add the two DataTables to a DataSet
        Dim SomeDataSet As New DataSet
        SomeDataSet.Tables.Add(Header)
        SomeDataSet.Tables.Add(Details)

Of course, if your quotes are text qualifiers, then you need to add logic to look for those and allow commas.

An alternative would be to read the one .dat file, separate out the headers from the details, write two new files, and attempt to use an ODBC driver against those new files.

 

[DaveInIowa]

Have a look at the TextFieldParser object.

http://msdn.microsoft.com/en-us/library/w30ffays.aspx

 

[chandlm]

DaveInIowa & RIVERGUY

Star to both of you as you pushed me in the right direction.

I used a combination of both your suggestions and came up with the following. Just what I needed.

        Dim dsheader As New DataSet
        Dim dsdetails As New DataSet
        dsheader.Tables.Add()
        For a As Integer = 1 To 4
            dsheader.Tables(0).Columns.Add("Col" & a)
        Next
        dsdetails.Tables.Add()
        For a As Integer = 1 To 6
            dsdetails.Tables(0).Columns.Add("Col" & a)
        Next

        Using myreader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\temp\test.dat")
            myreader.TextFieldType = FileIO.FieldType.Delimited
            myreader.SetDelimiters(",")
            Dim CurrentRow As String()
            Dim HEADER As String = ""

            While Not myreader.EndOfData
                Try

                    CurrentRow = myreader.ReadFields()
                    If CurrentRow(0) = "D" Then
                        'MsgBox("DETAILS" & vbCrLf & CurrentRow(1))
                        dsdetails.Tables(0).LoadDataRow(CurrentRow, True)
                    Else
                        dsheader.Tables(0).LoadDataRow(CurrentRow, True)



                    End If
                Catch ex As _
                    Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & " is invalid.")
                End Try
            End While
        End Using

Hope this helps.

Matt