Data is missing while reading excel file using OLEDB

Question

I am using OLEDB to read excel file into datatable. But the problem is, some values are missing(Empty). In my excel sheet one column datatype is General, it has mixed values like string and integers. Most of the cell values are integers. Why OLEDB is skipping string values.

OleDbConnection connection = new OleDbConnection();

connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + "; Extended Properties=\"Excel 12.0;IMEX=1\";";
OleDbCommand myAccessCommand = new OleDbCommand();

myAccessCommand.CommandText = "Select * from [" + sheetName + "]";

OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

myDataAdapter.Fill(myDataSet);

We still need to see how you actually retrieve the data out of the sheet — Marco
– Marco, Commented Mar 21, 2014 at 7:34

Hassan Nisar · Accepted Answer · 2015-07-30 11:00:20Z

5

Check following link and see points under "RESOLUTION": http://support.microsoft.com/kb/194124

Please see point 2 NOTE.

Setting IMEX=1 is entirely dependent on your registry settings. By default, first 8 rows are checked to determine the data type. IMEX=1 can give unpredictable behaviors, such as skipping string values. There is also one small workaround for this problem. Just add single quote (') before every cell value in excel. Every cell will be treated as string.

edited Jul 30, 2015 at 11:00

answered Mar 21, 2014 at 7:38

Hassan Nisar

5,4502 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bharadwaj Over a year ago

Add a little explanation also.

wctiger Over a year ago

In Excel set cell format to 'Text' instead of 'General' will also work.

jps · Accepted Answer · 2017-04-11 10:24:53Z

0

Add IMEX=1 to the connection string as below:

string con = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + @"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", fileName);

edited Apr 11, 2017 at 10:24

jps

22.9k18 gold badges92 silver badges121 bronze badges

answered Apr 11, 2017 at 8:57

Anil Kanojia

11

Collectives™ on Stack Overflow

Data is missing while reading excel file using OLEDB

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related