-
Dec 21st, 2017, 12:39 PM
#1
Thread Starter
New Member
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!
-
Dec 21st, 2017, 12:58 PM
#2
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
-
Dec 21st, 2017, 01:08 PM
#3
Thread Starter
New Member
Re: How can i import text from .txt into datagridview
Last edited by babypandah; Dec 21st, 2017 at 01:13 PM.
-
Dec 21st, 2017, 01:12 PM
#4
Thread Starter
New Member
Re: How can i import text from .txt into datagridview
Originally Posted by Shaggy Hiker
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
-
Dec 21st, 2017, 02:28 PM
#5
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
-
Dec 21st, 2017, 06:48 PM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|