Import excel file to SQL Server - SQL Programmers

Steps to import excel data to SQL Server

1. Select the Excel File
2. Check the File is exist or Not
3. Get the name of the Excel Sheet
4. Select the rows from the excel file.
5. Open the SQL Connection
6. Provide the Destination Name
7. And export the data from Excel to SQL Table using Bulk Insert

Example to import excel

Use the following code.

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.Common;
using System.Data.SqlClient;
using System.IO;

// Get the First sheet name
string strFilePath=”C:\Test.xls”;
DataTable dtXLS = new DataTable();
OleDbConnection cnXLS = GetExcelCon(strFilePath);
cnXLS.Open();
System.Data.DataTable dt = null;
// Get the data table containg the schema guid.
dt = cnXLS.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
       return null;
}

// Get the First sheet name
DataRow row = dt.Rows[0];
string SheetName =  row["TABLE_NAME"].ToString();
dt.Dispose();

OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", cnXLS);
OleDbDataAdapter daCSV = new OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
daCSV.Fill(dtXLS);

string sqlConnectionString = "Data Source=user11\\user112008;Initial Catalog=Testing;uid=sri;password=test123;Integrated Security=True";

SqlBulkCopy bulk = new SqlBulkCopy(sqlConnectionString);
bulk.DestinationTableName = "tmpExcelImport";
bulk.WriteToServer(dtXLS);
            
cnXLS.Close();
daCSV = null;

public OleDbConnection GetExcelCon(string strFilePath)
{

excelcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + strFilePath + ";Jet OLEDB:Engine Type=5;" +
            "Extended Properties='Excel 8.0;IMEX=1;'");
return excelcon;
}

In the above code the file Test.xls is selected from C:\ drive. Then created a Datatable dtXLS and OLEDB Connection dtXLS. The GetExcelCon() function will return the Excel Connection string with the File name.  Once the connection is established then using GetOleDbSchemaTable created data table dt. It will have all the sheets.

Get Excel sheet name

Using the following code we can get the first sheet name from the datatable dt.

DataRow row = dt.Rows[0];
string SheetName =  row["TABLE_NAME"].ToString();

Collect data from excel

We have to collect all the data from the Excel Sheet using the OLEDBCommand object and created dtCSV datatable

OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", cnCSV);
OleDbDataAdapter daCSV = new OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
daCSV.Fill(dtCSV);

Establish SQL connection

we have to Established the SQL Connection for the SQLBulkCopy Object and exported all the data into the specified destination table using the WriteToServer() method.

string sqlConnectionString = "Data Source=user11\\user112008;Initial Catalog=idacs;uid=sa;password=acsadmin;Integrated Security=True";
SqlBulkCopy bulk = new SqlBulkCopy(sqlConnectionString);
bulk.DestinationTableName = "tmpExcelImport";
bulk.WriteToServer(dtCSV);