Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Retrieving Unicode data using OleDBDataReader in c#

Posted on 2013-11-19
2
Medium Priority
?
1,213 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 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

610 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