CODEWITHSUNDEEP
vb.netwinformscsvdatagridview
Mark
Mark

Reputation: 57

Import CSV file with comma and multiline text in quotes in a DataGridView

I'm trying to import a CSV file into a DataGridView but I'm running in some issues when I try to import multiline text.

What I'm trying to import is this:

ID;RW;Name;Description;Def;Unit;Min;Max
0;R;REG_INFO;"state of the
machine";0;ms;0;0xFFFF
1;R/W;REG_NUMBER;current number;0;days;0;65,535

This is what it should like when imported:

enter image description here

What I've implemented till now:

Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
    Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Text file|*.csv"}
        If ofd.ShowDialog() = DialogResult.OK Then
            Dim lines As List(Of String) = File.ReadAllLines(ofd.FileName).ToList()
            Dim list As List(Of Register) = New List(Of Register)

            For i As Integer = 1 To lines.Count - 1
                Dim data As String() = lines(i).Split(";")
                list.Add(New Register() With {
                    .ID = data(0),
                    .RW = data(1),
                    .Name = data(2),
                    .Description = data(3),
                    .Def = data(4),
                    .Unit = data(5),
                    .Min = data(6),
                    .Max = data(7)
                         })
            Next
            DataGridView1.DataSource = list
        End If
    End Using
End Sub

But I run in some problems with multiline text when I try to load the CSV, as "state of the machine" in the example.

Upvotes: 1

Views: 1001

Answers (1)

Jimi
Jimi

Reputation: 32248

An example, using the TextFieldParser class.
(This class is available in .Net 5)

The TextFieldParser object provides methods and properties for parsing structured text files. Parsing a text file with the TextFieldParser is similar to iterating over a text file, while using the ReadFields method to extract fields of text is similar to splitting the strings

  • Your source of data is a delimited (not fixed-length) structure, the header/fields values are separated by a symbol, so you can specify TextFieldType = FieldType.Delimited
  • The delimiter is not a comma (the C in CSV), so you need to pass the delimiter symbol(s) to the SetDelimiters() method.
  • Call the ReadFields() to extract each line as an array of String, representing the Fields' values (=> here, no conversion is performed, all values are returned as strings. Make your own Type converter in case it's needed.)
Imports Microsoft.VisualBasic.FileIO

Public Class RegisterParser
    Private m_FilePath As String = String.Empty
    Private m_delimiters As String() = Nothing

    Public Sub New(sourceFile As String, delimiters As String())
        m_FilePath = sourceFile
        m_delimiters = delimiters
    End Sub

    Public Function ReadData() As List(Of Register)
        Dim result As New List(Of Register)

        Using tfp As New TextFieldParser(m_FilePath)
            tfp.TextFieldType = FieldType.Delimited
            tfp.SetDelimiters(m_delimiters)
            tfp.ReadFields()

            Try
                While Not tfp.EndOfData
                    result.Add(New Register(tfp.ReadFields()))
                End While
            Catch fnfEx As FileNotFoundException
                MessageBox.Show($"File not found: {fnfEx.Message}")
            Catch exIDX As IndexOutOfRangeException
                MessageBox.Show($"Invalid Data format: {exIDX.Message}")
            Catch exIO As MalformedLineException
                MessageBox.Show($"Invalid Data format at line {exIO.Message}")
            End Try
        End Using
        Return result
    End Function
End Class

Pass the path of the CSV file and the set of delimiters to use (here, just ;).
The ReadData() method returns a List(Of Register) objects, to assign to the DataGridView.DataSource.

DefaultCellStyle.WrapMode is set to True, so multiline text can actually wrap in the Cell (otherwise it would be clipped).
After that, call AutoResizeRows(), so the wrapped text can be seen.

Dim csvPath = [The CSV Path]
Dim csvParser = New RegisterParser(csvPath, {";"})

DataGridView1.DataSource = csvParser.ReadData()
DataGridView1.Columns("Description").DefaultCellStyle.WrapMode = DataGridViewTriState.True
DataGridView1.AutoResizeRows()

Register class:

Added a constructor that accepts an array of strings. You could change it to Object(), then add a converter to the class to parse and convert the values to another Type.

Public Class Register
    Public Sub New(ParamArray values As String())
        ID = values(0)
        RW = values(1)
        Name = values(2)
        Description = values(3)
        Def = values(4)
        Unit = values(5)
        Min = values(6)
        Max = values(7)
    End Sub
    Public Property ID As String
    Public Property RW As String
    Public Property Name As String
    Public Property Description As String
    Public Property Def As String
    Public Property Unit As String
    Public Property Min As String
    Public Property Max As String
End Class

Upvotes: 2

Related Questions