How to Import data from excel sheets to Oracle and Sql server

Posted by Somu475 under Sql Server on 11/22/2012 | Points: 10 | Views : 7539 | Status : [Member] | Replies : 3
I have a excel sheet which contains some data in table format. How can I Import this excel data to Oracle and SQL Database tables.
Plz provide me some code and help me out.

-Somu


Responses

Posted by: Nishant_Mittal on: 11/23/2012 [Member] Starter | Points: 25

Up
0
Down
Refer this link

http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server

You can also try the code given below :

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";using (OleDbConnection connection = new OleDbConnection(xConnStr))

{

OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();

// Create DbDataReader to Data Worksheet

using (DbDataReader dr = command.ExecuteReader())
{

// SQL Server Connection String

string sqlConnectionString =DataAccess_Perf.GetConnectionString() ;
// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{

bulkCopy.DestinationTableName = "ImportedExcelData";
bulkCopy.WriteToServer(dr);

}

}

}


Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jeetu_Choudhary12 on: 11/23/2012 [Member] Starter | Points: 25

Up
0
Down
First of all make a table in database as excel table structure. Now take Excel table in a data table variable.Then Use given below technique.

// Filling Row Data into Database Table Named : Inoutdata
i = 0;
while (ds.Tables["TblInOut"].Rows.Count > i)
{
sql = "insert inoutdata (Employeecode,EmployeeName,Date,FromTime,ToTime,InHours,OutHours)";
sql = sql + "values(@Emp_Cdoe, @Emp_name, @Date, @FromTime, @ToTime, @InHours, @OutHours)";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.AddWithValue("@Emp_Cdoe", ds.Tables["TblInOut"].Rows[i][0].ToString());
cmd.Parameters.AddWithValue("@Emp_name", ds.Tables["TblInOut"].Rows[i][1].ToString());
cmd.Parameters.AddWithValue("@Date", ds.Tables["TblInOut"].Rows[i][2].ToString());
cmd.Parameters.AddWithValue("@FromTime", ds.Tables["TblInOut"].Rows[i][3].ToString());
cmd.Parameters.AddWithValue("@ToTime", ds.Tables["TblInOut"].Rows[i][4].ToString());
cmd.Parameters.AddWithValue("@InHours", ds.Tables["TblInOut"].Rows[i][5].ToString());
cmd.Parameters.AddWithValue("@OutHours", ds.Tables["TblInOut"].Rows[i][6].ToString());
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
i++;
}



Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Eva2012 on: 11/23/2012 [Member] Starter | Points: 25

Up
0
Down
Using below code first to export excel to datatable and then, connect datatable with oracle or sql server:

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\my file\FandH.xlsx");
Worksheet sheet = workbook.Worksheets[0];
this.dataGridView1.DataSource = sheet.ExportDataTable();


details can be seen in :
http://excelcsharp.blog.com/2012/10/29/export-excel-to-datatable-and-import-datatable-to-excel/


Somu475, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response