import excel to sql

Question

plz send some zip format of asp.net to import excel to sql...urgent and fast plz...

Sanjeeb Lenka · Answer

Hi amitesh i am attaching modified code which will definitely work. check the attached code and reply Mark it as answered if it helps you....

Amitesh Verma · Answer

thanks @sanjeeb lanka...ur code works bro...thanx a lot...

Iftikar Hussain · Answer

have you restarted your system and check? Regards Iftikar

Sunny Sharma · Answer

Hi Amitesh The appropriate user account (NETWORK SERVICE by default) needs MODIFY permissions on the folder that the Excel file sits in. Right Click on the folder where your Excel file lies. Goto Security > Edit >Add. Find and Add NETWORK_SERVICE account. Click OK. Assign all the permissions to this account and try to run your application. Feedback awaited. Hope it helps.

Sanjeeb Lenka · Answer

Did you changed the sheet name and check ..... which browser you are using...

Amitesh Verma · Answer

i dnt knw whats pblm with my system

Sanjeeb Lenka · Answer

yes it working perfectly after changing the Exel07 to sheet1

Amitesh Verma · Answer

is my code working on ur system....i atched excel file in it....check n reply

Sanjeeb Lenka · Answer

in your code i checked that you are providing the exel file name. but it actually required the Sheet name check this line of code OleDbCommand cmd = new OleDbCommand(Select [CustomerID][City][Country][PostalCode] from [Sheet1$]   excelConnection); modify your code and reply. if it help you mark it as answered.

Amitesh Verma · Answer

its my file...

Sanjeeb Lenka · Answer

attach your code. i will check

Amitesh Verma · Answer

same error....i dnt knw what has happend....almost tried tried eveythng to solve it...bt its not running

Sanjeeb Lenka · Answer

hi i checked your code and modified. just check the attached file and run it. 1st open the .mdf file. 2nd match the table column name with exel column name.

Muralidharan Deenathayalan · Answer

Make sure you've closed excel and the excel file is not opened.

Iftikar Hussain · Answer

can you just restart your system and check again? Regards Iftikar

Amitesh Verma · Answer

String strConnection = @Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True; //file upload path string path = fileuploadExcel.PostedFile.FileName; //Create connection string to Excel work book string excelConnectionString = @Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + path + ;Extended Properties=Excel 12.0;Persist Security Info=False; //Create Connection to Excel work book OleDbConnection excelConnection =new OleDbConnection(excelConnectionString); //Create OleDbCommand to fetch data from Excel OleDbCommand cmd = new OleDbCommand(Select [CustomerID][City][Country][PostalCode] from [Excel07$]excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); //Give your Destination table name sqlBulk.DestinationTableName = excelimport; sqlBulk.WriteToServer(dReader); excelConnection.Close(); in this code..its showing.. The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user or you need permission to view and write its data. .. but this code doesnot have anyfolder to upload excel file in it...

Sanjeeb Lenka · Answer

hi refer to this link and follow these steps http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html reply

Amitesh Verma · Answer

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user or you need permission to view and write its data. 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: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user or you need permission to view and write its data. Source Error: Line 26: //Create OleDbCommand to fetch data from Excel Line 27: OleDbCommand cmd = new OleDbCommand(Select [CustomerID][City][Country][PostalCode] from [Excel07$] excelConnection); Line 28: excelConnection.Open(); Line 29: OleDbDataReader dReader; Line 30: dReader = cmd.ExecuteReader(); this is the error sir...

Iftikar Hussain · Answer

what is the error? Regards Iftikar

Sanjeeb Lenka · Answer

hi i modified your code just check the attachment.

Amitesh Verma · Answer

there is some error sir

Iftikar Hussain · Answer

i have modified your code behind try like this using System; using System.Data.OleDb; using System.Data.SqlClient; namespace QuestionWebApplication2 { public partial class newimport : System.Web.UI.Page { protected void Page_Load(object sender EventArgs e) { } protected void btnSend_Click(object sender EventArgs e) { String strConnection = @Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True; //file upload path string path = fileuploadExcel.PostedFile.FileName; //Create connection string to Excel work book string excelConnectionString = @Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + path + ;Extended Properties=Excel 12.0;Persist Security Info=False; //Create Connection to Excel work book OleDbConnection excelConnection =new OleDbConnection(excelConnectionString); //Create OleDbCommand to fetch data from Excel OleDbCommand cmd = new OleDbCommand(Select [CustomerID][City][Country][PostalCode] from [Excel07$]excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); //Give your Destination table name sqlBulk.DestinationTableName = excelimport; sqlBulk.WriteToServer(dReader); excelConnection.Close(); } } } Regards Iftikar

Amitesh Verma · Answer

sir i am attaching zip..kindly check it..it contains the excel file too

Iftikar Hussain · Answer

what compilation error you are getting? can you share it? Regards Iftikar

Amitesh Verma · Answer

its showing compliation error sir....

Sanjeeb Lenka · Answer

hi refer to this link http://csharpdotnetfreak.blogspot.com/2009/04/export-excel-data-sqlbulkcopy-aspnet.html

Iftikar Hussain · Answer

Hi Please refer this link http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html Regards Iftikar