Import MS Excel data to SQL Server table using C#

View: 1113    Dowload: 0   Comment: 0   Post by: hanhga   Category: MS Office   Fields: Computers - Technology

7 point/3 review File has been tested

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.

Import MS Excel data to SQL Server table using C#

Import MS Excel data to SQL Server table using C# Posted on 22-03-2014  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: 2.33/10 1113

Comment:

To comment you must be logged in members.

Files with category

  • Document Library - Enable New Document, Edit Document Features

    Document Library - Enable New Document, Edit Document Features

    View: 2089    Download: 0   Comment: 0

    Category: MS Office     Fields: Other

    In this article we can explore the underlying aspects involved in enabling the New Document feature in a Document Library.

  • Generating Excel (XML Spreadsheet) in C#

    Generating Excel (XML Spreadsheet) in C#

    View: 2627    Download: 2   Comment: 0

    Category: MS Office     Fields: Other

    Recently, I was working on a web based report in ASP.NET. The report had to be generated in MS Excel format and the data was too much. To give you an idea, I had to generate more than 300 worksheets and the file size was more than 5 MB. I tried the...

  • Searching - Human Way and Index Way

    Searching - Human Way and Index Way

    View: 2161    Download: 0   Comment: 0

    Category: MS Office     Fields: Other

    It was really an art to optimize the data retrieval from database and data retrieval optimization is part of every developer's life. Indexes are one of the best ways of Optimization that SQL Servers provide. Understanding indexes some time may be...

  • Creating Pivot Tables and Charts Using ActiveX

    Creating Pivot Tables and Charts Using ActiveX

    View: 2791    Download: 0   Comment: 0

    Category: MS Office     Fields: none

    Most of us (developers) use the “Generate Excel” feature in our applications. This allows us to generate an Excel copy on any of our reports. Most of us are also familiar with “Pivot Table” and “Pivot Charts”. This feature from MS Excel allows the...

  • Automating MS Excel Documents

    Automating MS Excel Documents

    View: 2038    Download: 0   Comment: 0

    Category: MS Office     Fields: Other

    I have been working on automating MS Office applications since some time. I have seen that it is very easy to find information on automation of MS Word but it is difficult to find information on automation of PowerPoint or Excel. So I decided to...

  • Embedding PowerPoint presentation player into a WPF application

    Embedding PowerPoint presentation player into a WPF application

    View: 1201    Download: 0   Comment: 0

    Category: MS Office     Fields: Other

    On one occasion I came across an interesting and, I can even say, challenging task of building a customized player of PowerPoint presentations. This task emerged as part of a project which my teammates and I developed at Reliable Systems. Our...

  • Parallel Processing MS Access VBA Application

    Parallel Processing MS Access VBA Application

    View: 1176    Download: 1   Comment: 0

    Category: MS Office     Fields: Other

    I got a challenging assignment to provide alarm capability on top of an existing application, written in VBA Access. This required a few issues to be dealt with and resolved. I will, in this article, describe the steps I took to provide parallel...

  • Microsoft Office XML formats, defective by design?

    Microsoft Office XML formats, defective by design?

    View: 3804    Download: 0   Comment: 0

    Category: MS Office     Fields: Other

    Microsoft is trying to push new file formats that are using Zip and XML. Are those new file formats any good for Office developers? In other words, should anyone feel safe to make direct access to file parts, and start getting free of running...

 
File suggestion for you
File top downloads
Codetitle.net - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.net Develope by Vinagon .Ltd