asp.net – 使用OLEDB读取CSV文件,即使连接字符串中的HDR =否,也会忽略第一行

前端之家收集整理的这篇文章主要介绍了asp.net – 使用OLEDB读取CSV文件,即使连接字符串中的HDR =否,也会忽略第一行前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我们正在将Classic ASP站点转换为ASP.NET站点.其中一项功能是以CSV格式上传“模板”数据,以便导入数据库.那里有几种不同的记录类型(第一个字段总是标识数据的类型).

任务是将CSV转换为DataTable,以便验证它(新项目是要有更好的验证规则)

代码看起来非常简单 – 淡化(取出评论,Try / Catch等),如下所示:

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

填充DataTable(dtData),但仅从CSV文件的第二行开始DESPITE“连接字符串”中存在“HDR = No”的事实.

我在这里想念的是什么?

解决方法

文件的开头是否有可能导致第一行被跳过的东西?也许是一个不可打印的角色? NPC可能来自未以预期编码保存的文件.当我创建CSV文件时,我收到了您期望的结果.这是我用来测试的代码
  1. Private Sub Test()
  2. Dim TempDir = My.Computer.FileSystem.SpecialDirectories.Temp
  3. Dim TempFile = "Test.csv"
  4.  
  5. '//Create our test file with a header row and three data rows
  6. Using FS As New System.IO.FileStream(System.IO.Path.Combine(TempDir,TempFile),IO.FileMode.Create,IO.FileAccess.Write,IO.FileShare.Read)
  7. Using SW As New System.IO.StreamWriter(FS,System.Text.Encoding.ASCII)
  8. SW.WriteLine("Col1,Col2")
  9. SW.WriteLine("R1","R1")
  10. SW.WriteLine("R2","R2")
  11. SW.WriteLine("R3","R3")
  12. End Using
  13. End Using
  14.  
  15. '//Read the data into a table specifying that the first row should be treated as a header
  16. Using dtData As New DataTable()
  17. Using da As New System.Data.OleDb.OleDbDataAdapter
  18. Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TempDir & ";" & "Extended Properties=""Text;HDR=Yes;FMT=Delimited;""")
  19. Using cd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & TempFile,cn)
  20. cn.Open()
  21. da.SelectCommand = cd
  22. da.Fill(dtData)
  23. Trace.WriteLine("With header,expected 3,found " & dtData.Rows.Count)
  24. End Using
  25. End Using
  26. End Using
  27. End Using
  28.  
  29. '//Read the data into a table again,this time specifying that the there isn't a header row
  30. Using dtData As New DataTable()
  31. Using da As New System.Data.OleDb.OleDbDataAdapter
  32. Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TempDir & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited;""")
  33. Using cd As New System.Data.OleDb.OleDbCommand("SELECT * FROM " & TempFile,cn)
  34. cn.Open()
  35. da.SelectCommand = cd
  36. da.Fill(dtData)
  37. Trace.WriteLine("Without header,expected 4,found " & dtData.Rows.Count)
  38. End Using
  39. End Using
  40. End Using
  41. End Using
  42.  
  43. '//Delete our temporary file
  44. System.IO.File.Delete(System.IO.Path.Combine(TempDir,TempFile))
  45. End Sub

如果您将初始编码更改为Unicode,您将在结果中获得8行和9行,这可能就是您所看到的.如果结果是编码问题,可以将CharacterSet = Unicode添加到扩展属性中.

猜你在找的asp.Net相关文章