阅读与CSV文件OLEDB忽略甚至与HDR第一线=没有在连接字符串 [英] Reading CSV file with OLEDB ignores first line even with HDR=No in Connection String

问题描述

我们正在转变传统的ASP网站到ASP.NET网站。一个功能是上传CSV格式的数据的模板导入到数据库中。有在有几种不同的记录类型（第一场总是indentifies数据的类型）。

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).

现在的任务是让CSV进入一个DataTable，因此它可能被验证（新项目是为了有更好的验证规则）

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

在code显得pretty简单 - 淡化（取出意见，try / catch语句等），其计算方法如下：

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)

在数据表（dtData）填充，但只是开始CSV文件的第二行尽管HDR =无是在连接字符串中

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.

我是什么在这里失踪？

推荐答案

是否有可能在这是造成第一行的文件的开头的东西被忽略？也许非打印字符？全国人大可能来自没有被保存在一个预期的编码文件。当我创建了一个CSV文件，我收到了所期望的结果。这里的code，我用测试：

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

如果您更改初始编码统一code，你会在结果得到8和第9行，而不是这是你看到的也许什么。如果事实证明是一个编码的问题，您可以添加 CHARACTERSET =统一code 你的扩展属性。

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.

这篇关于阅读与CSV文件OLEDB忽略甚至与HDR第一线=没有在连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！