none
Recommended way to read CSV file in 64-bit environment? RRS feed

  • Question

  • I am trying to read a CSV file in a 64-bit environment. As I understand it, the Jet OLE DB and ODBC drivers for text are supported for 32-bit only. Is there a recommended way to read a CSV file with ADO.NET other than parsing the file yourself or using a third-party library?

    Thank you.

    Wednesday, February 2, 2011 8:01 PM

Answers

  • I would use the TextFieldParser Class. You can also use the ACE OLEDB Provider, for which there is both a 32 and 64-bit version.

        Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\Dalton.txt")
    
        TextFileReader.TextFieldType = FileIO.FieldType.Delimited
        TextFileReader.SetDelimiters(",")
    
        ' Declare variables for DataColumn and DataRow objects.
        Dim Column As DataColumn
        Dim Row As DataRow
        Dim UpperBound As Int32
        Dim ColumnCount As Int32
        Dim CurrentRow As String()
    
        While Not TextFileReader.EndOfData
          Try
            CurrentRow = TextFileReader.ReadFields()
            If Not CurrentRow Is Nothing Then
              'Check if DataTable has been created
              If TextFileTable Is Nothing Then
                TextFileTable = New DataTable("TextFileTable")
                'Get number of columns
                UpperBound = CurrentRow.GetUpperBound(0)
                'Create new DataTable
                For ColumnCount = 0 To UpperBound
                  Column = New DataColumn()
                  Column.DataType = System.Type.GetType("System.String")
                  Column.ColumnName = "Column" & ColumnCount
                  Column.Caption = "Column" & ColumnCount
                  Column.ReadOnly = False
                  Column.Unique = False
                  TextFileTable.Columns.Add(Column)
                Next
              End If
              Row = TextFileTable.NewRow
              For ColumnCount = 0 To UpperBound
                Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
              Next
              TextFileTable.Rows.Add(Row)
            End If
          Catch ex As _
          Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & _
            "is not valid and will be skipped.")
          End Try
          Console.WriteLine()
        End While
        TextFileReader.Dispose()
        frmMain.DataGrid1.DataSource = TextFileTable
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, February 3, 2011 1:49 PM

All replies

  • I would use the TextFieldParser Class. You can also use the ACE OLEDB Provider, for which there is both a 32 and 64-bit version.

        Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\Dalton.txt")
    
        TextFileReader.TextFieldType = FileIO.FieldType.Delimited
        TextFileReader.SetDelimiters(",")
    
        ' Declare variables for DataColumn and DataRow objects.
        Dim Column As DataColumn
        Dim Row As DataRow
        Dim UpperBound As Int32
        Dim ColumnCount As Int32
        Dim CurrentRow As String()
    
        While Not TextFileReader.EndOfData
          Try
            CurrentRow = TextFileReader.ReadFields()
            If Not CurrentRow Is Nothing Then
              'Check if DataTable has been created
              If TextFileTable Is Nothing Then
                TextFileTable = New DataTable("TextFileTable")
                'Get number of columns
                UpperBound = CurrentRow.GetUpperBound(0)
                'Create new DataTable
                For ColumnCount = 0 To UpperBound
                  Column = New DataColumn()
                  Column.DataType = System.Type.GetType("System.String")
                  Column.ColumnName = "Column" & ColumnCount
                  Column.Caption = "Column" & ColumnCount
                  Column.ReadOnly = False
                  Column.Unique = False
                  TextFileTable.Columns.Add(Column)
                Next
              End If
              Row = TextFileTable.NewRow
              For ColumnCount = 0 To UpperBound
                Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
              Next
              TextFileTable.Rows.Add(Row)
            End If
          Catch ex As _
          Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & _
            "is not valid and will be skipped.")
          End Try
          Console.WriteLine()
        End While
        TextFileReader.Dispose()
        frmMain.DataGrid1.DataSource = TextFileTable
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, February 3, 2011 1:49 PM
  • Hi Tlm2,

    Welcome come to Ado.net manager provider and all the participators.

    I think you have the jet and the jet odbc driver with 32 bit version only and it can't in 64 bit mode.

    There is a new driver,"2010 office system driver", which supports both 32 bit and 64 bit versions. The provider name is "provider=microsoft.ace.oledb.12.0", you don't need to buy or install the office suite, the components are available as a separate download:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    For the detail informations, Please refer to the link:

    http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx?wa=wsignin1.0

    I hope that would be help you.

    Best Regards.



    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 4, 2011 1:55 AM
    Moderator
  • Thank you, David, that is helpful! It sounds like the perfect solution, except - for arcane reasons, I am stuck using 32-bit Office on a 64-bit machine, and I am building an app for a 64-bit environment. (The blog post says: "If you have Office 2010 32bit is installed on the machine you cannot install "Microsoft Access Database Engine 2010 Redistributable" 64bit .")

    So I think I need a solution at present that is not tied to Office, but I will keep this in mind for the future. I will try the oddly named Microsoft.VisualBasic.FileIO.TextFieldParser, despite the fact that I'm using C#.

    Thanks,

    Tamra

    Monday, February 7, 2011 11:32 PM