?
Solved

Retrieving Unicode data using OleDBDataReader in c#

Posted on 2013-11-19
2
Medium Priority
?
1,305 Views
Last Modified: 2013-11-20
Hi,

Today I have another strange and frustrating issue to cope with. I have written a piece of code to read and parse .xlsx files using OleDBDataReader.

So, I build a connection to an excel file.
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0;");

Open in new window

Then I open a command object and a datareader.
OleDbCommand myCommand = new OleDbCommand(" SELECT * FROM [" + SheetName + "$]", con);
                OleDbDataReader myDataReader = myCommand.ExecuteReader();

Open in new window

Finally data read statements using an index.
while (myDataReader.Read())
{
  for (tempindex = 0; tempindex < 10; tempindex++)
  {
      b = myDataReader.GetValue(tempindex).ToString();
  }
}

Open in new window

This is where the issue is. When there is multilingual data, b gets "blank", otherwise there is no issue.
I am using MSExcel 2010.
Sample excel sheet data is as given below. I entered some dummy chinese data in Efficiency column to test. On First and Third row, I get blanks where as in other rows I get valid data.
sample xls fileAny help in this matter will be greatly appreciated as I am stuck at the moment.

Thanks,
Amit
0
Comment
Question by:AmitJain001
2 Comments
 
LVL 11

Accepted Solution

by:
jasonduan earned 2000 total points
ID: 39660856
Amit,

I don't think the issue is caused by DataReader. It is caused by OLE DB Driver. You can confirm this by reading the data into a DataTable and inspect the data in "Debug | Watch" window.

When you say "On First and Third row, I get blanks", do mean you get blanks for all fields or blank for column "G" only?

Try this to see if it works:

1. Append HDR=NO;IMEX=1;  to connection string

OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0;HDR=NO;IMEX=1;");

Open in new window


2. If #1 fixes your issue, then stop here, otherwise, continue.
    Specify data range in the query

string datarange = "A2:K5";
OleDbCommand myCommand = new OleDbCommand(" SELECT * FROM [" + SheetName + "$" + datarange + "]", con);  

Open in new window


   If this does not fix your issue, then ignore my answer; otherwise, continue.

Using the following technique to figure out "datarange".
uses SELECT * FROM [" + SheetName + "$]", read the data to a DataTable or DataReader, and see how many rows and columns in the sheet (you don't really care about the actual data, all you need is the row count and column count). From there, you can "calculate" the data range.

Hope this help.

Jason
0
 

Author Closing Comment

by:AmitJain001
ID: 39664937
Hey Jason,

That was really neat solution. I wasn't aware of Mixed DataTypes option and I have suffered much in the past because of that. Now I know how to get around it.

Your solution totally worked and I am very glad.

Thank You.
Amit
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
SingleRun is a tool that ensures that only one instance of an application is started, running it again brings the application to focus.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question