[RESOLVED] DGV Text File

Hi

I have my datagridview being loaded from a Text File and then being saved to a text file as a comma separated file - the commas obviously separate the columns.

However in some of the cells prior to saving, I have data that i have inputted over two lines by pressing shift + return.. eg

"SOLD
10/05/2012"

This saves fine, but obviously shows up in the text file as a line break, which then causes some issues when i choose to re open the info back into DGV.

I'm wondering if anyone can help as to how i can code so that it saves as a line break within a cell, rather than a completely new line.

Thanks

You could replace all the linebreaks with commas. Could you post a sample of your text file with these linebreaks ?

Hi Niya

Here's what one of the lines looks like...

Quote:

---

,VOLVO,,,,,,SOLD
10 05 2012,2:48,True,False,True,False,False,False,True

---

So at the moment, the info is entered into the dgv by the user, they hit save, and this is how it saves. The cell with the line break is "SOLD 10 05 2012". When the text file is loaded back into the DGV it should appear in one cell, with a line break, however at the moment it throws an exception etc etc etc

Ok, does your text file have multiple rows ? If so, how do you separate them. My reason for asking is related to my earlier suggestion. If you replace all the linebreaks with commas you may end up combining several rows into a single row which would royally screw up the whole thing. We may need a way to distinguish different rows.

A better solution would be to remove line breaks before saving to the text file. Perhaps you could replace them with some special character and when you load them back into the DataGridView, you convert them back into line breaks for display purposes.

Apologies Niya! That makes sense.

So in the text file, new rows in the DGV are separated by a line break.

Your solution to remove line breaks, replacing with a character and reading the character for display sounds great...can you advise on how to do that?

Here is an example:-

vbnet Code:

'
        Dim st As String = "Hello" + Environment.NewLine + "World"
 
        MsgBox(st)
 
        'Replace line break with <br>
        st = Replace(st, Environment.NewLine, "<br>")
 
        MsgBox(st)
 
 
        'Replace <br> with real line break
        st = Replace(st, "<br>", Environment.NewLine)
 
        MsgBox(st)

I suggested using a special character but you could use anything you want. In the above example, I used a certain character sequence. IE <br>

When I was in a situation similar to you, I had written this function for myself.
Hope this would help you.

vb.net Code:

