Reading CSV file with OLEDB ignores first line even with HDR=No in Connection String

Question

We're converting a Classic ASP site to an ASP.NET site. One function was to upload a 'template' of data in CSV format for importing into the database. There were several different record types in there (the first field always indentifies the type of data).

The task was to get the CSV into a DataTable so it could be validated (new project is to have MUCH better validation rules)

The code seemed pretty straightforward - watered down (taking out comments, Try/Catch, etc) it is as follows:

    Dim da As New System.Data.OleDb.OleDbDataAdapter
    Dim cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDirectory & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited;""")
    Dim cd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & strCSVFilename, cn)
    cn.Open()
    da.SelectCommand = cd
    da.Fill(dtData)

The DataTable (dtData) is populated, but only starting with the second line of the CSV file DESPITE the fact that "HDR=No" is in the connection string.

What am I missing here?

Answer 1

Is there maybe something at the begining of the file that's causing the first row to be skipped? Maybe a non-printable character? The NPC could come from the file not being saved in an expected encoding. When I created a CSV file I received the results that you expected. Here's the code that I used to test:

    Private Sub Test()
    Dim TempDir = My.Computer.FileSystem.SpecialDirectories.Temp
    Dim TempFile = "Test.csv"

    '//Create our test file with a header row and three data rows
    Using FS As New System.IO.FileStream(System.IO.Path.Combine(TempDir, TempFile), IO.FileMode.Create, IO.FileAccess.Write, IO.FileShare.Read)
        Using SW As New System.IO.StreamWriter(FS, System.Text.Encoding.ASCII)
            SW.WriteLine("Col1,Col2")
            SW.WriteLine("R1", "R1")
            SW.WriteLine("R2", "R2")
            SW.WriteLine("R3", "R3")
        End Using
    End Using

    '//Read the data into a table specifying that the first row should be treated as a header
    Using dtData As New DataTable()
        Using da As New System.Data.OleDb.OleDbDataAdapter
            Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TempDir & ";" & "Extended Properties=""Text;HDR=Yes;FMT=Delimited;""")
                Using cd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & TempFile, cn)
                    cn.Open()
                    da.SelectCommand = cd
                    da.Fill(dtData)
                    Trace.WriteLine("With header,    expected 3, found " & dtData.Rows.Count)
                End Using
            End Using
        End Using
    End Using

    '//Read the data into a table again, this time specifying that the there isn't a header row
    Using dtData As New DataTable()
        Using da As New System.Data.OleDb.OleDbDataAdapter
            Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TempDir & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited;""")
                Using cd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & TempFile, cn)
                    cn.Open()
                    da.SelectCommand = cd
                    da.Fill(dtData)
                    Trace.WriteLine("Without header, expected 4, found " & dtData.Rows.Count)
                End Using
            End Using
        End Using
    End Using

    '//Delete our temporary file
    System.IO.File.Delete(System.IO.Path.Combine(TempDir, TempFile))
End Sub

If you change the initial encoding to Unicode you'll get 8 and 9 rows in the results instead which is maybe what you're seeing. If it turns out to be an encoding problem you can add CharacterSet=Unicode to your extended properties.