Results 1 to 6 of 6

Thread: How can i import text from .txt into datagridview

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    11

    Post How can i import text from .txt into datagridview

    I want to import text into columns into a datagridview

    this is my .txt:
    Code:
    	test1 	|	BabyPandah	|	Test	|	1	|
    ---------------------------------------------
    i want "test1" in the first column
    i want "BabyPandah" in the 2nd column
    i want "Test" in the 3th column
    i want "1" in 4th first column

    Please comment, and thanks for ur help!

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,990

    Re: How can i import text from .txt into datagridview

    I would hope that what you showed isn't REALLY the text, is it? Do you really have text formatted like that with | symbols separating things? The formatting would have had to be done with loads of spaces or tabs, which will get in the way. The | symbol isn't as bad.

    The ideal would be to be able to use ADO.NET to query the text file as if it were a database table, and by doing so, populate a datatable. That datatable could then be set as the datasource for the DGV. ADO.NET will query text files, but not ALL text files. It's great with CSV files, and it may be able to handle a file with | symbols separating items, but all those spaces would make for some ugly output. Therefore, I ask whether the text really looks like that?
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    11

    Re: How can i import text from .txt into datagridview

    Last edited by babypandah; Dec 21st, 2017 at 01:13 PM.

  4. #4

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    11

    Re: How can i import text from .txt into datagridview

    Quote Originally Posted by Shaggy Hiker View Post
    I would hope that what you showed isn't REALLY the text, is it? Do you really have text formatted like that with | symbols separating things? The formatting would have had to be done with loads of spaces or tabs, which will get in the way. The | symbol isn't as bad.

    The ideal would be to be able to use ADO.NET to query the text file as if it were a database table, and by doing so, populate a datatable. That datatable could then be set as the datasource for the DGV. ADO.NET will query text files, but not ALL text files. It's great with CSV files, and it may be able to handle a file with | symbols separating items, but all those spaces would make for some ugly output. Therefore, I ask whether the text really looks like that?
    it actually is, my export code is:
    Code:
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            For i As Integer = 0 To DataGridView1.Rows.Count - 2 Step +1
    
                For j As Integer = 0 To DataGridView1.Columns.Count - 1 Step +1
    
                    writer.Write(vbTab & DataGridView1.Rows(i).Cells(j).Value.ToString() & vbTab & "|")
    
                Next
    
                writer.WriteLine("")
                writer.WriteLine("---------------------------------------------")
    
            Next
            writer.Close()
            MessageBox.Show("Data Exported")
    
        End Sub
    But i would like to change it to a way it is easy to import again

  5. #5
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: How can i import text from .txt into datagridview

    I would encourage to implement an ADO.NET solution as Shaggy Hiker suggested, especially since you have control over how the data is exported. You can continue to use the pipe as a delimiter, but there is absolutely no reason to implement the dashes to separate the rows; after all shouldn't new lines represent a new row?

    Take a look at this import/export code that converts the data from a pipe delimited text file to a DataTable and from a DataTable back to a pipe delimited text file:
    Code:
    Namespace DelimitedData
    
        Public Module Convert
    
            Public Function ToDataTable(ByVal path As String, ByVal delimiter As Char) As DataTable
                Dim dt As DataTable = New DataTable()
                Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
                    Try
                        con.ConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={IO.Path.GetDirectoryName(path)};Extended Properties=""Text;HDR=YES;FMT=Delimited"""
                        Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand($"SELECT * FROM {IO.Path.GetFileName(path)}", con)
                            Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                                con.Open()
                                da.Fill(dt)
                                con.Close()
                            End Using
                        End Using
                    Catch ex As Exception
                        Console.WriteLine(ex.ToString())
                    Finally
                        If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                            con.Close()
                        End If
                    End Try
                End Using
    
                Return dt
            End Function
    
            Public Function ToDataTable(ByVal path As String, ByVal delimiter As Char, ByVal headers As Boolean) As DataTable
                Dim dt As DataTable = New DataTable()
                Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
                    Try
                        con.ConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={IO.Path.GetDirectoryName(path)};Extended Properties=""Text;HDR={If(headers, "YES", "NO")};FMT=Delimited)"""
                        Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand($"SELECT * FROM {IO.Path.GetFileName(path)}", con)
                            Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                                con.Open()
                                da.Fill(dt)
                                con.Close()
                            End Using
                        End Using
                    Catch ex As Exception
                        Console.WriteLine(ex.ToString())
                    Finally
                        If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                            con.Close()
                        End If
                    End Try
                End Using
    
                Return dt
            End Function
    
            Public Sub ToTextFile(ByVal dt As DataTable, ByVal path As String, ByVal delimiter As Char)
                Dim builder As System.Text.StringBuilder = New System.Text.StringBuilder
    
                'Append the column names
                builder.AppendLine(String.Join(delimiter.ToString(), dt.Columns.Cast(Of DataColumn).Select(Function(c) c.Caption.Replace(delimiter.ToString, "\" & delimiter))))
    
                'Append each row/cell
                For Each row As DataRow In dt.Rows
                    builder.AppendLine(String.Join(delimiter.ToString, row.ItemArray.Select(Function(cell) cell.ToString.Replace(delimiter.ToString, "\" & delimiter))))
                Next
    
                'Create the file
                IO.File.WriteAllText(path, builder.ToString)
    
                'Create the ini file if it isn't a comma delimitted file too
                If delimiter <> ","c Then
                    IO.File.WriteAllLines(IO.Path.Combine(IO.Path.GetDirectoryName(path), "schema.ini"), {$"[{IO.Path.GetFileName(path)}]", $"Format = Format=Delimited({delimiter.ToString})"})
                End If
            End Sub
    
            Public Sub ToTextFile(ByVal dt As DataTable, ByVal path As String, ByVal delimiter As Char, ByVal headers As Boolean)
                Dim builder As System.Text.StringBuilder = New System.Text.StringBuilder
    
                If headers Then
                    'Append the column names
                    builder.AppendLine(String.Join(delimiter.ToString(), dt.Columns.Cast(Of DataColumn).Select(Function(c) c.Caption.Replace(delimiter.ToString, "\" & delimiter))))
                End If
    
                'Append each row/cell
                For Each row As DataRow In dt.Rows
                    builder.AppendLine(String.Join(delimiter.ToString, row.ItemArray.Select(Function(cell) cell.ToString.Replace(delimiter.ToString, "\" & delimiter))))
                Next
    
                'Create the file
                IO.File.WriteAllText(path, builder.ToString)
    
                If delimiter <> ","c Then
                    IO.File.WriteAllLines(IO.Path.Combine(IO.Path.GetDirectoryName(path), "schema.ini"), {$"[{IO.Path.GetFileName(path)}]", $"Format = Format=Delimited({delimiter.ToString})"})
                End If
            End Sub
    
        End Module
    
    End Namespace
    Here is how it could be used in a Windows Form Application with two Buttons and a DataGridView:
    Code:
    Public Class Form1
    
        Private dt As DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using ofd As OpenFileDialog = New OpenFileDialog
                With ofd
                    .Filter = "Text Files|*.txt"
                    .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
                    .Multiselect = False
                    .RestoreDirectory = True
    
                    If ofd.ShowDialog = DialogResult.OK Then
                        dt = DelimitedData.Convert.ToDataTable(ofd.FileName, "|"c, True)
                        Dim bs As New BindingSource With {.DataSource = dt}
                        DataGridView1.DataSource = bs
                    End If
                End With
            End Using
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using sfd As SaveFileDialog = New SaveFileDialog
                With sfd
                    .Filter = "Text Files|*.txt"
                    .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
                    .RestoreDirectory = True
    
                    If sfd.ShowDialog = DialogResult.OK Then
                        DelimitedData.Convert.ToTextFile(dt, sfd.FileName, "|"c, True)
                    End If
                End With
            End Using
        End Sub
    End Class
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,990

    Re: How can i import text from .txt into datagridview

    I'd say that the question comes down to whether you want to be able to go into the text file and look at the data that way, or even edit the data that way. If you really don't care about seeing the data in the text file, and how the text file looks, there is a much easier way than even the ADO.NET approach. If the DGV was bound to a datatable such that the DGV was really just displaying the contents of the table, then you could read/write the datatable to an XML file with ReadXML/WriteXML. That's much easier, but it results in an XML file. XML files are just plain text, so you can see them with any text editor like Word, NotePad, or whatever you wanted to use. However, the file would be XML, which would mean that there's be a lot of 'extra' stuff in the file as to the rows, columns, and fields. You could still edit it, just not as easily, cause you wouldn't want to change any of the XML tags. It would make the reading and writing SUPER easy, though.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width