Excel Import to SQL Server using OleDB-SQL Utility

Introduction

Microsoft Excel file can be used as OLE DB data source.
Microsoft .NET Framework has rich capabilities for working with OLE DB data sources and SQL Server.

So, we can make a simple command line OleDB-SQL utility to import Excel data to SQL Server.

The core code of the utility:

bulkCopy.DestinationTableName = sqlTableName;
bulkCopy.WriteToServer(oleDbDataReader);

This code inserts all the data from the Excel data reader to the destination table at SQL Server.

After this operation, you can run a stored procedure using sqlcmd utility to process the data.

For testing Excel queries, the utility has a CSV export mode.

You can download the binary executable of the OleDB-SQL utility, the source code, and example files.

Table of Contents

Command Line Utility for Excel Data Import to SQL Server

You can skip these comments and just download and use the OleDB-SQL utility.

Function OleDbToSqlServer

The heart of the utility is OleDbToSqlServer function.

The function reads data from OLE DB data source using connection string oleDbConnectionString and SQL statement oleDbSQL and writes the data to SQL Server table sqlTableName using connection string sqlConnectionString.

static void OleDbToSqlServer(string oleDbConnectionString, string oleDbSQL,
    string sqlConnectionString, string sqlTableName)
{

    oleDbConnectionString = ExpandConnectionStringFileName(oleDbConnectionString);

    OleDbConnection oleDbConnection = new OleDbConnection(oleDbConnectionString);
    try
    {
        oleDbConnection.Open();
        OleDbCommand command = new OleDbCommand(oleDbSQL, oleDbConnection);
        try
        {
            OleDbDataReader reader = command.ExecuteReader();
            try
            {
                SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
                try
                {
                    sqlConnection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
                    {
                        bulkCopy.DestinationTableName = sqlTableName;
                        bulkCopy.WriteToServer(reader);
                    }
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            command.Dispose();
        }
    }
    finally
    {
        oleDbConnection.Close();
    }
}

The only standard .NET Framework code is used except the function ExpandConnectionStringFileName.

Function ExpandConnectionStringFileName

This function expands a filename of the data source file of the OLE DB connection string.

Due to this feature, you can use a relative path to the data source.

static string ExpandConnectionStringFileName(string connectionString)
{
    const string term = "Data Source=";

    int index = connectionString.IndexOf(term);
    if (index < 0)
        return connectionString;

    string dataSource = connectionString.Substring(index + term.Length,
        connectionString.IndexOf(";", index + term.Length) - index - term.Length);

    if (!File.Exists(dataSource))
        return connectionString;

    string expandedDataSource = Path.GetFullPath(dataSource);

    connectionString.Replace(term + dataSource, term + expandedDataSource);

    return connectionString;

}

Function OleDbToCSV

The function OleDbToCSV reads data from OLE DB data source using connection string oleDbConnectionString and SQL statement oleDbSQL and writes the CSV data to the standard output using delimiter.

static void OleDbToCSV(string oleDbConnectionString, string oleDbSQL,
    string delimiter)
{
    oleDbConnectionString = ExpandConnectionStringFileName(oleDbConnectionString);

    OleDbConnection oleDbConnection = new OleDbConnection(oleDbConnectionString);
    try
    {
        oleDbConnection.Open();
        OleDbCommand command = new OleDbCommand(oleDbSQL, oleDbConnection);
        try
        {
            OleDbDataReader reader = command.ExecuteReader();
            try
            {
                Console.Write("{0}", reader.GetName(0));
                for (int i = 1; i < reader.FieldCount; i++)
                {
                    Console.Write("{1}{0}", reader.GetName(i), delimiter);
                }
                Console.WriteLine();

                while (reader.Read())
                {
                    Console.Write("{0}", reader.GetValue(0));
                    for (int i = 1; i < reader.FieldCount; i++)
                    {
                        Console.Write("{1}{0}",
                            FormatValueForCSV(reader.GetValue(i), delimiter),
                            delimiter);
                    }
                    Console.WriteLine();
                }
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            command.Dispose();
        }
    }
    finally
    {
        oleDbConnection.Close();
    }
}

The only standard .NET Framework code is used except the function FormatValueForCSV.

Function FormatValueForCSV

The function quotes the value which contains qoutes or delimiters.

static Object FormatValueForCSV(Object value, string delimiter)
{
    switch (value.GetType().Name)
    {
        case "String":
            if (value.ToString().IndexOf('"') >= 0)
            {
                return '"' + value.ToString().Replace(@"""", @"""""") + '"';
            }
            else if (value.ToString().IndexOf(delimiter) >= 0)
            {
                return '"' + value.ToString() + '"';
            }
            else
                return value.ToString();
        default:
            return value;
    }

}

Function Main

The main function reads parameters and runs the required function.

static void Main(string[] args)
{
    try
    {
        if (args.Length == 0)
        {
            Help();
            return;
        }

        switch (args[0].ToLower())
        {
            case "sql":
                if (args.Length != 5)
                {
                    Help();
                    break;
                }
                OleDbToSqlServer(args[1], args[2], args[3], args[4]);
                break;
            case "csv":
                if (args.Length != 4)
                {
                    Help();
                    break;
                }
                OleDbToCSV(args[1], args[2], args[3]);
                break;
            default:
                Help();
                break;
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
        Environment.ExitCode = 2;
    }
}

You can find the function Help in the source code.

The Building of the Solution

We should build two versions: for x86 and x64 platforms.

The platform of the utility to use should be identical to the installed Microsoft Office platform.

To build the oledb-sql utility you can use the following commands:

@echo off

csc /target:exe /platform:x86 /out:oledb-sql.exe oledb-sql.cs

csc /target:exe /platform:x64 /out:oledb-sql-64.exe oledb-sql.cs

Command File for Excel Data Import to SQL Server

There are working command files for Excel Data Imports to SQL Server or CSV files below.

You can modify it for your tasks freely.

You should uncomment the string rem set exe=oledb-sql-64.exe if Microsoft Office x64 is installed on the machine.

Command File for Excel Data Import to SQL Server

The file runs :RUN_ONE part for each configuration file like oledb-sql-task-*.txt.

The format of the configuration file is described below. Each file should contain the required parameters for oledb-sql.exe utility. You can uncomment echo command to see the value of a variable.

@echo off

rem Change to oledb-sql-64.exe if Microsoft Office x64 installed
set exe=oledb-sql.exe
rem set exe=oledb-sql-64.exe

for %%i in (oledb-sql-task-*.txt) do call :RUN_ONE %%i
goto END

:RUN_ONE

for /F "eol=; tokens=1* delims===" %%i in (%1) do set %%i=%%j

rem echo %OleDbConString%
rem echo %OleDbSQL%
rem echo %SqlConString%
rem echo %SqlTable%
rem echo %CsvFile%

%exe% sql "%OleDbConString%" "%OleDbSQL%" "%SqlConString%" "%SqlTable%"

:END

Command File for Excel Data Import to CSV Files

The file runs :RUN_ONE part for each configuration file like oledb-sql-task-*.txt.

The format of the configuration file is described below. Each file should contain the required parameters for oledb-sql.exe utility. You can uncomment echo command to see the value of a variable.

@echo off

rem Change to oledb-sql-64.exe if Microsoft Office x64 installed
set exe=oledb-sql.exe
rem set exe=oledb-sql-64.exe

for %%i in (oledb-sql-task-*.txt) do call :RUN_ONE %%i
goto END

:RUN_ONE

for /F "eol=; tokens=1* delims===" %%i in (%1) do set %%i=%%j

rem echo %OleDbConString%
rem echo %OleDbSQL%
rem echo %SqlConString%
rem echo %SqlTable%
rem echo %CsvFile%

%exe% csv "%OleDbConString%" "%OleDbSQL%" , > %CsvFile%

:END

Excel Data Import Configuration Files

Each Excel data import configuration file contains values for one import operation:

  • The OLE DB Provider connection string for the Excel data query.
  • The SQL Statement for the Excel data query.
  • The SQL Server connection string (for SQL mode)
  • The target table name (for SQL mode).
  • The target CSV file name (for CSV mode).

The configuration file example for Microsoft.Jet.OLEDB.4.0 provider:

OleDbConString==Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.xls;Extended Properties='Excel 8.0';

OleDbSQL==SELECT NULL AS ImportID, * FROM [Sheet1$]

SqlConString==Data Source=.;Initial Catalog=AzureDemo50;User ID=excel_user;Password=ExSQL_#02;

SqlTable==dbo04.ExcelTestImport

CsvFile==ExcelTestImport1.csv

The configuration file example for Microsoft.ACE.OLEDB.16.0 provider:

OleDbConString==Provider=Microsoft.ACE.OLEDB.16.0;Data Source=test.xlsm;Extended Properties='Excel 12.0';

OleDbSQL==SELECT NULL AS ImportID, * FROM [Sheet1$]

SqlConString==Data Source=.;Initial Catalog=AzureDemo50;Integrated Security=SSPI;

SqlTable==dbo04.ExcelTestImport

CsvFile==ExcelTestImport2.csv

See details about OLE DB providers in the article Excel Import to SQL Server using Distributed Queries.

Conclusion

Using the described technique, you can import Excel data to SQL Server easily.

Just add a new configuration file for each pair of Excel and SQL Server table.

Download

Downloads
Importing Excel to SQL Server Using OleDB-SQL Utility
Includes binaries, source codes, and examples of the OleDB-SQL utility
Version: 1.11 | 06/01/2024 | 0.1MB | Article
 Download

See Also