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=12.0.0.0,
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?
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.
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.
Source Error:
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
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.
I am pulling from excel 2007. The assembly version was 12.0.0.0 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.
nrana
Member
45 Points
25 Posts
Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 01:19 PM|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=12.0.0.0, 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.
bbcompent1
All-Star
33097 Points
8529 Posts
Moderator
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 01:37 PM|LINK
I think you are definitely going to need to install Office since you are calling out to it by way of Interop Assemblies.
nrana
Member
45 Points
25 Posts
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 01:50 PM|LINK
Thanks for your reply.
So Installing Runtime would not help me?
bbcompent1
All-Star
33097 Points
8529 Posts
Moderator
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 01:52 PM|LINK
It can't hurt to try. Its been a while but I distinctly remember having to install the full office app.
bbcompent1
All-Star
33097 Points
8529 Posts
Moderator
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 02:03 PM|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.
http://support.microsoft.com/kb/321686
nrana
Member
45 Points
25 Posts
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 05:03 PM|LINK
I have removed two following assembly from my webconfig file.
And change my connection string to
Now I am getting following error. If I keep the second assembly shown above it does not find the assembly.
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
http://www.microsoft.com/download/en/details.aspx?id=13255
but no luck.
bbcompent1
All-Star
33097 Points
8529 Posts
Moderator
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 05:07 PM|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.
nrana
Member
45 Points
25 Posts
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 05:24 PM|LINK
I am pulling from excel 2007. The assembly version was 12.0.0.0 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.
bbcompent1
All-Star
33097 Points
8529 Posts
Moderator
Re: Do I Need Office Installed on Server to Import from Excel to SQL in Asp.net
Dec 14, 2011 05:26 PM|LINK
Try this connection string from http://connectionstrings.com/excel-2007