Import Data from Excel to SQL using C#


First need to add the reference “Microsoft.office.Interop.Excel”. You can add it from .Net Components. Add open Dialogbox ,Textbox and two command button on form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
namespace ExcelTOSQLDataBase
{
    public partial class EXCELTOSQL : Form
    {
        publicEXCELTOSQL()
        {
            InitializeComponent();
        }
        privatevoid button1_Click(objectsender, EventArgs e)
        {
            if(openFileDialog1.ShowDialog() == DialogResult.OK)
                textBox1.Text = openFileDialog1.FileName;
        }
        privatevoid button2_Click(objectsender, EventArgs e)
        {
        
        //Table Name
        stringEXCELSQLTable = “Employees”;
        stringExcelData = “Select * from [Sheet1$]”;
        //SQL Server Connection String
 string SQLConnectionString = “Data Source=ComputerName;Initial  
 Catalog=DataBaseName;Integrated Security=True”;
        //Excel File Path 
        stringExcelFilePath=textBox1.Text;
          try
            {
string  ConnectionString =@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + ExcelFilePath  + “;Extended Properties=” + “”Excel 8.0;HDR=YES;””;
       
OleDbConnection OleDbCnn = new OleDbConnection(ConnectionString);
           
            OleDbCnn.Open();
           
            SqlBulkCopybulkCopy = new SqlBulkCopy(SQLConnectionString);
     
            bulkCopy.DestinationTableName = EXCELSQLTable;
       
       
using (OleDbDataAdapterX = new OleDbDataAdapter(ExcelData, OleDbCnn))

     {

             

            DataTable SQLT = newDataTable();

          

            X.Fill(SQLT);

            bulkCopy.WriteToServer(SQLT);

             

     }
          

            OleDbCnn.Close();
             MessageBox.Show(“Data Imported Successfully”);
    }
            
    catch (Exception ee)
    {
         
        label2.Text = ee.Message;
     }
 
     }
   }
 }
 Design and Output

 Results:

One Comment on “Import Data from Excel to SQL using C#”

Leave a Reply

Your email address will not be published. Required fields are marked *