Function StringToCsvFormat(ParamArray fields() As String) As String
    Dim output As String = ""
    For Each field In fields
        If String.IsNullOrEmpty(field) Then
            field = ""
        ElseIf field.Contains("""") Then
            field = """" & field.Replace("""", """""") & """"
        ElseIf field.Contains(",") Then
            field = """" & field & """"
        End If
        field = field.Replace(vbCrLf, "; ").Replace(vbCr, "; ").Replace(vbLf, "; ")
        output &= field.Trim & ","
    Next
    output &= vbCrLf
    Return output
End Function

This writes one complete row at a time with a line break at the end.
If you will be writing in parts and would like to put the line breaks yourself, then simply comment out the following line:
output &= vbCrLf


Sample Usage:

Code:

---

MyWriter.Write(StringToCsvFormat("value1", "val,ue2", "val""ue3", "val" & vbCr & "ue4"))

---

This will write the values 1 to 4 preserving its commas etc. and will be correctly reloaded when read from any CSV complaint reader (or code) like Excel etc.

The output will be:

Code:

---

value1    val,ue2    val"ue3    val; ue4

---

Hi! Thanks for the replies and sorry for my delay.

Just wondering where I should put either of the codes provided in my "save DGV" code so that line breaks in individual cells are identified, replaced and saved,

and where I should put the code in my "Load" code so that the replaced cell line breaks are identified and swapped for ACTUAL line breaks in the cells?

I've attached an example cell.

And here are the relevant codes:

SAVE CODE:

Code:

---

Dim Rows = (From row In DataGridView1.Rows _
                                Where Not DirectCast(row, DataGridViewRow).IsNewRow _
                                Let RowItem = String.Join(",", Array _
                                  .ConvertAll( _
                                  DirectCast(row, DataGridViewRow).Cells.Cast(Of DataGridViewCell).ToArray, _
                                  Function(c As DataGridViewCell) _
                                    If(c.Value Is Nothing, "", c.Value.ToString))) _
                                Select RowItem).ToArray

        IO.File.WriteAllLines(tbDGVName.Text, Rows)

        If Dir(tbDGVName.Text) <> "" Then
            MsgBox("Saved")
        Else
            MsgBox("Grid not created")
        End If

---

LOAD CODE:

Code:

---

Imports Microsoft.VisualBasic.FileIO
Module Module1
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Function LoadTextFile( _
      ByVal sender As DataGridView, _
      ByVal FileName As String, _
      ByRef Errors As List(Of String)) As Boolean

        If Not IO.File.Exists(FileName) Then
            Errors.Add("Failed to locate '" & FileName & "'")
            Return False
        End If

        If sender.ColumnCount <> 16 Then

            Errors.Add("Must have three columns to load your text file., there are " & _
                      sender.ColumnCount.ToString & " columns presently.")

            Return False

        End If

        Using MyReader As New TextFieldParser(FileName)
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.Delimiters = New String() {","}
            Dim Line As String()

            sender.SuspendLayout()

            Try
                While Not MyReader.EndOfData
                    Try
                        Line = MyReader.ReadFields()
                        '
                        ' For every column per line
                        ' there must be a column in the DataGridView
                        '
                        sender.Rows.Add(New Object() {Line(0), Line(1), Line(2), Line(3), Line(4), Line(5), Line(6), Line(7), Line(8), Line(9), Line(10), CBool(Line(11)), Line(12), Line(13), Line(14), Line(15)})
                        sender.EndEdit()
                        ' Line(4), Line(5), Line(6), Line(7), Line(8), Line(9), Line(10), Line(11), Line(12), Line(13), Line(14), Line(15), Line(16)})
                    Catch ex As FileIO.MalformedLineException
                        '
                        ' Report problem
                        '
                        Errors.Add(ex.Message)
                    Catch ex As Exception
                        Errors.Add(ex.Message)
                    End Try
                End While
            Finally
                sender.ResumeLayout()
            End Try
        End Using

        Return Errors.Count = 0

    End Function

End Module

---

Ok Let's try it:

1. In my case I didnt need any special handling of line breaks. So I simply replaced them with "; ". Here we need, so we will modify our procedure. We also don't need the extra line break at the end of line, since we will be using IO.File.WriteAllLines which puts that line break automatically.
So we modify our procedure as follows:

vb.net Code:

'' We use some sequence of non-printing characters to replace the line breaks. 
'' You can change these if any of these characters can be part of your regular text.
Const MyCrlfMark As String = ChrW(164) & ChrW(165) & ChrW(166)
 
'' This function converts our text to CSV compliant text
Function StringToCsvFormat(ByVal ParamArray fields() As String) As String
    Dim output As String = ""
    For Each field In fields
        If String.IsNullOrEmpty(field) Then
            field = ""
        ElseIf field.Contains("""") Then
            field = """" & field.Replace("""", """""") & """"
        ElseIf field.Contains(",") Then
            field = """" & field & """"
        End If
        field = field.Replace(vbCrLf, MyCrlfMark).Replace(vbCr, MyCrlfMark).Replace(vbLf, MyCrlfMark)
        output &= field.Trim & ","
    Next
    ' -- output &= vbCrLf
    Return output
End Function
 
'' This function puts back the line breaks after we get our saved data back from disk.
Function CsvToStringFormat(ByVal field As String) As String
    Return field.Replace(MyCrlfMark, vbCrLf)
End Function

2. Next we use our StringToCsvFormat to when saving file.

vb.net Code:

Dim Rows = (From row In DataGridView1.Rows _
            Where Not DirectCast(row, DataGridViewRow).IsNewRow _
            Let RowItem = StringToCsvFormat(Array _
              .ConvertAll( _
              DirectCast(row, DataGridViewRow).Cells.Cast(Of DataGridViewCell).ToArray, _
              Function(c As DataGridViewCell) _
                If(c.Value Is Nothing, "", c.Value.ToString))) _
            Select RowItem).ToArray
 
IO.File.WriteAllLines(tbDGVName.Text, Rows)

3. When loading the data, we restore our line breaks:

vb.net Code:

While Not MyReader.EndOfData
    Try
        Line = MyReader.ReadFields()
 
        '' restore the line breaks
        For i = 0 To line.Length - 1
            Line(i) = CsvToStringFormat(Line(i))
        Next
 
        ' For every column per line
        ' there must be a column in the DataGridView
        sender.Rows.Add(New Object() {Line(0), Line(1), Line(2), Line(3), Line(4), Line(5), Line(6), Line(7), Line(8), Line(9), Line(10), CBool(Line(11)), Line(12), Line(13), Line(14), Line(15)})
        sender.EndEdit()
        ' Line(4), Line(5), Line(6), Line(7), Line(8), Line(9), Line(10), Line(11), Line(12), Line(13), Line(14), Line(15), Line(16)})
    Catch ex As FileIO.MalformedLineException
        Errors.Add(ex.Message)
    Catch ex As Exception
        Errors.Add(ex.Message)
    End Try
End While

(NB: None of the above code is tested.)

Fantastic! Thank you ever so much! That works absolutely perfectly - not an error anywhere. Superb! :D