Just Code‎ > ‎

C# - Read from Excel and insert to DB

posted Feb 11, 2011, 1:55 AM by Peter Henell   [ updated Feb 11, 2011, 2:05 AM ]
Column names must match in Excel and table

HDR=Yes because excel file has headers in its first line.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace LoadToDBFromExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            InsertToDbFromExcel("Customers");
            InsertToDbFromExcel("Employees");
            InsertToDbFromExcel("Orders");
        }

        private static void InsertToDbFromExcel(string tableName)
        {
            DataSet ds = new DataSet();

            using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Datafolder\\TestData.xls;Extended Properties=\"EXCEL 12.0;HDR=YES\""))
            {
                con.Open();

                using (OleDbCommand cmd = new OleDbCommand(string.Format("select * from [{0}$]", tableName), con))
                {
                    OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                    ad.Fill(ds);

                    con.Close();
                }
            }


            using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=CustomerDB;Integrated Security=True"))
            {

                StringBuilder sb = new StringBuilder();

                sb.AppendFormat(@"
SET IDENTITY_INSERT {0} ON;

INSERT {0}(", tableName);


                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    sb.Append(string.Format("{0}", ds.Tables[0].Columns[i].ColumnName));
                    if (i < ds.Tables[0].Columns.Count - 1)
                        sb.AppendLine(", ");
                }

                sb.Append(")");
                sb.Append(" VALUES(");

                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    sb.Append(string.Format("@{0}", ds.Tables[0].Columns[i].ColumnName));
                    if (i < ds.Tables[0].Columns.Count - 1)
                        sb.AppendLine(", ");
                }

                sb.Append(")");

                using (SqlCommand cmd = new SqlCommand(sb.ToString(), con))
                {
                    con.Open();
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        try
                        {
                            cmd.Parameters.Clear();
                            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                            {

                                cmd.Parameters.AddWithValue(string.Format("@{0}", ds.Tables[0].Columns[i].ColumnName), row[i]);
                            }

                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
            }
        }
    }
}
Comments