Click here to Skip to main content
13,295,441 members (35,033 online)
Click here to Skip to main content
Add your own
alternative version


60 bookmarked
Posted 3 Sep 2008

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.


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)
            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)
            '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, _
        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
        txtexcelPath.Text = OpenFileDialog1.FileName
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles Button1.Click
                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,_
                    ProgressBar1.Visible = False
                    MessageBox.Show(&quot;File imported successfully&quot;)
                 End If
        Catch ex As Exception
            ProgressBar1.Visible = False
        End Try
    End Sub
End Class


I hope this helps!


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


About the Author

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

You may also be interested in...


Comments and Discussions

QuestionCan we get your Project File? Pin
Dwi Eko Sapto R30-Nov-16 0:52
memberDwi Eko Sapto R30-Nov-16 0:52 
AnswerRe: Can we get your Project File? Pin
Mohammad Al Hoss2-Jan-17 1:31
memberMohammad Al Hoss2-Jan-17 1:31 
Questionerror in c# Pin
explorerbar12-Nov-14 8:30
memberexplorerbar12-Nov-14 8:30 
AnswerRe: error in c# Pin
Mohammad Al Hoss23-Mar-15 22:29
memberMohammad Al Hoss23-Mar-15 22:29 
QuestionError found " Error = ODBC---Call Failed " Pin
rico_herdian@yahoo.com2-Jul-14 0:39
memberrico_herdian@yahoo.com2-Jul-14 0:39 
QuestionDownload Pin
Mehmet Oya17-Jan-13 22:47
memberMehmet Oya17-Jan-13 22:47 
QuestionPush to SQL Pin
richardbrigzy2-Jul-12 1:07
memberrichardbrigzy2-Jul-12 1:07 
Questioncan you put your project file Pin
mani.kishore.asp.net27-May-12 19:52
membermani.kishore.asp.net27-May-12 19:52 
QuestionNeed to Import Excel csv File Pin
imahaboob14-May-12 6:42
memberimahaboob14-May-12 6:42 
AnswerTo Mohammed - Importing CSV files to SQL Server in C# Pin
Shashidhar Jarung9-Apr-12 5:27
memberShashidhar Jarung9-Apr-12 5:27 
Hi All, I found the solution for importing CSV file using C#, SQL Server, sqlbulkcopy, this code even overcomes limitations of Flat File Source task in SSIS

System.Data.Odbc.OdbcConnection conn;
DataTable dt = new DataTable();
System.Data.Odbc.OdbcDataAdapter da;
string connectionString;
string importFolder;
string fileName;
string strsqlconn = "Data Source=(local);Initial Catalog=<database_name>;Integrated Security=True;";
SqlConnection oSqlConn = new SqlConnection(strsqlconn);
importFolder = @"C:\Test\SQL Server Load\CSVLoadTest"; // path of the csv file
fileName = "TestDataCSV.csv"; // CSV filename
connectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + importFolder + ";"; // ODBC driver for txt, csv files
conn = new System.Data.Odbc.OdbcConnection(connectionString);
da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", conn);
da.Fill(dt); // fill the data to datatable
SqlBulkCopy bulkCopy = new SqlBulkCopy(strsqlconn); // establish connection to bulk copy to SQL Server
bulkCopy.DestinationTableName = "TestDataTable"; // set the destination table
bulkCopy.BulkCopyTimeout = 600; // default 30 seconds
bulkCopy.WriteToServer(dt); // Write from the datatable to the destination.

-- modified 18-Apr-12 9:52am.
GeneralMy vote of 3 Pin
waaqee25-Jul-11 20:18
memberwaaqee25-Jul-11 20:18 
GeneralImport Excel files to SQL Server dynamically Pin
Bhavik170511-Aug-10 19:35
memberBhavik170511-Aug-10 19:35 
GeneralRe: Import Excel files to SQL Server dynamically Pin
longct6-Dec-15 23:54
memberlongct6-Dec-15 23:54 
GeneralRegarding source code Pin
Member 320625016-Nov-09 19:59
memberMember 320625016-Nov-09 19:59 
QuestionODBC Connection error Pin
Nisu Cheriyan31-Mar-09 0:53
memberNisu Cheriyan31-Mar-09 0:53 
GeneralCould not find installable ISAM Pin
Karan Mathur29-Mar-09 6:46
memberKaran Mathur29-Mar-09 6:46 
GeneralNeed help : Error when executing application Pin
bondan_intikom21-Oct-08 21:05
memberbondan_intikom21-Oct-08 21:05 
GeneralRe: Need help : Error when executing application Pin
Mohammad Al Hoss21-Oct-08 22:21
memberMohammad Al Hoss21-Oct-08 22:21 
GeneralRe: Need help : Error when executing application Pin
bondan_intikom21-Oct-08 22:46
memberbondan_intikom21-Oct-08 22:46 
GeneralRe: Need help : Error when executing application Pin
Mohammad Al Hoss22-Oct-08 11:27
memberMohammad Al Hoss22-Oct-08 11:27 
GeneralRe: Need help : Error when executing application Pin
bondan_intikom22-Oct-08 16:30
memberbondan_intikom22-Oct-08 16:30 
GeneralRe: Need help : Error when executing application Pin
Mohammad Al Hoss23-Oct-08 5:15
memberMohammad Al Hoss23-Oct-08 5:15 
QuestionRe: Need help : Error when executing application Pin
bondan_intikom3-Nov-08 20:29
memberbondan_intikom3-Nov-08 20:29 
AnswerRe: Need help : Error when executing application Pin
Mohammad Al Hoss6-Nov-08 22:37
memberMohammad Al Hoss6-Nov-08 22:37 
QuestionTo Mohammad Pin
jilynii11-Sep-08 1:44
memberjilynii11-Sep-08 1:44 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171207.1 | Last Updated 3 Sep 2008
Article Copyright 2008 by Mohammad Al Hoss
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid