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?

As per below, if strCSVFilename has a path in it, for whatever reason, it treats the first line as a header line. If it ONLY has the filename (and the Connection string will tell ADO what path to use), the first line is imported into the data table. — David, Jan 05 '11 at 12:09
Did not work for me. Data Source has the path, SELECT statement has ONLY filename. HDR=NO but Fields of the 1st row are becoming column headers. — Mehdi Anis, Feb 09 '12 at 19:23

score 1 · Accepted Answer · answered Jan 04 '11 at 21:55

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.

Close but no cigar. It turns out the problem was in the filename I passed to the SELECT * FROM command. Originally, the path was in the Connect string as well as the SELECT command. When I took the path OUT of the SELECT command, for some reason, it worked. In other words "SELECT * FROM Sample.csv" gives me ALL the lines. "SELECT * FROM C:\Inetpub\wwwroot\Site\Upload\Sample.csv" results in the first line being ignored. I'll put the check mark on this answer since it got me started down the right path. — David, Jan 05 '11 at 12:07

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

1 Answers1

Linked