reading excel file, merged cells

hello
Please check the sample excel sheet I intend to use to get a clear understanding of my question

I need to use datareader to query using a sql statement to retrieve data from the woksheet. I have seen many examples on how to do this but all those consider the first row (Row 1 in spreadsheet) to contain column names. However, in my example it starts in Row 4. So, how do i write a query to read from the table and retrieve the values?

sample-worsheet.jpg
LVL 7
maXXXeEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Are the files always going to have those headers?  Can you just read the first 4 lines, ignore them, and store the rest?
0
maXXXeEAuthor Commented:
yes, the files are going to have those header. But, the format of the worksheet is always same. i.e. table always starts from row 4, with the header at row 4.

So, how do i read it?
0
Bob LearnedCommented:
If you have an OleDbDataReader, then just use a while block to call the Read method until a counter reaches 4 or the end-of-file is reached, and then if you aren't at the end-of-file, continue reading the rest of the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

maXXXeEAuthor Commented:
So, do you mean, that i read from the excel file, write to another after skipping the headers?
0
maXXXeEAuthor Commented:
Can you help me out with more information, with a code or article, as I am not able to do it.
0
Bob LearnedCommented:
I am trying to learn more about DB2 and .NET, but I would love to help you with what you have already tried.
0
maXXXeEAuthor Commented:
the problem is since there is a merged field, ado.net takes only 1 column.
So, if i try to read the 2nd or 3rd column, it gives an exception
0
Bob LearnedCommented:
Can you show me what you mean by that?
0
maXXXeEAuthor Commented:
take the above worksheet as an example.

i try to read the file using ado.net, and write the table after ignoring the headers as you told.
I use oledbdatareader.

when i write i can refernce the first column. eg: datareader.GetString(0)

But if i reference other columns using datareader(datareader.GetString(1) or datareader.GetString(2) it throws "index out of bounds exception.
0
Bob LearnedCommented:
The OleDbDataReader has the FieldCount property.  When you get to the place where you are getting an exception, what does FieldCount equal?
0
maXXXeEAuthor Commented:
fieldcount is 1.
0
Bob LearnedCommented:
Try checking FieldCount before trying to access values from index 1 and higher.
0
maXXXeEAuthor Commented:
fieldcount is ALWAYS 1.

And datareader it takes only 1 column.
0
Bob LearnedCommented:
The OLE DB driver is pretty brain-dead, as far as I am concerned.  When the file is opened, it makes determinations, and goes with those based on what it learned.  Often times it gets things wrong, like field type, and you get blank rows.  And, as you are finding, it is getting mixed up determining columns, because of the way that it stores merged cells.

Sometimes, the problem can be solved by using IMEX=1 in the connection string.
0
maXXXeEAuthor Commented:
i already have IMEX=1 in connection string
0
Bob LearnedCommented:
There aren't a lot of options when it comes to OLE DB, to solve your problem.  I would say it is not the right choice for this case, but the other options are not very good either.
0
maXXXeEAuthor Commented:
what are the other options?

if i can read the content of excel file, and make a second excel file with the table excluding the headers that is fine for me. It is not important that i use oledb.
0
Bob LearnedCommented:
1) COM Interop with ASP.NET is messy at best.

2) I have seen BIFF8 readers, that read the file at the doc stream level.

0
maXXXeEAuthor Commented:
can u explain or provide a link to articles which shows its implementation?
0
Bob LearnedCommented:
0
maXXXeEAuthor Commented:
but i guess this can only read, not manipulate the data on worksheet
0
Bob LearnedCommented:
Right, that is a reader, not a reader/writer.  
0
maXXXeEAuthor Commented:
can u suggest anything that can manipulate the excel worksheet?
0
Bob LearnedCommented:
There is a product from Aspose and something from Infragistics (which I use).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.