CODEWITHSUNDEEP
c#asp.netiis-7oledbweb-deployment
jatin khimani
jatin khimani

Reputation: 21

'Microsoft.ace.oledb.12.0' not read all the rows from .xlsx file on server (IIS7)

I know that is this question has dozen of answers and posts, but nothing works for me.

We have my MVC4 application and i deployed it to IIS7 to my server and one more machine where we don't have visual studio installed.

We are doing import/export data from/to excel for doing bulk entry.

we do have data around 20000 in one sheet with .xlsx format of Office 2007(File is save as Excel Work book).

Now when i read data by using following code it just read data around 4000-5000 rows only.

This whole below code working fine in my local machine where i do code and read all 20000 rows data from "Excel 97-2003 Workbook with .xls" but not working with "Excel Workbook with .xlsx".

private void FillDataSet(HttpPostedFileBase file, DataSet ds)
{
  using (
    var con =
      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" File Path";Extended Properties=Excel 12.0"))
  {
      con.Open();
      var dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
      var strSheetName = "";
      strSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();          
      var cmd = new OleDbCommand();
  var da = new OleDbDataAdapter();
  cmd.Connection = con;
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "SELECT * FROM [" + strSheetName + "]";
  da = new OleDbDataAdapter(cmd);
  da.Fill(ds);        
  }
}

I have gone through the below steps and solutions but it's not working for me.

-Installing Microsoft Access Database Engine 2010 Redistributable.

-Installing 2007 Office System Driver: Data Connectivity Components

we are confuse why it is working on my local machine and not working on server.

Please provide any idea or solution for this issue..

What I have tried: -From the solution explorer right-click your project then click Properties. -Click the Build tab -Change Platform target from: Any CPU to x86 | Any CPU to x64 -Re-build your solution -Also made changes on IIS in Advance setting with Enable 32-Bit application as True/False.

Upvotes: 2

Views: 3179

Answers (3)

Wayne Lin
Wayne Lin

Reputation: 1

I had the same issue. The program worked fine in my local/SIT environment, but I couldn't read all of the Excel rows. And it would not allow for me to change the platform target from Any CPU to x86 | x64. My production environment worked find on Windows Server 2012 R2 and IIS8 when reading Excel files (.xlsx).

I was able to fix this with following steps:

  1. Check the IIS application pool > Advanced Settings > Enable 32-bit applications True | False. (I think this lead the program to use the x32 | x64 bit driver)
  2. Search your ODBC Data Sources and check the related x32 | x64 driver. And upgrade the driver from v12 to v14 (remove Access Database Engine 2007, install Access Database Engine 2010)
  3. Connection String: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;"

Upvotes: 0

Catalin Duceac
Catalin Duceac

Reputation: 11

Try this. I had some problems as well.

  new OleDbConnection(@"Provider=Provider=Microsoft.Jet.OLEDB.4.0;Data Source;Data Source=" File Path";Extended Properties=\"Excel 8.0;HDR=Yes;\";))

Upvotes: 0

Tanmay Nehete
Tanmay Nehete

Reputation: 2198

some time while reading the data from excel you need to convert all the data into Text Format

OLD Connection String

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" File Path";Extended Properties=Excel 12.0"

Updated Connection string

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" File Path";Extended Properties=Excel 12.0;HDR=YES;IMEX=1';"

This will convert your all data into Text format all the data will be displayed.

UPDATED CODE

private void FillDataSet(HttpPostedFileBase file, DataSet ds)
{
  using (
    var con =
      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" File Path";Extended Properties=Excel 12.0;HDR=YES;IMEX=1';"))
  {
      con.Open();
      var dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
      var strSheetName = "";
      strSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();          
      var cmd = new OleDbCommand();
  var da = new OleDbDataAdapter();
  cmd.Connection = con;
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "SELECT * FROM [" + strSheetName + "]";
  da = new OleDbDataAdapter(cmd);
  da.Fill(ds);        
  }
}

Upvotes: 2

Related Questions