Reading an Excel file from a WinForm application

I need some help reading an Excel file from .NET application. I have an Excel file that has data starting from the 3rd row. The first row has some info about the file, second row has the column names and the 3rd row has data.

The application is reading the values and column names correctly when I delete the first row so that the new first row contains the column names and data from second row onwards.

Is there a way to specify that the header row is the second row and data starts from the third row Or is there a better way to handle this

I don't want the user to delete the first row for this to work.

Any help is appreciated!

Thanks
Basani

.NET Development11

I believe Jet does not have such property in a connection string to identify starting row for the reads. What you could do in your code is to put some logic that will analyze if values in first N-rows are Nulls, and then delete them

Probably the easiest way is to modify your query string to skip the first row, something like this:

string myQuery = "SELECT F1, F2, F3 FROM [" + mySheetName+ "$] WHERE (NOT F1 = 'SomeTextInFirstRow')";

The alternative way is to skip the row in the OleDbDataReader.Read() loop if it is empty.

Bojan,

Thanks for the reply. I want the column names also. So, I figured out a different way where I am specifying the range in the dynamic query. I am making two roundtrips to the database.

1. First one gets the total number of records (SELECT num = COUNT(*) FROM [Sheet1$]) so that we can determine the last cell.

2. Second one gets the actual data with column names also. ("SELECT * FROM [Sheet1$A5:C" + (num+1).ToString() + "]")

Here A5 is the starting cell in the header row and C(num+1) is the last cell in the range. You have to specify the Extended Properties "HDR=Yes" in the connection string.

I'll update with the actual code later!

Thanks
Basani

I can confirm that the method above works. One thing to note with the second query: The "C" in "$A5:C" can be replaced by whatever column you expect to extract from the query. For example, I needed to extract 6 rows, so I replaced this "C" with an "F". I wanted to select data beginning at row 3. Thus, my select statement was like this:

oleCmd.CommandText = String.Format("SELECT [Rate Code], [Rate], [Load Profile] " & _
"FROM [Rate Conversion Table$A3:F{0}] " & _
"WHERE [Company] = '{1}'", _
rows, Me.mCompany)

I hope that this helps anyone with a similar problem! God bless!