Last post Oct 14, 2013 05:41 AM by Fuxiang Zhang - MSFT
Oct 09, 2013 01:12 AM|pratyushapratyusha|LINK
hi,i have one employee details excel sheet,i want to upload it into database and and i want to retrieve the data,not the excel sheet,just i want to see retrieve the seperate employee details?how it is possible?can any one please tell me ?
Oct 09, 2013 01:16 AM|Sekhar Prasad|LINK
Please refer these links
Oct 09, 2013 01:17 AM|jinnahsma|LINK
refer this link
Oct 09, 2013 07:35 AM|pratyushapratyusha|LINK
hi i tried like this,
protected void btnSend_Click(object sender, EventArgs e)
String strConnection = "Data Source=E-3D34;Initial Catalog=Ektha;Integrated Security=True";
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = "user id=sa;password=e@2013;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
SqlConnection excelConnection =new SqlConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
SqlCommand cmd = new SqlCommand("Select [EmployeeID],[EmployeeNAME],[Designation],[Department],[DOJ],[DOR],[Monthly Salary],[Medical],[HRA],[Incentive],[Other All],[OT],[Gross Total],[PF],[ESI],[Advance],[Other Ded],[PT],[TDS-1],[Total Ded],[Net Amt] from [Sheet1$]",excelConnection);
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "PayDescription";
but am getting the error as....can you please help me
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.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Line 31: //Create OleDbCommand to fetch data from Excel
Line 32: SqlCommand cmd = new SqlCommand("Select [EmployeeID],[EmployeeNAME],[Designation],[Department],[DOJ],[DOR],[Monthly Salary],[Medical],[HRA],[Incentive],[Other All],[OT],[Gross Total],[PF],[ESI],[Advance],[Other Ded],[PT],[TDS-1],[Total Ded],[Net Amt] from [Sheet1$]",excelConnection);
Line 33: excelConnection.Open(); Line 34: SqlDataReader dReader;
Line 35: dReader = cmd.ExecuteReader();
Oct 09, 2013 08:52 AM|Kushalamk|LINK
read the data from excel to user defined data table.
Pass this user defined data table as input to Stored procedure that contains insert statement.
Oct 09, 2013 09:20 PM|Paul Linton|LINK
Do you need help with understanding the error message? It seems to spell the problem out fairly clearly.
Oct 10, 2013 12:36 AM|pratyushapratyusha|LINK
yes i need help to understand this,and am nt getting where is the mistake?
Oct 10, 2013 12:40 AM|pratyushapratyusha|LINK
now am getting the error like this....
Oct 10, 2013 12:43 AM|Paul Linton|LINK
Can the file be opened? Is it located on a unc share?
(Whenever you change something and report a different error you need to show the latest version of your code. It is very hard to use psychic powers to know what you have changed)
Oct 11, 2013 12:41 AM|pratyushapratyusha|LINK
actually i don't know what is unc share,and the file is located at desktop
Oct 14, 2013 05:41 AM|Fuxiang Zhang - MSFT|LINK
Thanks for posting this issue to asp.net forum.
According to your description, I see that you want to import data from Microsoft Excel worksheets into Microsoft SQL Server databases.
For this issue, there many methods to complete it. As Kushalamk said, we can read this excel data to a datable then save the dataTable to sql server database.
I'd like to suggest you use a linked server, this what you want from your provided code. To simplify queries, you can configure an Excel workbook as a linked server in SQL Server.
The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,'SELECT * FROM [Customers$]')
There are many other methods with sample in below link. Please refer to:
Hope this helps. Thanks.