Click here to Skip to main content
Click here to Skip to main content

Tagged as

Simple Excel 2007 Data Loader to SQL 2008 Server

, 9 Dec 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Simple Excel data loader to SQL Server using SQlBulkCopy

Introduction

This is a very simple Excel 2007 data loader to SQL 2008 database. I am using SqlBulkCopy class to make data load fast. (10K record loaded in less than 3s).

One catch: You will need existing table in SQL DB with matching columns. I skipped dynamic table creation to keep the code simple.

Using the Code

This is the most basic version, without error checking to make the code clear. Below is the completed code behind the form.

Button 1 - Opens the Excel file.

Button 2 - Uploads data to SQL Server.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;

namespace SimpleExcelLoader
{
    public partial class Form1 : Form
    {
        string filename="";
        public Form1()
        {
            InitializeComponent();
        }
      private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "Excel 2007 files 
			(*.xlsx)|*.xlsx|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
               filename = openFileDialog1.FileName;
               textBox1.Text = filename;
               button2.Enabled = true;
               toolStripStatusLabel1.Text = "File Selected. 
				Enter DB Info and click Upload Data";
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            string server = textBox2.Text;
            string db = textBox3.Text;
            string user = textBox4.Text;
            string pass = textBox5.Text;
            string tableName=textBox6.Text;
            string serverConnectionString="Server="+server+";
			Database="+db+";Uid="+user+";Pwd="+pass+";";
            
            
            //step 1
            // Load Excel data into DataTable
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";
            string strSQL = "SELECT * FROM [Sheet1$]";
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.
            OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
            // create data table
            DataTable dTable = new DataTable();
            //fill table with Excel data
            dataAdapter.Fill(dTable);
           
            //step 2 
            //connect to server
            
            using (SqlConnection destinationConnection =
                   new SqlConnection(serverConnectionString))
                {                   
                    destinationConnection.Open();   
             
                    using (SqlBulkCopy bulkCopy =
                            new SqlBulkCopy(destinationConnection))
                    {
                        //Destination Table must match columns in Excel sheet
                        bulkCopy.DestinationTableName = tableName;
                                              
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(dTable);
                            toolStripStatusLabel1.Text="Data Uploaded";
                        }                                                  
                        catch (Exception ex)
                        {                           
                            toolStripStatusLabel1.Text=ex.Message;
                        }
                       
                     destinationConnection.Close(); }}
    
                // dispose used objects
                dTable.Dispose();
                dataAdapter.Dispose();
                dbCommand.Dispose();
                excelConnection.Close();
                excelConnection.Dispose();
            }}}

Points of Interest

The code was tested with Excel 2007 and SQL Server 2008.

History

  • 9th December, 2008: Initial post

License

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

Share

About the Author

Mia Wallace
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 Pinmemberphoohtoo26-Dec-12 22:59 
QuestionExcel to sql conversion Pinmembernetdevny4-Dec-12 10:32 
Generalxlsx 2 sql PinmemberBernie Su16-Jun-09 7:05 
GeneralSSIS... PinmemberTarabanko Yury9-Dec-08 14:26 
GeneralRe: SSIS... PinmemberInga Bemman9-Dec-08 23:53 
GeneralRe: SSIS... PinmemberTarabanko Yury10-Dec-08 3:54 
GeneralWell... PinmemberJayDial9-Dec-08 14:07 
GeneralRe: Well... PinmemberInga Bemman9-Dec-08 23:49 

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 | Mobile
Web03 | 2.8.141015.1 | Last Updated 9 Dec 2008
Article Copyright 2008 by Mia Wallace
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid