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

Import Excel files to SQL Server dynamically

, 3 Sep 2008
Rate this:
Please Sign up or sign in to vote.
This article shows how to import Excel files to SQL Server dynamically.

Introduction

This article will show you how to import an Excel file to SQL Server, dynamically, without the need to open the Import wizard from SQL Server. This could be of great help if you have a lot of Excel files to import.

Using the code

First, create the Datamanage class that holds the import code. The “excelCommand” will create a new table in the SQL Server database, taking into consideration the names and data types of the Excel file fields.

Public Class Datamanage
    ''' <span class="code-SummaryComment"><summary /></span>
    ''' Imports the selected Excel file to SQL Server 
    ''' <span class="code-SummaryComment"></summary /></span>
   Public Sub importToServer(ByVal ExcelPath As String, _
              ByVal ServerName As String, _
              ByVal DBName As String, ByVal UserName As String, _
              ByVal Password As String, ByVal InsertedTableName As String)
        Try
            Dim ExceCon As String = _
                &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot; & _
                ExcelPath & &quot;; Extended Properties=Excel 8.0&quot;
            Dim excelConnection As System.Data.OleDb.OleDbConnection = _
                New System.Data.OleDb.OleDbConnection(ExceCon)
            excelConnection.Open()
            'Dim OleStr As String = &quot;SELECT * INTO [ODBC; Driver={SQL Server};&quot; & _
                 &quot;Server=Hoss;Database=Excel_Test;Uid=sa;Pwd=sa2008; ].[myTable]   
            FROM [Sheet1$];&quot;
            Dim OleStr As String = &quot;SELECT * INTO [ODBC; Driver={SQL Server};Server=&quot; _
                & ServerName & &quot;;Database=&quot; & DBName & &quot;;Uid=&quot; & _
                UserName & &quot;;Pwd=&quot; & Password & &quot;; ].[&quot; & _
                InsertedTableName & &quot;]   FROM [Sheet1$];&quot;
            Dim excelCommand As New System.Data.OleDb.OleDbCommand(OleStr, _
                                                      excelConnection)
            excelCommand.ExecuteNonQuery()
            excelConnection.Close()
        Catch ex As Exception
            Throw New Exception(&quot;Error: &quot; & ex.Message)
        End Try
    End Sub
End Class

Now, the main form:

Imports System.IO
Public Class ImportExcel
    Dim dm As New Datamanage
    Private Sub Button2_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.ShowDialog()
        txtexcelPath.Text = OpenFileDialog1.FileName
                     
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles Button1.Click
        Try
                Dim ExcelPath As String = txtexcelPath.Text.ToLower()
                Dim Ext As String = _
                    ExcelPath.Substring(ExcelPath.LastIndexOf(&quot;.&quot;) + 1)
                If File.Exists(ExcelPath) AndAlso (Ext.Equals(&quot;xls&quot;) _
                        Or Ext.Equals(&quot;xlsx&quot;)) Then
                    dm.importToServer(OpenFileDialog1.FileName, txtServer.Text,_
                        txtDbName.Text, txtusername.Text, txtpassword.Text,_
                        txtInsertedTableName.Text)
                    ProgressBar1.Visible = False
                    MessageBox.Show(&quot;File imported successfully&quot;)
                 End If
        Catch ex As Exception
            ProgressBar1.Visible = False
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

Import_Excel.jpg

I hope this helps!

License

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

Share

About the Author

Mohammad Al Hoss
Software Developer CME Offshore
Lebanon Lebanon
No Biography provided

Comments and Discussions

 
QuestionError found " Error = ODBC---Call Failed " Pinmemberrico_herdian@yahoo.com1-Jul-14 23:39 
QuestionDownload PinmemberMehmet Oya17-Jan-13 21:47 
QuestionPush to SQL Pinmemberrichardbrigzy2-Jul-12 0:07 
Questioncan you put your project file Pinmembermani.kishore.asp.net27-May-12 18:52 
QuestionNeed to Import Excel csv File Pinmemberimahaboob14-May-12 5:42 
AnswerTo Mohammed - Importing CSV files to SQL Server in C# [modified] PinmemberShashidhar Jarung9-Apr-12 4:27 
GeneralMy vote of 3 Pinmemberwaaqee25-Jul-11 19:18 
GeneralImport Excel files to SQL Server dynamically PinmemberBhavik170511-Aug-10 18:35 
GeneralRegarding source code PinmemberMember 320625016-Nov-09 18:59 
QuestionODBC Connection error PinmemberNisu Cheriyan30-Mar-09 23:53 
GeneralCould not find installable ISAM PinmemberKaran Mathur29-Mar-09 5:46 
GeneralNeed help : Error when executing application Pinmemberbondan_intikom21-Oct-08 20:05 
GeneralRe: Need help : Error when executing application PinmemberMohammad Al Hoss21-Oct-08 21:21 
GeneralRe: Need help : Error when executing application Pinmemberbondan_intikom21-Oct-08 21:46 
GeneralRe: Need help : Error when executing application PinmemberMohammad Al Hoss22-Oct-08 10:27 
GeneralRe: Need help : Error when executing application Pinmemberbondan_intikom22-Oct-08 15:30 
GeneralRe: Need help : Error when executing application PinmemberMohammad Al Hoss23-Oct-08 4:15 
QuestionRe: Need help : Error when executing application Pinmemberbondan_intikom3-Nov-08 19:29 
AnswerRe: Need help : Error when executing application PinmemberMohammad Al Hoss6-Nov-08 21:37 
QuestionTo Mohammad Pinmemberjilynii11-Sep-08 0:44 
AnswerRe: To Mohammad PinmemberMohammad Al Hoss12-Sep-08 1:58 
QuestionRe: To Mohammad [modified] Pinmemberjotapecardas19-Jul-10 22:39 
AnswerRe: To Mohammad [adding code solution] Pinmemberjotapecardas20-Jul-10 3:29 
AnswerRe: To Mohammad PinmemberMohammad Al Hoss20-Jul-10 21:19 
GeneralNeed Help. Pinmemberashu fouzdar4-Sep-08 2:50 

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
Web01 | 2.8.140821.2 | Last Updated 3 Sep 2008
Article Copyright 2008 by Mohammad Al Hoss
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid