Set column datatype automatically

Question

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.

If you don't want to add columns and want to stick with `ToArray`, then no, I don't know of a way to test. If you want to do a pre-initialization, you can always do various data conversion tests to determine what each position holds, create your columns, and then add the Rows. If you go the pre-init testing, it will work for new columns. Just make sure you test in your particular order of precedence. — Chris Fannin, May 10 '16 at 21:01
At some point someone or something has to specify the datatype. Either the compiler uses the lowest common denominator (string) or you add typed columns, or define a matching class and parse the data into that and use a List instead of a datatable. BTW, `data` is already an array. — Ňɏssa Pøngjǣrdenlarp, May 10 '16 at 21:02
@ChrisFannin That sounds doable. Do you have any code examples of that I can refer to? — 4everlearning, May 10 '16 at 21:03
Yes, but what difference does it make? You either have code to add some typed columns or you have code to conditionally throw away some string columns (based on order) and replace with a hardcoded different column type. If it is not destined for a DB, use a List(of T); if it is going to a db, get the dt column defs from the db table. — Ňɏssa Pøngjǣrdenlarp, May 10 '16 at 21:21
@Plutonix It is not for a DB. I don't understand what you mean by List. Can you elaborate on that? — 4everlearning, May 10 '16 at 21:27
[Five Minute Beginners Guide to Classes and Lists](http://stackoverflow.com/a/34164458/1070452) — Ňɏssa Pøngjǣrdenlarp, May 10 '16 at 21:29
@Plutonix I'm officialy lost. Why do i need a List object? Still, i guess i will have t o figure this out myself... Thank you. P.S. Can you write your comment as an answer so i can accept it as the solution? — 4everlearning, May 10 '16 at 21:42
You may want a list instead of the datatable because it is a bit easier to define the types via the underlying class. There is no magic spell: you either have to add typed columns to the DT or write a small simple class which defines the types. — Ňɏssa Pøngjǣrdenlarp, May 10 '16 at 21:45
@Plutonix I get what you're saying. I just don't get why and how to use Lists! I don't know the number of columns and their datatype a priori, how can i create a class when i don't know its members? — 4everlearning, May 10 '16 at 21:50
If the number and type of columns is a total unknown, you could use OleDB to import it and let it guess at the columns by testing them. It will recognize dates and numerics fairly well. — Ňɏssa Pøngjǣrdenlarp, May 10 '16 at 21:54
@4everlearning I posted an answer that shows one way to load the `DataTable`. Obviously it will require more work, including adding unique column names and such, if you want to actually query it or anything. You may also need to adjust the various data types depending on your needs. — Chris Fannin, May 10 '16 at 22:04

Chris Fannin · Accepted Answer · 2016-05-10T22:18:46.733

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:

If you add a `Debug.Print` in each of the data type tests below the `dc.Caption` lines, you can see what it detects each value as. Example: `Debug.Print("Value: " & value & " = " & dc.Caption)` — Chris Fannin, May 10 '16 at 22:17

Set column datatype automatically

1 Answers1