Results 1 to 16 of 16

Thread: Read Excel file except fist 14 rows

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Read Excel file except fist 14 rows

    Hi,

    I need to read an Excel file (.xls, 2003 format). The data in the excel file is composed of two parts:
    - The first 14 rows consist of all kinds of stuff, buttons, merged columns, stuff I don't need. You can see this as a header for the worksheet.
    - The next row (number 15 viewed in Excel) contains column header names
    - All remaining rows contain the data I need to read.


    I have tried reading this file using two different methods. The first method is simply using OleDb:
    vb.net Code:
    1. Private Function GetDataTable(ByVal sheetName As String)
    2.         Dim dt As DataTable = Nothing
    3.  
    4.         Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR={1};IMEX={2}"";", _
    5.                                        Me.FilePath, _
    6.                                        "No", _
    7.                                        "1")
    8.         Using conn As New OleDbConnection(connString)
    9.             Using cmd As New OleDbCommand()
    10.                 cmd.Connection = conn
    11.                 cmd.CommandType = CommandType.Text
    12.                 cmd.CommandText = String.Format("select * from [{0}$]", sheetName.Trim())
    13.  
    14.                 conn.Open()
    15.                 dt = New DataTable
    16.                 dt.Load(cmd.ExecuteReader)
    17.                 conn.Close()
    18.             End Using
    19.         End Using
    20.  
    21.         Return dt
    22.     End Function
    I have tried fiddling with the HDR and IMEX settings, but the result is always the same. I keep getting an error that says "Not a legal OleAut date", which is supposed to mean it cannot convert a date to .NET DateTime format.

    I am sure this error occurs because it is trying to read all the stuff in the first 14 rows but fails, because it does not contain simple data.

    So my first question is:
    How can I tell it to only start reading after row 14?

    The file comes from a third party so I have no control over the contents. I could make a copy of the file and delete the first 14 columns, then try to read that copy instead, but the only way I think that is possible (I haven't tried this though) is via Office Interop, which brings its own problems.





    That brings me to the second method I tried to read the file: using Office Interop.

    I simply create a new Excel.Application, open the Workbook (excel file), find the correct worksheet (there's multiple sheets that I have to read) and read the data from the cells directly.

    This works. I can read the data. But there is a very big problem: this method seems to actually open an instance of Excel and the workbook, and after it is closed it actually closes this instance again. When that happens, Excel tells the user that the file has changed and asks whether it needs to be saved. It seems that merely opening the file changes something (because my code is only reading, not changing, and the same happens when I just open the file directly in windows), and when it is closed a dialog pops up asking for user input.

    This is a big problem, because my code does not continue until the workbook is closed (workbook.Close method). And the user has to close the workbook. This application is supposed to be run as a service, and it must run once per day, and I cannot guarantee that a user will be around every day to close the Excel dialog...


    EDIT
    See post 2 for update



    Thanks for any help!

  2. #2

    Thread Starter
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Read Excel file except fist 14 rows

    Ok, I managed to supress the user input dialog (Excel.Application.DisplayAlerts = False).


    So, I can now use office interop to read the excel file.

    However, while I found this solution, I also found that Microsoft does not recommend using interop in an environment without a user, like I have:
    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.

    Besides the technical problems, you must also consider the feasibility of such a design with respect to licensing. Current licensing guidelines prevent Office Applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).
    So... Yeah, I don't really want to use this method!


    So I still want a solution to my first question: how do I read the excel files but skip the first 14 lines so it does not error on them..?

    As I said, I could copy the file and delete the first 14 rows, but for that I again need office automation, and we're back to my current problem...

  3. #3
    PowerPoster 2.0 Negative0's Avatar
    Join Date
    Jun 2000
    Location
    Southeastern MI
    Posts
    4,367

    Re: Read Excel file except fist 14 rows

    Have you tried any third party products? I have used the Aspose suite for Word documents on a server and that worked great for me. They have an excel version called Aspose.Cells.

    It basically gives you access to all of the content of the file without needing the Microsoft product installed on the server. They are relatively inexpensive for a business to purchase, weighing it against the amount of time it may take you to find a workaround.

  4. #4

  5. #5
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: Read Excel file except fist 14 rows

    Or you have gemboxsoftware (which I am investigating myself as it happens) which are far far cheaper if you have to release this as it has no royalty fees.

  6. #6
    PowerPoster 2.0 Negative0's Avatar
    Join Date
    Jun 2000
    Location
    Southeastern MI
    Posts
    4,367

    Re: Read Excel file except fist 14 rows

    They have a free trial, so you might be able to quickly proof it out to see if it works. If it does, then you can suggest spending $900 now or taking two weeks to see if you can write a workaround.

    I've never had good luck with the OLE connection and an excel file that had anything more than really simple tabular data in it.

    Technically you might be able to code something that does use excel and technically works, even though it is against the Microsoft recommendation. I have seen people do this and have it work for them.

    Another option might be some sort of automation using OpenOffice, but I haven't looked into what kind of Automation they offer in a few years.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Read Excel file except fist 14 rows

    Quote Originally Posted by Negative0 View Post
    They have a free trial, so you might be able to quickly proof it out to see if it works. If it does, then you can suggest spending $900 now or taking two weeks to see if you can write a workaround.

    I've never had good luck with the OLE connection and an excel file that had anything more than really simple tabular data in it.

    Technically you might be able to code something that does use excel and technically works, even though it is against the Microsoft recommendation. I have seen people do this and have it work for them.

    Another option might be some sort of automation using OpenOffice, but I haven't looked into what kind of Automation they offer in a few years.
    Unfortunately I think me spending two weeks will be cheaper then spending 900 bucks for them

    OpenOffice might be a good idea, I'll check that out.

  8. #8
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: Read Excel file except fist 14 rows

    Never tried this on a machine without Excel installed (and even that was years ago) but you used to be able to connected via ODBC/ADO.

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Read Excel file except fist 14 rows

    Quote Originally Posted by Grimfort View Post
    Never tried this on a machine without Excel installed (and even that was years ago) but you used to be able to connected via ODBC/ADO.
    That's my first method, where I get a "Not a legal OleAut date" error.

  10. #10
    PowerPoster 2.0 Negative0's Avatar
    Join Date
    Jun 2000
    Location
    Southeastern MI
    Posts
    4,367

    Re: Read Excel file except fist 14 rows

    Oh and if you are glutton for punishment, you could technically write your own parser for excel files:

    http://www.microsoft.com/interop/doc...ryFormats.mspx

    I know that this is not realistic, but in searching I found this and thought it was interesting. The Excel spec is over 1100 pages.

  11. #11
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Read Excel file except fist 14 rows

    The following reads a specific range into a DataTable. I tried it with a work sheet with several columns of integer, one column DateTime and one column of String and the data was read in fine. Not sure how you determine how many columns and rows but I guess you could widen the range of both row and col then figure out which ones are empty. Without IMEX there were issues

    Here is a decent resource at Code Project.
    http://www.codeproject.com/KB/miscct...ta_access.aspx


    Code:
        Private Sub ReadRange()
            Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
                "data source='C:\Data\Book1.xls';Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
    
            Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
                MyConnection.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A10:F17]", MyConnection)
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
    
                DataGridView2.DataSource = dt
    
            End Using
    
        End Sub
    Last edited by kareninstructor; Dec 23rd, 2010 at 10:14 AM.

  12. #12
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Read Excel file except fist 14 rows

    Quote Originally Posted by Negative0 View Post
    Have you tried any third party products? I have used the Aspose suite for Word documents on a server and that worked great for me. They have an excel version called Aspose.Cells.

    It basically gives you access to all of the content of the file without needing the Microsoft product installed on the server. They are relatively inexpensive for a business to purchase, weighing it against the amount of time it may take you to find a workaround.
    Aspose Cells is a great library for working with Excel, I've been using and has saved me time and money. For what Nick is looking for it is overkill.

  13. #13

    Thread Starter
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Read Excel file except fist 14 rows

    Quote Originally Posted by kevininstructor View Post
    The following reads a specific range into a DataTable. I tried it with a work sheet with several columns of integer, one column DateTime and one column of String and the data was read in fine. Not sure how you determine how many columns and rows but I guess you could widen the range of both row and col then figure out which ones are empty. Without IMEX there were issues

    Here is a decent resource at Code Project.
    http://www.codeproject.com/KB/miscct...ta_access.aspx


    Code:
        Private Sub ReadRange()
            Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
                "data source='C:\Data\Book1.xls';Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
    
            Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
                MyConnection.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A10:F17]", MyConnection)
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
    
                DataGridView2.DataSource = dt
    
            End Using
    
        End Sub
    I see, I didn't know you could select a range in the query. I'll try that. I don't know how many rows there are however, I have to keep checking rows until an empty row is found. I suppose I could run the query several times, each times 100 rows at a time or something.

    What concerns me more is this statement in the article you linked to:
    A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.
    My data begins in column A, row 16 (15 are the column headers), and there is a whole load of data in rows above that, so it may not work after all...

    I'll get back to you.

  14. #14
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Read Excel file except fist 14 rows

    You should be able too as shown here
    Last edited by kareninstructor; Jul 20th, 2011 at 12:56 PM.

  15. #15

  16. #16
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Read Excel file except fist 14 rows

    Quote Originally Posted by NickThissen View Post
    Awesome, thanks, it worked Didn't expect to find a solution this quick!
    Good to hear it works for you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width