Gavin142 Gavin142 -3 years ago 189
Vb.net Question

convert csv data to DataTable in VB.net, capturing column names from row 0

I've adapted the code from the @tim-schmelter answer to question convert csv data to DataTable in VB.net (see below)

I would like to parse in the column titles from row 0 of the csv file

DT|Meter Number|Customer Account Number|Serial Number|Port...


but I'm not having any luck trying to figure out how to do this. any suggestions would be very appreciated.

Public Function csvToDatatable_2(ByVal filename As String, ByVal separator As String)
'////////////////////////////////////////
'Reads a selected txt or csv file into a datatable
'based on code from http://stackoverflow.com/questions/11118678/convert-csv-data-to-datatable-in-vb-net
'////////////////////////////////////////
Dim dt As System.Data.DataTable

Try
dt = New System.Data.DataTable
Dim lines = IO.File.ReadAllLines(filename)
Dim colCount = lines.First.Split(separator).Length

For i As Int32 = 1 To colCount
dt.Columns.Add(New DataColumn("Column_" & i, GetType(String)))
Next

For Each line In lines
Dim objFields = From field In line.Split(separator)
Dim newRow = dt.Rows.Add()
newRow.ItemArray = objFields.ToArray()
Next

Catch ex As Exception
Main.Msg2User(ex.Message.ToString)
Return Nothing

End Try

Return dt

End Function

Answer Source

Just loop thru all the line of the file. Use a boolean to check for the first row.

Public Function csvToDatatable_2(ByVal filename As String, ByVal separator As String)
 Dim dt As New System.Data.DataTable
 Dim firstLine As Boolean = True
 If IO.File.Exists(filename) Then
   Using sr As New StreamReader(filename) 
     While Not sr.EndOfStream
       If firstLine Then
         firstLine = False
         Dim cols = sr.ReadLine.Split(separator)
         For Each col In cols 
           dt.Columns.Add(New DataColumn(col, GetType(String)))
         Next
       Else
         Dim data() As String = sr.Readline.Split(separator)
         dt.Rows.Add(data.ToArray)
       End If
      End While
   End Using
 End If
 Return dt
End Function
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download