代码之家 › 专栏 › 技术社区 › Talguy

以OLEDB连接方式打开CSV文件会将文件文本转换为双文本

vb.net-2010 csv vb.net

Talguy · 技术社区 · 7 年前

here . 我遇到的问题是,我用于导入数据的基本查询是将IP地址列转换为双精度。我想读入10.0.0.1,它显示为10.001。如何将此列作为字符串读入?如果可以的话,我不想重复处理文件。

我使用的查询是基本的,如下所示:

SELECT * FROM [ComputerList.csv]

下面是我打开CSV文件并将其读入数据表的函数

Public Function OpenFile(ByVal strFolderPath as String, ByVal strQuery as String) as DataTable
   Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFolderPath & ";Extended Propteries=""text; HDR=Yes;FMT=Delimited"""
   Dim conn as OleDb.OleDbConnection = New OleDb.OleDbConnection(strConn)

   Try
      conn.Open()
      Dim cmd as OleDb.OleDbCommand = New OleDb.OleDbCommand(strQuery, conn)
      Dim da as OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()

      da.SelectCommand = cmd
      Dim ds as DataSet = New DataSet()
      da.Fill(ds)
      da.Dispose()

      return ds.Tables(0)
   Catch
      return Nothing
   Finally
      conn.Close()
   End Try

End Function

2 回复 | 直到 7 年前

Danny James 7 年前

我用它来读取csv并强制所有字符串。

Public Function convert_csv_to_data_table(ByVal File As String, ByVal separator As String) As DataTable
    Dim dt As New DataTable
    Dim firstLine As Boolean = True
    If IO.File.Exists(File) Then
        Using sr As New StreamReader(File)
            While Not sr.EndOfStream
                If firstLine Then
                    firstLine = False
                    Dim cols = sr.ReadLine.Split(separator)
                    For Each col In cols
                        dt.Columns.Add(New DataColumn(col, GetType(String)))
                    Next
                Else
                    Dim data() As String = sr.ReadLine.Split(separator)
                    dt.Rows.Add(data.ToArray)
                End If
            End While
        End Using
    End If
    Return dt
End Function

Talguy 7 年前

好的,我尝试了每个人建议的变体,并决定在它们之间进行混合。我的目标是读取CSV文件,以数据表的形式对其进行操作,然后将其写回。我的一些CSV文件在一个单元格中有多行,一些在一个单元格中有删除器。下面是我的混合解决方案,它利用TextFieldParser读取文件并将其分解。

Public Function OpenFile(ByVal File as String, NyVal delim as String) as DataTable
   Dim dt as New DataTable()
   Dim firstline as Boolean = True
   Using MyReader as New Microsoft.VisualBasic.FileIO.TextFieldParser(File)
      MyReader.TextFieldType = FileIO.FieldType.Delimited
      MyReader.SetDelimiters(delim)
      Dim currentRow as String()

      While Not MyReader.EndOfData
         Try
            currentRow = MyReader.ReadFields()

            If firstline
               firstline = false
               For Each col in currentRow
                  dt.Columns.Add(New DataColumn(col.ToString(), System.Type.GetType("System.String")))
               Next
            Else
               dt.Rows.Add(currentRow.ToArray())
            End If
         Catch ex as Microsoft.VisualBasic.FileIO.MalformedLineException
            Console.WriteLIne("Line " + ex.Message + " is not valid and will be skipped")
         End Try
      End While
   End Using

   return dt
End Function