Converting textfile to datatable and converting datatable to textfile

[dday9]

I wrote a function that will convert the contents of a text file to a datatable:

Private Function Text_To_DataTable(ByVal path As String, ByVal delimitter As Char, ByVal header As Boolean) As DataTable
    Dim source As String = String.Empty
    Dim dt As DataTable = New DataTable

    If IO.File.Exists(path) Then
        source = IO.File.ReadAllText(path)
    Else
        Throw New IO.FileNotFoundException("Could not find the file at " & path, path)
    End If

    Dim rows() As String = source.Split({Environment.NewLine}, StringSplitOptions.None)

    For i As Integer = 0 To rows(0).Split(delimitter).Length - 1
        Dim column As String = rows(0).Split(delimitter)(i)
        dt.Columns.Add(If(header, column, "column" & i + 1))
    Next

    For i As Integer = If(header, 1, 0) To rows.Length - 1
        Dim dr As DataRow = dt.NewRow

        For x As Integer = 0 To rows(i).Split(delimitter).Length - 1
            If x <= dt.Columns.Count - 1 Then
                dr(x) = rows(i).Split(delimitter)(x)
            Else
                Throw New Exception("The number of columns on row " & i + If(header, 0, 1) & " is greater than the amount of columns in the " & If(header, "header.", "first row."))
            End If
        Next

        dt.Rows.Add(dr)
    Next

    Return dt
End Function

Private Sub DataTable_To_Text(ByVal table As DataTable, ByVal path As String, ByVal header As Boolean, ByVal delimiter As Char)
    If table.Columns.Count < 0 OrElse table.Rows.Count < 0 Then
        Exit Sub
    End If

    Using sw As IO.StreamWriter = New IO.StreamWriter(path)
        If header Then
            For i As Integer = 0 To table.Columns.Count - 2
                sw.Write(table.Columns(i).ColumnName & delimiter)
            Next
            sw.Write(table.Columns(table.Columns.Count - 1).ColumnName & Environment.NewLine)
        End If

        For row As Integer = 0 To table.Rows.Count - 2
            For col As Integer = 0 To table.Columns.Count - 2
                sw.Write(table.Rows(row).Item(col).ToString & delimiter)
            Next
            sw.Write(table.Rows(row).Item(table.Columns.Count - 1).ToString & Environment.NewLine)
        Next

        For col As Integer = 0 To table.Columns.Count - 2
            sw.Write(table.Rows(table.Rows.Count - 1).Item(col).ToString & delimiter)
        Next
        sw.Write(table.Rows(table.Rows.Count - 1).Item(table.Columns.Count - 1).ToString)
    End Using

End Sub

I would advice against storing data into a textfile, but if you must here is a way to convert it to a datatable and back.

[wild_bill]

Wouldn't it be easier using ADO.NET (OleDb provider)?

[dday9]

Wouldn't it be easier using ADO.NET (OleDb provider)?

Not sure, I haven't played with converting text to datatables using ADO.Net yet. But I know for a fact that the code provided above will work guaranteed.

[dday9]

Well, I did an update to the code. I've added how to convert the data back from a datatable to a textfile. Still, I'd advise against storing data in a textfile, but if you must, these are some useful examples.

[dday9]

I've updated the code again to shrink down the total size of the code.

[techgnome]

I use the TextFieldParser (Microsoft.VisualBasic.FileIO.TextFieldParser) when reading in text files. It can deal with both delimited files as well as fixed-width (I sometimes need to deal with both), but more importantly, when dealing with delimited text, it will take into consideration quote string identifiers.

a much pared down version of the code I'm using:

            Using fileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(DataFile)

                Dim rowData As String()

                fileReader.TextFieldType = FileIO.FieldType.Delimited 
                fileReader.Delimiters = Me.Delimiters()
                fileReader.HasFieldsEnclosedInQuotes = Me.DataContainsQuotes

                While Not fileReader.EndOfData
                    rowData = fileReader.ReadFields()
                    currentLineNumber += 1
                    Dim newRow = myDataTable.NewRow
                    'This next bit works because I have a routine that creates the datatable based on some meta-data (it's used to create the myFields list)... but it shouldn't be too hard to otherwise take the array and set the values for each column in the datarow
                    myFields.ForEach(Sub(r) newRow(r.Name) = IIf(rowData(r.FieldIndex) <> "", rowData(r.FieldIndex), DBNull.Value))
                    myFileDataTable.Rows.Add(newRow)

                End While
            End Using

It actually does a whole lot more than that, but it demonstrates the use well enough.

-tg

[kareninstructor]

If data is clean then as wild_bill suggested we can use

Imports System.Data.OleDb
Public Class DemoReadTxt
    Dim FileName As String = "People.txt"
    Private Sub DemoReadTxt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.Jet.OLEDB.4.0",
                .DataSource = Application.StartupPath & IO.Path.DirectorySeparatorChar
            }

        Builder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited(,)")

        Using cn As New OleDbConnection With
            {
                .ConnectionString = Builder.ConnectionString
            }

            Using cmd As New OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText =
                    <SQL>
                        SELECT * 
                        FROM <%= FileName %>
                    </SQL>.Value
                }

                Dim dt As New DataTable

                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
    End Sub
End Class

[dday9]

I do have another snippet that I didn't post on here, I started using it after wild_bill made the comment about the OleDb provider. This is it:

Private Function CSV_to_DataTable(ByVal path As String) As DataTable
	'Create a new datatable
	Dim dt As DataTable = New DataTable
	'Create a new data connection using the JET engine
	Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", "C:\"))
	'Use the command to select all the records
	Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & path, con)

	'Set up a dataadapter
	Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

	'Open the connection
	con.Open()

	'Set the select command and fill
	da.SelectCommand = cmd
	da.Fill(dt)

	'Close the connection
	con.Close()

	Return dt
End Function