I found how to do this in several languages but not in .net (specifically vb.net). I am using OLeDbCommand to read both CSV and Excel files. In case of Excel I can skip first row and select second row onwards by specifying a range of cells. But in case of CSV, I am not sure how to do it. Current code looks like:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [" + Path.GetFileName(FileName) + "]", cn)
Here we give the file, not the sheet. So I am bit stuck.
From my experience reading a text file like this is very restrictive. It only allows you to read the whole file, because you can't specify a table name. You might be better of reading each line and making table rows and adding them to a table. If the first row is headers you can use that to make the columns, otherwise hard code the columns.
Here's a simple little method that fills a datatable with the data from a .csv file, that you should be able to use:
Private Sub GetData(ByRef dt As DataTable, FilePath As String, Optional ByVal Header As Boolean = True)
Dim Fields() As String
Dim Start As Integer = CInt(Header) * -1
If Not File.Exists(FilePath) Then
Return
End If
dt.Clear()
Dim Lines() As String = File.ReadAllLines(FilePath)
If CBool(Start) AndAlso dt.Columns.Count = 0 Then
Lines(0) = Lines(0).Replace(Chr(34), "")
For Each h As String In Lines(0).Split(",")
dt.Columns.Add(h)
Next
End If
For I = Start To Lines.Count - 1
Fields = Lines(I).Split(",")
dt.Rows.Add(Fields)
Next
End Sub
