Solved

Retrieving Unicode data using OleDBDataReader in c#

Posted on 2013-11-19
2
1,039 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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now