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 : 10930 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

Latest Posts