Forums » .NET » .NET »

How to insert excelsheet to sql tables?


Posted Date: 06 Jul 2013      Posted By:: Nitin Giri     Member Level: Bronze    Member Rank: 0     Points: 3   Responses: 2



Hi,
I am new in .net. Recently I got a project in which I was provided with tow excel files. I have to make a entry form through which on selecting an excel file it should be automatically converted to sql tabel
there are many related topics but they ask to create the sql table with same column names as in excel file
I need this automatically in C# Windows app form.





Are you looking for information on excel sheet insertion into sql tables? Want to know how to insert excel sheet to sql tables? Read this thread to learn more about excel sheet insertion functionality with examples.

Responses

#722181    Author: Ravindran      Member Level: Diamond      Member Rank: 3     Date: 07/Jul/2013   Rating: 2 out of 52 out of 5     Points: 4

Hi,

Follow my example with detail code snippet. I was worked based on your requirement and create dynamic table based on excel data etc.

Step1:

Create one form control and get excel sheet from user.

using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;
using System.IO;
namespace Slicing
{
public partial class frmDataSet : Form
{
DAL obj = new DAL();
public frmDataSet()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fDialog = new OpenFileDialog();
fDialog.Title = "Select file to be upload";
fDialog.Filter = "Excel_2003 Files|*.xls|Excel_2007 Files|*.xlsx";
if (fDialog.ShowDialog() == DialogResult.OK)
{
txtPath.Text = fDialog.FileName.ToString();
}
}

private void btnView_Click(object sender, EventArgs e)
{
//Assign Excel path here
string Exfilepath = txtPath.Text;
OleDbConnection MyConnection;


FileInfo fi = new FileInfo(txtPath.Text);

//DbTablename is table name to maintain whole application using this variable we can get table name any form
DAL.DbTablename = fi.Name.Replace(fi.Extension, "") + "_" + DateTime.Now.ToString("ddMMyyyy_HHmmss");

MessageBox.Show(DAL.DbTablename);

//Assign conncetion string
MyConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Exfilepath + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");


DataSet DtSet = null;
DataTable dt = new DataTable();
OleDbDataAdapter MyCommand = null;
MyCommand = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new DataSet();
MyCommand.Fill(DtSet);
dt = DtSet.Tables[0];

//Create table
obj.CreateTable(dt, DAL.DbTablename);

//Insert Data Table data to sql server table
obj.ExportToSQLServer(dt, DAL.DbTablename);

//Read data from sql server and bind in the data grid view
DG.DataSource = obj.ExecuteData("select * from " + DAL.DbTablename);
txtPath.Text = "";
}
}
}


Step2:

Write below code in your class file DAL.cs

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Slicing
{
class DAL
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.AppSettings["constr"].ToString());
SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.AppSettings["constr"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
DataSet ds = new DataSet();

public static string DbTablename;

//Read data from database
public DataTable ReadData(string query)
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand(query, sqlcon);
da = new SqlDataAdapter(sqlcmd);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlcon.Close();
}
return dt;
}
//below method create new dynamic table based on the excel columns
public void CreateTable(DataTable pDt, string tblname)
{
string query = string.Empty;
try
{
query = "CREATE TABLE " + " " + tblname + " (";
for (int i = 0; i <= pDt.Columns.Count - 1; i++)
{
query += "[" + pDt.Columns[i].ColumnName.ToUpper() + "] NVARCHAR(MAX), ";
}
query = query.Substring(0,query.Length-2) + ")";
ExecuteCommand(query);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlcon.Close();
}
}


public void ExportToSQLServer(DataTable pDt, string tblname)
{
try
{
//Assign destination sql server target table name below
sqlBulk.DestinationTableName = tblname;

//Write to server table
sqlBulk.WriteToServer(pDt);
}
catch (Exception ex)
{
throw ex;
}
}
}
}


Regards
N.Ravindran
Your Hard work never fails


 
#722239    Author: Anil Kumar Pandey      Member Level: Diamond      Member Rank: 1     Date: 08/Jul/2013   Rating: 2 out of 52 out of 5     Points: 1

You can also make use of the Import and Export wizard of the sql server.

Use the wizard to import the values present inside a excel sheet in a sql table.

Thanks & Regards
Anil Kumar Pandey
Microsoft MVP, DNS MVM


 
Post Reply

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.



Return to Discussion Forum
Start new thread

Subscribe to Subscribers
Awards & Gifts
Talk to Webmaster Tony John

Online Members

More...
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India