3

To import excel to datatable, I am using the simple code:

string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", physicalFolder + FileUpload1.FileName);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
DataSet ds = new DataSet();

When in one of the rows of excel, if my row looks like below

enter image description here

strings are ommited and my data set looks like this

enter image description here

However if I add some strings and if my upload looks like this:

enter image description here

Then my dataset looks like it does not omit the strings:

enter image description here

HOY
  • 1,067
  • 10
  • 42
  • 85
  • I was also facing the same issue. Then i manually changed the datatype of that particular column in excel from general to text. And it worked – beard_less_coder Oct 31 '20 at 11:24

4 Answers4

4

Try to change your oledbconnection string as following format:

Code Snippet OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

Note: "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

Thalaiselvam
  • 41
  • 1
  • in my case it is not working. The case is in a column first cell value is date and others are string and in an another column first cell value is string and rest are numeric. The rest values are still blank. – Haider Ali Wajihi Oct 09 '15 at 11:33
2

MD.Unicorn's answer is not 100% correct. Your OLEDB provider uses a settings named TypeGuessRows to determine how many rows are read to decide the data type of a column. Unfortunately this setting cannot be specified in the connection string and must be changed in the system registry. See this question for more details.

Community
  • 1
  • 1
dotNET
  • 33,414
  • 24
  • 162
  • 251
1

This is because the provider decides on the type of the column from first row of the column (the row after the header row). When first row contains a number, the type of column is double or another number type, so it cannot contain string values.

I tried every possible way (setting the table structure beforehand, using a DataReader, changing the format of the cell, ...) and they all failed. It seem to be the problem with Microsoft.Jet.OLEDB provider. I highly recomment you to use a third party excel reading library. There are plenty of open source libraries available.

If your file is a Excel 2007 (.xlsx) file, I highly recommend using EPPluse. It is also available as a NuGet package.

Otherwise, you can take a look at this answer to find a few more libraries.

Community
  • 1
  • 1
Mohammad Dehghan
  • 17,853
  • 3
  • 55
  • 72
1

use IMEX=1 in connection string. hope it will resolve this issue..

raj
  • 11
  • 1