Click here to Skip to main content
11,702,890 members (57,672 online)
Click here to Skip to main content

Tagged as

Import MS Excel data to SQL Server table using C#

, 13 Aug 2013 CPOL 103.7K 23
Rate this:
Please Sign up or sign in to vote.
Import MS Excel data to SQL Server table using C#.

Introduction

If you already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow the below steps:

Step 1: Let’s take an example to import data to SQL Server table. I am going to import student information data from an MS Excel sheet to the tStudent SQL table:

Step 2: Now design a tStudent table in SQL Server

CREATE TABLE
(
STUDENT VARCHAR(64),
ROLLNO VARCHAR(16),
COURSE VARCHAR(32),
)

Your MS Excel sheet and SQL table are ready, now it’s time to write C# code to import the Excel sheet into the tStudent table.

Step 3: Add these two namespaces in your class file:

USING SYSTEM.DATA.OLEDB;
USING SYSTEM.DATA.SQLCLIENT;

Step 4: Add below method in your class file, you can call this method from any other class and pass the Excel file path:

public void importdatafromexcel(string excelfilepath)
{
    //declare variables - edit these based on your particular situation
    string ssqltable = "tdatamigrationtable";
    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have
    different
    string myexceldataquery = "select student,rollno,course from [sheet1$]";
    try
    {
        //create our connection strings
        string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
        ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
        string ssqlconnectionstring = "server=mydatabaseservername;user
        id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
        //execute a query to erase any previous data from our destination table
        string sclearsql = "delete from " + ssqltable;
        sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
        sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
        sqlconn.open();
        sqlcmd.executenonquery();
        sqlconn.close();
        //series of commands to bulk copy data from the excel file into our sql table
        oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
        oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
        oledbconn.open();
        oledbdatareader dr = oledbcmd.executereader();
        sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
        bulkcopy.destinationtablename = ssqltable;
        while (dr.read())
        {
            bulkcopy.writetoserver(dr);
        }
     
        oledbconn.close();
    }
    catch (exception ex)
    {
        //handle exception
    }
}

In the above function you have to pass the MS Excel file path as a parameter. If you want to import your data by providing the client access to select the Excel file and import, then you might have to use the ASP.NET File control and upload the Excel file on the server in some temp folder, then use the file path of the uploaded Excel file and pass the path in the above function. Once data import is complete then you can delete the temporary file.

The above method first deletes the existing data from the destination table, then imports the Excel data into the same table.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Musakkhir
Software Developer (Senior)
India India
Musakkhir Sayyed is a Software Engineer working in IT Company. He has been a programmer/Software Developer for past 2 years specializing in .NET/C# development. Currently, he is a Silverlight, WPF/Sharepoint developer specializing in BCS Services.

You may also be interested in...

Comments and Discussions

 
GeneralHello, Import MS Excel data to SQL Server table using C# is very good,I want to download the entire source code project. ok? Pin
Member 118813642-Aug-15 22:35
memberMember 118813642-Aug-15 22:35 
GeneralImport data from MS Excel to SQL Server in C# Pin
y2knbb19-Feb-15 15:48
membery2knbb19-Feb-15 15:48 
Questioncalling the method Pin
Member 1130700612-Dec-14 11:00
memberMember 1130700612-Dec-14 11:00 
AnswerRe: calling the method Pin
Musakkhir30-Jan-15 18:37
memberMusakkhir30-Jan-15 18:37 
QuestionMissing Row Pin
Jonathan Pinney29-Oct-14 10:06
memberJonathan Pinney29-Oct-14 10:06 
AnswerRe: Missing Row Pin
Musakkhir30-Jan-15 18:35
memberMusakkhir30-Jan-15 18:35 
QuestionImport data from Excel to sql server table Pin
Member 1095553714-Sep-14 0:34
memberMember 1095553714-Sep-14 0:34 
AnswerRe: Import data from Excel to sql server table Pin
Musakkhir23-Oct-14 6:25
memberMusakkhir23-Oct-14 6:25 
BugSir I tried to integrate my code with your ode to import excel data to sql server Pin
Omkar Hendre8-Sep-14 20:00
memberOmkar Hendre8-Sep-14 20:00 
GeneralRe: Sir I tried to integrate my code with your ode to import excel data to sql server Pin
Musakkhir17-Sep-14 20:47
memberMusakkhir17-Sep-14 20:47 
GeneralRe: Sir I tried to integrate my code with your ode to import excel data to sql server Pin
Omkar Hendre17-Sep-14 21:14
memberOmkar Hendre17-Sep-14 21:14 
QuestionNice Article Pin
Siva Hyderabad29-Jul-14 19:04
groupSiva Hyderabad29-Jul-14 19:04 
QuestionOracle database Pin
gowtha malar20-May-14 4:10
membergowtha malar20-May-14 4:10 
AnswerRe: Oracle database Pin
Musakkhir23-Oct-14 6:27
memberMusakkhir23-Oct-14 6:27 
AnswerImport MS Excel data to SQL Server table using C# Pin
Member 1080649211-May-14 21:06
memberMember 1080649211-May-14 21:06 
QuestionYou may need to have Excel installed on a server for server-side automation (aspx) Pin
vrsuarez11-Feb-14 15:28
membervrsuarez11-Feb-14 15:28 
SuggestionHow to get the excel sheet name and import into sql server- continue to my previous comment Pin
ravi24june19-Jan-14 2:36
memberravi24june19-Jan-14 2:36 
AnswerImport MS Excel data to SQL Server table using C# Pin
ravi24june19-Jan-14 1:39
memberravi24june19-Jan-14 1:39 
GeneralRe: Import MS Excel data to SQL Server table using C# Pin
Member 106784218-Jun-15 20:25
memberMember 106784218-Jun-15 20:25 
Suggestionexample didn't work for me until I made a small modification. Pin
john_17265-Oct-13 8:22
memberjohn_17265-Oct-13 8:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 13 Aug 2013
Article Copyright 2013 by Musakkhir
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid