ASP.NET MVC2 and IIS 7.5 . Problems when read large data from Excel 2010

Question

I have a problem when reading large amount of data from Excel 2010

I use the code below to read data:

public static DataTable GetExcelData(string filePath, string extension)
        {
            try
            {
                string conStr = "";
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        //conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        //conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR={YES}'";
                        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                        break;
                    case ".xlsx": //Excel 07
                        //conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                        break;
                }

                //conStr = String.Format(conStr, filePath, true);
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = connExcel;

                //Get the name of First Sheet
                log.Debug("Excel ConnectionString = " + conStr);
                connExcel.Open();
                log.Debug("Open Excel connection ok");
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();
                log.Debug("SHEET NAME = " + SheetName);
                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();

                return dt;
            }
            catch(Exception ex)
            {
                log.Debug("GetExcelData Error:" + ex.ToString());
                return null;
            }
        }

My ASP.NET MVC 2.0 project works correctly when running on Visual Studio 2010 with both a small Excel file and a large Excel file.

But, when I deploy to IIS 7.5 (windows 7 pro), my website only works correctly with small Excel file.

With a large excel file, it throw the following error:

GetExcelData Error:System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()

(On IIS 7.5, my website uploads large file Excel success)

How can I fix this?

This has come up before here, http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format Hope this helps — Robert, Dec 08 '13 at 16:12

score 0 · Answer 1 · edited May 23 '17 at 11:57

It might be a web.config change that is needed to allow larger files over 4MB. You can add the following to the web.config to allow this,

<configuration>
    <system.web><!-- maxRequestLength (KB) -->
        <httpRuntime maxRequestLength="10000" executionTimeout="110"/>
    </system.web>
</configuration>

This may help,

Maximum request length exceeded

I use the following connection string where '{0}' is the file path, for .xls files,

strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties= ""Excel 12.0 Xml;HDR=YES;IMEX=1"""

and for .xlsx files I use,

strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""";

It won't be the size of the files if you have set up the web.config up. I would try and save the file as an 97-2003 workbook .xls file and use the relevant connection string. If you are hosting this on a server, the server will need the Office drivers needed to process the file.

UPDATE Set HDR to No and go through each row dismissing the header row. This will avoid a data type issue that occurs when the columns are checked for the data type.

[On IIS 7.5] I try import Excel 2003 and my project work fine. Maximum rows in Excel 2003 are 65535 and all imported to DB But with Excel 2007, my project is not work when number of rows ~20.000 I think on IIS, website may be miss some dll — rocket, Dec 08 '13 at 17:05
I upload file to Upload folder OK, but when read file from Upload folder with Excel 2007 large data fail — rocket, Dec 08 '13 at 17:13

ASP.NET MVC2 and IIS 7.5 . Problems when read large data from Excel 2010

1 Answers1