I am using SQL compact database table called "Contacts" contains firtname & email. My project requiement is user can upload excel sheet using C# razor code and import into contacts table. In excel sheet contains the same column like firstname & lastname.
So basically it reads the data from excel ans stroing into the database.
If your Excel file has an xlsx extension, you need to use the ACE OleDb provider to connect to it and query it. If it has an xls extension, you can use the JET OleDb provider. Note that these providers must be installed on the server. Often, JET is installed
as part of the Operating System files, but ACE isn't.
If you can persuade your users to save the file as csv and then upload that instead, your job will be much easier. Then you can use the simple File API that forms part of .NET:
var contacts = File.ReadAllLines(Server.MapPath("~/Path_To_File"));
foreach(var line on contacts){
var contact = line.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
db.Execute("INSERT INTO Contacts (FirstName, LastName, Email) VALUES (@0, @1, @2)", contact[0], contact[1], contact[2]);
}
Member
229 Points
662 Posts
Excel sheet contact import
Aug 05, 2013 09:07 AM|ssvikramuk|LINK
I am using SQL compact database table called "Contacts" contains firtname & email. My project requiement is user can upload excel sheet using C# razor code and import into contacts table. In excel sheet contains the same column like firstname & lastname. So basically it reads the data from excel ans stroing into the database.
Guide me how to achive the above.
Thanks
All-Star
194007 Points
28027 Posts
Moderator
Re: Excel sheet contact import
Aug 05, 2013 09:27 AM|Mikesdotnetting|LINK
If your Excel file has an xlsx extension, you need to use the ACE OleDb provider to connect to it and query it. If it has an xls extension, you can use the JET OleDb provider. Note that these providers must be installed on the server. Often, JET is installed as part of the Operating System files, but ACE isn't.
If you can persuade your users to save the file as csv and then upload that instead, your job will be much easier. Then you can use the simple File API that forms part of .NET:
var contacts = File.ReadAllLines(Server.MapPath("~/Path_To_File")); foreach(var line on contacts){ var contact = line.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries); db.Execute("INSERT INTO Contacts (FirstName, LastName, Email) VALUES (@0, @1, @2)", contact[0], contact[1], contact[2]); }