Convert type of a columns in datatable

Question

I import a csv file into a datatable which, unfortunately, put my datas into string columns even those with figures.

So I have to convert the format of some columns (unless there is an other way) into datetime, integer or double why I wrote the following code :

Public Sub ChangeFieldType(ByRef dataTable As DataTable, ByVal fieldIndex As Integer, ByVal newType As Type)
    Dim newDataTable As DataTable = dataTable.Clone

    newDataTable.Columns(fieldIndex).DataType = newType
    For Each row As DataRow In dataTable.Rows
        newDataTable.ImportRow(row)
    Next
    dataTable = newDataTable
End Sub

But there are some empty cells which in string format is vbnullstring. My question is is there an easier way then my code and if not is there an faster way than converting the empty cells then that way :

Public Sub ChangeFieldType(ByRef dataTable As DataTable, ByVal fieldIndex As Integer, ByVal newType As Type)
    Dim newDataTable As DataTable = dataTable.Clone

    newDataTable.Columns(fieldIndex).DataType = newType
    For Each row As DataRow In dataTable.Rows
        If row(fieldIndex) = vbNullString Then
            row(fieldIndex) = Nothing
        End If
        newDataTable.ImportRow(row)
    Next
    dataTable = newDataTable
End Sub

Because this is very very slow.

Thanks

Answer 1

When importing the csv file if you already know the column types beforehand then you should make a table with those columns and then fill the data. Cloning a table and then filling the data again is very slow process especially if data is large

You can refer to the following http://social.msdn.microsoft.com/Forums/windows/en-US/34b6a1e8-5103-42a3-aa45-cdc0cea461f2/importing-csv-file-to-datatable-problem-with-converting-data-type