[RESOLVED] csv to datatable, vb.net 3.5

[happyme]

I want to load a csv file (A comma delimited text file) into a datatable. I found the OdbcDataAdapter Class which looks perfect, but it says it is supported only in version 1.1 of the .NET Framework.

Is there a better way to import a csv file into a datatable? the first line has the column headers.

I am using Visual Studio 2008, using version 3.5 of the .NET framework

Thanks your your time

Hx

[dglienna]

Try the SQL version

[happyme]

solved, using streamreader and Split:


Public Function Read_CSV(ByVal FileName As String, ByVal FilePath As String) As DataTable
'----------------------------------------------------
'Reads a csv file into a datatable, with the first row as column headers

Dim myFile As String = FilePath & "\" & FileName
Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim myColumn As DataColumn
Dim MyType As String
Dim fieldValues As String()

Dim ColumnNames As String()
Dim ColumnTypes As String()
Dim myReader As New StreamReader(myFile)
Try

'Open file and read first two lines
ColumnNames = myReader.ReadLine().Split(",")
ColumnTypes = myReader.ReadLine().Split(",")

'Create data columns named according to first line of data, with type according to second line
For i = 0 To ColumnNames.Length() - 1
myColumn = New DataColumn()
MyType = "System." & ColumnTypes(i)
myColumn.DataType = System.Type.GetType(MyType)

myColumn.ColumnName = ColumnNames(i)
myTable.Columns.Add(myColumn)
Next

'Read the body of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(",")
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empty")
Finally
myReader.Close()
End Try

Return myTable
End Function