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:

Private Function GetDataTable(ByVal sheetName As String)
        Dim dt As DataTable = Nothing
 
        Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR={1};IMEX={2}"";", _
                                       Me.FilePath, _
                                       "No", _
                                       "1")
        Using conn As New OleDbConnection(connString)
            Using cmd As New OleDbCommand()
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = String.Format("select * from [{0}$]", sheetName.Trim())
 
                conn.Open()
                dt = New DataTable
                dt.Load(cmd.ExecuteReader)
                conn.Close()
            End Using
        End Using
 
        Return dt
    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!

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:

Quote:

---

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...

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.

That sounds good, thanks, but I can't do more than suggest it at this point. I am still looking for a different solution in the mean time :)

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.

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.

Quote:

---

Originally Posted by Negative0

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 :lol:

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

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.

Quote:

---

Originally Posted by Grimfort

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.

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.

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

---

Quote:

---

Originally Posted by Negative0

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.

Quote:

---

Originally Posted by kevininstructor

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:

Quote:

---

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.

You should be able too as shown here

Awesome, thanks, it worked :) Didn't expect to find a solution this quick!

Quote:

---

Originally Posted by NickThissen

Awesome, thanks, it worked :) Didn't expect to find a solution this quick!

---

Good to hear it works for you!