2

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.

voddy
  • 950
  • 1
  • 11
  • 21
  • 1
    See http://stackoverflow.com/questions/4597639/reading-csv-file-with-oledb-ignores-first-line-even-with-hdr-no-in-connection-st for some discussion of this behavior. – Tim Williams Apr 09 '14 at 00:17

1 Answers1

1

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
tinstaafl
  • 6,908
  • 2
  • 15
  • 22
  • I agree. But my file is a "Microsoft Excel Comma Separated Values File" which opens with Excel and has sheets in it with all other excel functionality. But I cant read a sheet given a sheet name, so I have to read the whole file. Is there a way to convert CSV to Excel? – voddy Apr 09 '14 at 00:08
  • 1
    "opens with Excel and has sheets in it" - a CSV file has no sheets, since it's just a plain text file. Excel will open it and create an excel file from it (ie. it appears in Excel as a single worksheet) but the CSV file has content only. – Tim Williams Apr 09 '14 at 00:18