• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2469
  • Last Modified:

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
0
maXXXeE
Asked:
maXXXeE
  • 12
  • 12
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now