Last post Dec 14, 2011 05:26 PM by bbcompent1
Dec 14, 2011 01:19 PM|nrana|LINK
I have created Import from excel module to create table in SQL with Excel data and it is running fine on my dev machine. But when I moved module to my production server. It is giving me "Could not load file or assembly 'Office, Version=220.127.116.11,
Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified"
Exception. My target framework is asp.net 4.0.
Do I need to have Office Installed on server to make this work or I can installed office runtime without Office installed on server?
Any suggestion. Thanks in advance.
Dec 14, 2011 01:37 PM|bbcompent1|LINK
I think you are definitely going to need to install Office since you are calling out to it by way of Interop Assemblies.
Dec 14, 2011 01:50 PM|nrana|LINK
Thanks for your reply.
So Installing Runtime would not help me?
Dec 14, 2011 01:52 PM|bbcompent1|LINK
It can't hurt to try. Its been a while but I distinctly remember having to install the full office app.
Dec 14, 2011 02:03 PM|bbcompent1|LINK
I'm thinking you might be able to accomplish this without office being installed. You need OleDB for .net which will let you tap into your excel sheet. Here's a how-to on doing that below.
Dec 14, 2011 05:03 PM|nrana|LINK
I have removed two following assembly from my webconfig file.
<add assembly="Office, Version=18.104.22.168, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" />
<add assembly="Microsoft.Office.Tools.Excel, Version=22.214.171.124, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
And change my connection string to
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\""
Now I am getting following error. If I keep the second assembly shown above it does not find the assembly.
External table is not in the expected format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: External table is not in the expected format.
Line 93: string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
Line 94: OleDbConnection excelCon = new OleDbConnection(connString);
Line 95: excelCon.Open();
Line 96: OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [Sheet1$]", excelCon);
I also tried to install Microsoft Access Database Engine 2010 Redistributable.
Here is the link
but no luck.
Dec 14, 2011 05:07 PM|bbcompent1|LINK
Ok, what version of Excel are you trying to pull from? If its 2010, you can parse it by way of XML I think. I personally haven't tried it yet but that is what I have heard. Earlier versions of excel, you have to use OleDB connections to get the data out.
You might want to take a peek at www.connectionstrings.com as they have all the various connection strings you will ever need.
Dec 14, 2011 05:24 PM|nrana|LINK
I am pulling from excel 2007. The assembly version was 126.96.36.199 in my webconfig file. But I also tried 14.0 as mentioned in http://www.microsoft.com/download/en/details.aspx?id=13255 after
installing Microsoft access database engine redistributable.
Dec 14, 2011 05:26 PM|bbcompent1|LINK
Try this connection string from
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";