none
ASP .Net 64 Bit Read CSV XLS. RRS feed

  • Question

  • Having seen many similar questions in this respect - I'm hoping for a more creative solution.
    We have an ASP.Net app that allows clients to upload CSV or XLS data.
    Once uploaded, we have been using OLEDB to read the file, parse and validate it in a DataTable before presenting any errors in formatting or data to the client to confirm and deal with.

    The app is now being migrated to 64 bits and even with the 64 bit MSDASQL Provider I understand the Text and Excel drivers are not going to be updtaed.

    This is a small component in a very large application and it is holding the whole thing back from being run in 64 bits, so I would like to know what alternatives I have.  Do I need to install Excel on the web server and use some sort of Excel object layer to parse the client files?

    I understand there used to be a CSV string parser in the VB Scripting namespace... Is that still useable?

    In short: Is there any way we can keep the current "Data Accessing" model for XLS and CSV, and still migrate to 64 bits, or do we have to kiss either one or the other goodbye!
    • Moved by Nai-dong Jin - MSFT Tuesday, September 22, 2009 4:47 AM Off topic (From:SQL Server Data Access)
    Thursday, September 17, 2009 6:37 AM

Answers

  • There currently is no 64-bit option with respect to OLEDB and Jet. You can handle CSV files using .NET File I/O so that shouldn't be an issue:

        Public Sub ReadTextFileParser()
    
            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\List.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    'Dim CurrentField As String
                    Console.WriteLine(CurrentRow(0).ToString)
                    Console.WriteLine(CurrentRow(1).ToString)
                    Console.WriteLine(CurrentRow(2).ToString)
                    Console.WriteLine(CurrentRow(3).ToString)
                    'For Each CurrentField In CurrentRow
                    ' Console.Write(CurrentField & Space(1))
                    'Next
                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()
    
        End Sub

    The Excel data is a separate issue. You may have to look at a 3rd party solution. Automating Excel isn't really a suitable solution when performed from unattended non interactive server apps because of threading limitations.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Thursday, September 24, 2009 3:03 AM
    Tuesday, September 22, 2009 2:50 PM
  • For the XLS files you would need to use third partry components, since providers from Microsoft do not support 64-bit mode. You could try components for reading XLS files from my web site. They support both 64- and 32- platforms
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Thursday, September 24, 2009 3:03 AM
    Wednesday, September 23, 2009 10:26 AM
    Moderator

All replies

  • There currently is no 64-bit option with respect to OLEDB and Jet. You can handle CSV files using .NET File I/O so that shouldn't be an issue:

        Public Sub ReadTextFileParser()
    
            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\List.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(";")
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    'Dim CurrentField As String
                    Console.WriteLine(CurrentRow(0).ToString)
                    Console.WriteLine(CurrentRow(1).ToString)
                    Console.WriteLine(CurrentRow(2).ToString)
                    Console.WriteLine(CurrentRow(3).ToString)
                    'For Each CurrentField In CurrentRow
                    ' Console.Write(CurrentField & Space(1))
                    'Next
                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()
    
        End Sub

    The Excel data is a separate issue. You may have to look at a 3rd party solution. Automating Excel isn't really a suitable solution when performed from unattended non interactive server apps because of threading limitations.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Thursday, September 24, 2009 3:03 AM
    Tuesday, September 22, 2009 2:50 PM
  • For the XLS files you would need to use third partry components, since providers from Microsoft do not support 64-bit mode. You could try components for reading XLS files from my web site. They support both 64- and 32- platforms
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Thursday, September 24, 2009 3:03 AM
    Wednesday, September 23, 2009 10:26 AM
    Moderator