4everlearning 4everlearning - 6 months ago 79
Vb.net Question

Set column datatype automatically

I'm using a csv file as the datasource to a datagridview. It works fine, the data is displayed, but when i try to sort numeric data it sorts as string ("9">"10").

When I'm adding the rows of data to the datagridview i do this:

Dim data() As String = _textStreamReader.ReadLine.Split(vbTab)
dt.Rows.Add(data.ToArray)


I don't want to explicitly add the columns one by one, setting its datatype programatically, thus losing the flexibility of adding new columns to the source data and have it loaded just fine in the datagridview (with the new columns and all).

Is there any way to test if the content of the cells in a column is numeric and set the datatype for the column accordingly? Every column has either all text or all floats.

Answer

If you want to stick with reading a CSV and parsing it into a DataTable, here is a hackish way to do it. There are more elegant ways, but you'd need to change your design.

My form just has a DataGridView named dgv. This is the contents of the code-behind. The List(Of String()) just represents an in-memory CSV file. In your case, you'd pass your first ReadLine result to AddDataColumns, and then add it with dt.LoadDataRow followed by the subsequent lines.

Public Class Form1

   Dim dt As DataTable = New DataTable()

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      Dim data As List(Of String()) = New List(Of String())
      data.Add(New String() {"test", "123", "12.3", "1/1/2000", "false"})
      data.Add(New String() {"test2", "456", "45.6", "2/2/2002", "true"})
      data.Add(New String() {"test3", "789", "78.9", "3/3/2003", "false"})
      data.Add(New String() {"test4", "012", "1.2", "4/4/2004", "true"})

      AddDataColumns(data(0))

      For Each line() As String In data
         dt.LoadDataRow(line, True)
      Next

      dgv.DataSource = dt

   End Sub

   Private Sub AddDataColumns(ByRef values() As String)

      Dim dc As DataColumn

      For Each value As String In values

         dc = New DataColumn()

         If Boolean.TryParse(value, Nothing) Then
            dc.DataType = GetType(Boolean)
            dc.Caption = "Boolean"

         ElseIf Integer.TryParse(value, Nothing) Then
            dc.DataType = GetType(Integer)
            dc.Caption = "Integer"

         ElseIf Long.TryParse(value, Nothing) Then
            dc.DataType = GetType(Long)
            dc.Caption = "Long"

         ElseIf Single.TryParse(value, Nothing) Then
            dc.DataType = GetType(Single)
            dc.Caption = "Single"

         ElseIf Double.TryParse(value, Nothing) Then
            dc.DataType = GetType(Double)
            dc.Caption = "Double"

         ElseIf Decimal.TryParse(value, Nothing) Then
            dc.DataType = GetType(Decimal)
            dc.Caption = "Decimal"

         ElseIf DateTime.TryParse(value, Nothing) Then
            dc.DataType = GetType(DateTime)
            dc.Caption = "DateTime"

         Else
            dc.DataType = GetType(String)
            dc.Caption = "String"

         End If

         dt.Columns.Add(dc)

      Next

   End Sub

End Class

The result:

CSV to DataGrid example

Comments