Solved

Retrieving Unicode data using OleDBDataReader in c#

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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

691 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