Solved

Retrieving Unicode data using OleDBDataReader in c#

Posted on 2013-11-19
2
1,101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…

726 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