How to load Excel's data into SQL Server in different ways

In this article, I demonstrate the most effective ways that I know of, to load data into a Microsoft SQL Server. If you know of other ways that you feel are more efficient, please share your ideas in the comments section below. Note that some customization may still be needed to cater for your own project.

---

Preparation

Complete the following two steps to follow the demonstrations:

Step 1. Create the table structure for Table: Sheet1$ by executing the SQL command below:

CREATE TABLE [dbo].[Sheet1$](
    [Dates] [datetime] NULL,
    [Type] [varchar](1) NULL,
    [Code] [varchar](20) NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
) ON [PRIMARY]
GO

Step 2. Download this source.xlsx source file as we will use it as the data to be imported.

---

How to load Excel's data into SQL Server

1 )   Using SQL Server Management Studio - Import Wizard
2 )   Using SQL Server Management Studio - OPENROWSET command
3 )   Using SSIS in Visual Studio
4 )   Using PowerShell
5 )   Using Node.js
6 )   Using Python
7 )   Using SQL Server Management Studio - sp_execute_external_script command
8 )   Using .NET (C#)
9 )   Using PHP
10 ) Using Java
11 ) Using Golang

* The above list will be updated in due course.

1 ) Using SQL Server Management Studio - Import Data wizard

Select a schema, and then right-click and select Tasks > Import Data ...



By following the wizard, select the Data Source as Microsoft Excel, and then the source file using the Browse button.



Note: Selecting the correct Excel version is critical.

If you hit a "provider is not registered" error, try installing the relevant drivers accordingly.



Microsoft.ACE.OLEDB.15 driver - Download and install Microsoft Access 2013 Runtime



Microsoft.ACE.OLEDB.16 driver - Download and install Microsoft Access Database Engine 2016 Redistributable

* Make sure you select the correct (32-bit vs 64 bit) program for your installation.

After that, we specify the Destination database to import the data into.



When necessary, we can change the Destination Table's Name, edit the Field Mapping and preview the data before we continue.



Check the appropriate a vailable option:




Finally, click Next and Finish the wizard.

You should be able to see the end result like this:



Then you can examine the data that was imported into table: Sheet1$ in SQL Server.


2 ) Using SQL Server Management Studio - OPENROWSET Command

To import the data, we can try the following SQL scripts:

--This is for testing purposes, so clean up the table
Truncate Table Sheet1$;

--Import the data to target table
Insert into Sheet1$
SELECT * FROM OPENROWSET
(
    'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\yourPath\source.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]'
) a

Note: You may encounter the following error when you execute the OPENROWSET command.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

To resolve the issue, you would need to execute the following SQL commands:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , N'AllowInProcess' , 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , N'DynamicParameters' , 1
GO

Try executing the OPENROWSET SQL command again and you should able to see data inserted successfully.


Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

3 ) Using SSIS in Visual Studio

In order to create a SSIS project in Visual Studio, you would first need to download and install the SQL Server Data Tools (SSDT) separately.

Download SQL Server Data Tools (SSDT) for Visual Studio

Once installed and available, you should be able to see a new tree note called Business Intelligence, and then select Integrated Services Project to continue.



To make a start, we can drag and drop an Execute SQL Task into the Control Flow.

Then we need to configure the Connection as well as the SQLStatement to be executed, which is to clean up the target table with SQL below:

 Truncate Table Sheet1$;

After this, drag and drop a Data Flow Task underneath the Execute SQL Task that we have created.

Once this is done, select the Execute SQL Task and a green arrow will appear.



Drag the green arrow to the top of Data Flow Task and link them together.



Now, double click the Data Flow Task, and it will bring us to the Data Flow tab.

The Data Flow is the place where we select, transform the data source and then load it into a data destination.

First, we need to select and drag the Excel Source from SSIS Toolbox > Other Sources into the Data Flow workspace.

Double click the Excel Source to edit the Connection Manager, Columns Mapping, Error Handling.



Once this is done, then we need to select and drag the SQL Destination from SSIS Toolbox > Other Destinations into the Data Flow workspace.



But by doing so, you may encounter the following error message:

The column "Dates" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.

To eliminate this error, try changing the SQL Destination to OLE DB Destination instead.



In some of the scenarios, you may also encounter a Data Conversion Error, so you may also include the Data Conversion component to convert your data when necessary.



Try executing the Package by clicking the Start (or press F5) button.

You should able to see that the Package is being executed successfully.



Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

4 ) Using PowerShell

PowerShell is a very powerful tool developed in .NET and can facilitate a lot of tasks effectively.

In order to import Excel directly into SQL Server, we will need to install 2 additional libraries

• Import-Excel
• dbatools

We can install these two libraries from the command Install-Module, such as:

Install-Module Import-Excel

Install-Module dbatools

Once the libraries are installed, we can try the following scripts to import the Excel data into SQL Server

$TargetServer = 'YourServerName'
$TargetDb = 'YourSchema'

$SourcePath = 'C:\yourPath\source.xlsx'
$WorksheetName = 'Sheet1'
$TableName = 'Sheet1$'

try{

    $data = Import-Excel -path $SourcePath -WorksheetName $WorksheetName -DataOnly

    $data | ConvertTo-DbaDataTable | Write-DbaDbTableData -SqlInstance $TargetServer -Database $TargetDb -Table $TableName -Truncate -Confirm:$false;

    ((Get-Date).ToString() + " | File Imported: " + $SourcePath);
}
catch
{
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    ((Get-Date).ToString() + " | Error: " + $ErrorMessage + " - " + $FailedItem);
}

You should be able to see that the script is being executed successfully.



The command Write-DbaDbTableData is very powerful. You should look into the documentation on what features are available.

Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

5 ) Using Node.js

In order to import the Excel data into SQL Server, you may first need to install the relevant library needed for the connection.

• mssql
• xlsx

Once the installation is done, you can prepare and run the codes below:

'use strict';
var http = require('http');
var port = process.env.PORT || 1337;

http.createServer(function (req, res) {
    if (req.url === '/favicon.ico') {
        res.writeHead(200, { 'Content-Type': 'image/x-icon' });
        res.end(/* icon content here */);
    } else {
        res.writeHead(200, { 'Content-Type': 'text/plain' });

        try {
            const conn = require('mssql');

            //Settings
            var TableName = 'Sheet1$';
            var SourceFile = 'C:\\yourPath\\source.xlsx';

            var config = {
                server: 'YourServerName',
                database: 'YourDatabaseName',
                authentication: {
                    type: 'default',
                    options: {
                        userName: 'UserID',
                        password: 'Password'
                    }
                },
                options: {
                    encrypt: false //Set to true if you're connecting to Azure
                }
            };

            //Load Excel Data
            var XLSX = require('xlsx');
            var workbook = XLSX.readFile(SourceFile, {
                type: 'binary',
                cellDates: true,
                cellNF: false,
                cellText: false
            });
            var ws = workbook.Sheets[workbook.SheetNames[0]];
            var xlData = XLSX.utils.sheet_to_json(ws, { raw: false, dateNF: 'yyyy-mm-dd' });

            conn.connect(config, function (err) {
                if (err) {
                    console.log(err);
                }

                //Clean the Target Table
                var sql = 'Truncate Table ' + TableName;
                conn.query(sql, function (err, result) {
                    if (err) {
                        console.log(err);
                    }
                });

                //Read Target Table Fields
                var sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N\'' + TableName + '\' ORDER BY ORDINAL_POSITION';
                conn.query(sql, function (err, result) {
                    if (err) {
                        console.log(err);
                    } else {
                        var cols = result.recordset;

                        var table = new conn.Table(TableName);
                        table.create = false;

                        if ((typeof (cols) !== 'undefined') && (cols !== null)) {
                            for (var i = 0; i < cols.length; i++) {
                                switch (cols[i]['DATA_TYPE']) {
                                    case 'datetime':
                                        table.columns.add(cols[i]['COLUMN_NAME'], conn.Date, { nullable: true });
                                        break;
                                    case 'varchar':
                                        table.columns.add(cols[i]['COLUMN_NAME'], conn.VarChar(cols[i]['CHARACTER_MAXIMUM_LENGTH']), { nullable: true });
                                        break;
                                    case 'money':
                                    case 'decimal':
                                        table.columns.add(cols[i]['COLUMN_NAME'], conn.Decimal(cols[i]['NUMERIC_PRECISION'], cols[i]['NUMERIC_SCALE']), { nullable: true });
                                        break;
                                    case 'int':
                                        table.columns.add(cols[i]['COLUMN_NAME'], conn.Int, { nullable: true });
                                        break;
                                    default:
                                        //Cannot be mapped
                                        break;
                                }
                            }
                        }

                        for (var row in xlData) {
                            table.rows.add(
                                ((typeof (xlData[row][cols[0]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[0]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[0]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[1]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[1]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[1]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[2]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[2]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[2]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[3]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[3]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[3]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[4]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[4]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[4]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[5]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[5]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[5]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[6]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[6]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[6]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[7]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[7]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[7]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[8]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[8]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[8]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[9]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[9]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[9]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[10]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[10]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[10]['COLUMN_NAME']]),
                                ((typeof (xlData[row][cols[11]['COLUMN_NAME']]) === 'undefined' || (xlData[row][cols[11]['COLUMN_NAME']]) === null) ? null : xlData[row][cols[11]['COLUMN_NAME']])
                            );
                        }

                        //Insert into Target Table
                        var req = new conn.Request();
                        conn.connect(config, function (err) {
                            if (err) {
                                console.log(err);
                            }

                            req.bulk(table, function (err, rowCount) {
                                //console.log(table);

                                if (err) {
                                    console.log(err);
                                } else {
                                    console.log(new Date().toLocaleString().replace(/T/, ' ').replace(/\..+/, '') + ' | Data Loaded Successfully | ' + rowCount.rowsAffected + ' rows affected');
                                }

                            });
                        });
                    }
                });
            });

            res.end('done!');

        } catch (err) {
            console.log(err);
        }
    }
}).listen(port);

You will probably get the error shown below when executing the above codes.

Failed to connect to YourServerName:1433 - Could not connect (sequence)

To resolve this, we need to make sure the TCP/IP setting is enabled under Protocols for MSSQLServer. We can find this setting by launching Computer Management and then look for Services and Applications > SQL Server Configuration Manager > SQL Server Network Configuration.


Once you've done that and re-execute the codes, you should able to see that the script is being executed successfully.


Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

6 ) Using Python

The execution in Python is easy too.

Before the execution, you may want to install the following modules:

• pandas
• Flask-SQLAlchemy

We can install these two libraries from the command pip install, such as:

pip install pandas

pip install Flask-SQLAlchemy

The script is shown as follows:

from sqlalchemy import create_engine
import urllib
import pandas as pd
import datetime as dt

# Settings
TargetServer = 'YourServerName'
SchemaName = 'dbo'
TargetDb = 'DatabaseName'
TableName = 'Sheet1$'
UserName = 'UserID'
Password = 'Password'
SourceFile = "C:\\yourPath\\source.xlsx"

# Configure the Connection
Params = urllib.parse.quote_plus(r'DRIVER={SQL Server};SERVER=' + TargetServer + ';DATABASE=' + TargetDb + ';UID=' + UserName + ';PWD=' + Password)
ConnStr = 'mssql+pyodbc:///?odbc_connect={}'.format(Params)
Engine = create_engine(ConnStr)

# Load the sheet into a DataFrame
df = pd.read_excel(SourceFile, sheet_name = 'Sheet1', header = 0)

# Clear the Data in Target Table
sql = 'Truncate Table Sheet1$'
with Engine.begin() as conn:
    conn.execute(sql)

# Load the Data in DataFrame into Table
df.to_sql(TableName, con=Engine, schema=SchemaName, if_exists='append', index=False)

print(dt.datetime.now().strftime("%Y-%m-%d %H:%M:%S") + ' | Data Imported Successfully')

You should be able to see that the script is being executed successfully.



Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

7 ) Using SQL Server Management Studio - sp_execute_external_script command

Due to the popularity of Machine Learning programs, from SQL Server 2016 onwards, we are able to execute Python and R scripts in SQL Server environment, in particular, the  sp_execute_external_script command is being used.

Before we run through the scripts, we need to do some configuration:

a) Execute the SQL script below to enable the settings:

sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

b) Restart the SQL Server services.

Once these are done, we can execute the SQL script below:

Truncate Table Sheet1$;


INSERT INTO Sheet1$
EXEC sp_execute_external_script
@language = N'Python',
@script =
N'
# Import Libraries
import pandas as pd


# Load Excel
SourceFile = "C:\\yourPath\\source.xlsx"
OutputDataSet = pd.read_excel(SourceFile, sheet_name = "Sheet1", header = 0)
'

You should be able to see that the script is being executed successfully.


Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

8 ) Using .NET (C#)

In the .NET environment, we could use a SqlBulkCopy object to efficiently import data into the database.

String ConnStr_Excel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\yourPath\source.xlsx;Extended Properties='Excel 12.0;HDR=YES;';";
            String ConnStr_SQLServer = "Data Source=YourServerName;Initial Catalog=YourDatabase;Persist Security Info=True;User ID=UserID;Password=Password";
           
            using (OleDbConnection ConnExcel = new OleDbConnection(ConnStr_Excel))
            using (SqlConnection ConnSQLServer = new SqlConnection(ConnStr_SQLServer))
            {
                //Read Excel data into OleDbDataReader
                OleDbCommand CmdExcel = new OleDbCommand("Select * from [Sheet1$]", ConnExcel);
                ConnExcel.Open();
                OleDbDataReader DrExcel = CmdExcel.ExecuteReader();


                ConnSQLServer.Open();


                //Clean up the Target Table
                SqlCommand CmdSQLServer = new SqlCommand("Truncate Table [Sheet1$]", ConnSQLServer);
                CmdSQLServer.CommandType = CommandType.Text;
                CmdSQLServer.ExecuteNonQuery();


                // Set up the Bulk Copy object.
                // Note that the column positions in the source
                // data reader match the column positions in
                // the destination table so there is no need to
                // map columns.
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnSQLServer))
                {
                    bulkCopy.DestinationTableName = "Sheet1$";


                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(DrExcel);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        // Close the OleDbDataReader. The SqlBulkCopy
                        // object is automatically closed at the end
                        // of the using block.
                        DrExcel.Close();
                    }
                }
            }
            MessageBox.Show(DateTime.Now.ToString("yyyy-MM-dd h:mm tt") + " | Data Imported Successfully!", "Process Completed", MessageBoxButtons.OK);

You should be able to see that the script is being executed successfully.



Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

9 ) Using PHP

I believe there are different approaches to load an Excel file in PHP and eventually load into a database. The question is whether we need to convert the Excel into a CSV file first? Since in PHP, there is an existing function fgetcsv which can be used to load the content of CSV file.

But to demonstrate in this article, I would try in another way, which will read the Excel content directly. Even by saying that, there are many libraries can do that, such as:

• PhpSpreadsheet
• SimpleXLSX
• XLSXReader
  
  

In this article, I will share the codes that are using SimpleXLSX and sqlsrv_connect libraries.

<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);


//DB Settings
$TargetServer = "YourServerName";
$TargetDb = "YourDatabaseName";
$TableName = "Sheet1$";
$UserName = "UserID";
$Password = "Password";
$SourceFile = "C:\\yourPath\\source.xlsx";


//Establish Connection
$connectionInfo = array("Database" => "$TargetDb", "UID" => "$UserName", "PWD" => "$Password", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect($TargetServer, $connectionInfo);
$colcnt = 0;


if ($conn) {


    //Get Column Count
    $sql = "SELECT * FROM $TableName Where 1 = 2";
    $stmt = sqlsrv_query($conn, $sql);
    if ($stmt === false) {
        die(print_r(sqlsrv_errors(), true));
    }


    $colcnt = count(sqlsrv_field_metadata($stmt));


} else {
    //"Connection could not be established";
    die(print_r(sqlsrv_errors(), true));
}


//Clear the Data
$sql = "Truncate Table $TableName;";
$stmt = sqlsrv_prepare($conn, $sql);
if (sqlsrv_execute($stmt) === false) {
    die(print_r(sqlsrv_errors(), true));
}


//Load the Excel
require_once 'includes/simplexlsx-master/src/SimpleXLSX.php';


if ($xlsx = SimpleXLSX::parse($SourceFile)) {


    //Load Data into Target Table
    $sql = "INSERT INTO $TableName VALUES (" . str_repeat("?, ", $colcnt - 1) . " ?);";


    $header_values = $rows = [];
    foreach ($xlsx->rows() as $k => $r) {
        //Skip header
        if ($k === 0) {
            $header_values = $r;
            continue;
        }


        //Convert Empty String to NULL
        //A quick fix, which assume and convert all empty strings to null
        $r2 = array_map(function ($v) {
            return ($v === "") ? null : $v;
        }, $r);


        $stmt = sqlsrv_prepare($conn, $sql, $r2);
        if (!$stmt) {
            die(print_r(sqlsrv_errors(), true));
        }


        if (sqlsrv_execute($stmt) === false) {
            die(print_r(sqlsrv_errors(), true));
        }
    }
    echo date('Y-m-d H:i:s') . " | Data Loaded Successfully ";
} else {
    echo SimpleXLSX::parseError();
}


//Close Connection
sqlsrv_close($conn);

You should be able to see that the script is being executed successfully.
Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

10 ) Using Java

It has been a while since I programmed in Java / JSP, so this actually give me a chance to revise my programming skills in this area = )

To make this happen, we will need to install some libraries:

• Apache POI
• Microsoft JDBC Driver for SQL Server

After that, we can prepare and run the codes, as shown below:

package javaapplication1;


import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.sql.*;
import java.text.*;
import java.util.*;


public class JavaApplication1 {


    static final int MINIMUM_COLUMN_COUNT = 12;
    static final int START_ROW = 2;
    static final int MAX_ROW = 1000000;


    public static void main(String[] args) {
        Connection conn = null;
        try {


            // Settings
            String TargetName = "YourServerName";
            String SchemaName = "YourDatabaseName";
            String TableName = "Sheet1$";
            String UserName = "UserID";
            String Password = "Password";
            String url = "jdbc:sqlserver://" + TargetName + ";databaseName=" + SchemaName + ";user=" + UserName + ";password=" + Password + ";";
            String SourceFile = "C:\\YourPath\\source.xlsx";
            String SheetName = "Sheet1";


            //Connect to Target Database
            conn = DriverManager.getConnection(url, UserName, Password);
            conn.setAutoCommit(false);


            //Clean the Target Table
            Statement stmt = conn.createStatement();
            String sql = "Truncate Table " + TableName;
            stmt.executeUpdate(sql);
            conn.commit();


            //Read Excel and Insert its Content to Target Table
            FileInputStream inputStream = new FileInputStream(SourceFile);
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheet(SheetName);


            sql = "INSERT INTO " + TableName + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            PreparedStatement prepstmt = conn.prepareStatement(sql);


            // Decide which rows to process
            int rowStart = Math.max(START_ROW - 1, sheet.getFirstRowNum());
            int rowEnd = Math.max(MAX_ROW, sheet.getLastRowNum());


            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {


                Row r = sheet.getRow(rowNum);
               
                if (r == null) {
                    // This whole row is empty
                    // Handle it as needed
                    continue;
                }


                int lastColumn = Math.max(r.getLastCellNum(), MINIMUM_COLUMN_COUNT);
               
                for (int cn = 0; cn < lastColumn; cn++) {


                    Cell c = r.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);


                    if (c == null) {
                        //Blank Cell, write as NULL
                        prepstmt.setNull(cn + 1, Types.NULL);
                    } else {
                        //Check the cell type and format accordingly
                        switch (c.getCellType()) {
                            case STRING:
                                prepstmt.setObject(cn + 1, c.getRichStringCellValue().getString());
                                break;


                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(c)) {
                                    prepstmt.setObject(cn + 1, c.getDateCellValue());
                                } else {
                                    prepstmt.setObject(cn + 1, c.getNumericCellValue());
                                }
                                break;


                            case BOOLEAN:
                                prepstmt.setObject(cn + 1, c.getBooleanCellValue());
                                break;
                        }
                    }
                }
                prepstmt.addBatch();
            }


            //Insert into Target Table
            prepstmt.executeBatch();
            conn.commit();


            String pattern = "dd/MM/yyyy h:mm:ss a z";
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern, new Locale("en", "US"));
            String date = simpleDateFormat.format(new java.util.Date());


            System.out.println(date + " | Data Loaded Successfully");
        } catch (SQLException | IOException ex) {
            ex.printStackTrace(System.out);
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace(System.out);
            }
        }


    }


}

You should be able to see that the script is being executed successfully.


Then you can examine the data that was imported into table: Sheet1$ in SQL Server.

11 ) Using Golang

I'm new to Golang so this was my first time programming on it!

It was a pretty powerful language and just for the codes below alone, I spent two days doing research and development.

It's fine to run the Golang in Visual Studio Code as extensions are available, but you will need to:

a ) Install the Go

b ) For this article, to install the following libraries:

• go-mssqldb
• Excelize

Once this is done, we can try it out:

package main


import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "reflect"
    "strconv"
    "time"


    "github.com/360EntSecGroup-Skylar/excelize"
    _ "github.com/denisenkom/go-mssqldb"
)


// NullInt64 is an alias for sql.NullInt64 data type
type NullInt64 sql.NullInt64


// Scan implements the Scanner interface for NullInt64
func (ni *NullInt64) Scan(value interface{}) error {
    var i sql.NullInt64
    if err := i.Scan(value); err != nil {
        return err
    }


    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *ni = NullInt64{i.Int64, false}
    } else {
        *ni = NullInt64{i.Int64, true}
    }
    return nil
}


// NullBool is an alias for sql.NullBool data type
type NullBool sql.NullBool


// Scan implements the Scanner interface for NullBool
func (nb *NullBool) Scan(value interface{}) error {
    var b sql.NullBool
    if err := b.Scan(value); err != nil {
        return err
    }


    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *nb = NullBool{b.Bool, false}
    } else {
        *nb = NullBool{b.Bool, true}
    }


    return nil
}


// NullFloat64 is an alias for sql.NullFloat64 data type
type NullFloat64 sql.NullFloat64


// Scan implements the Scanner interface for NullFloat64
func (nf *NullFloat64) Scan(value interface{}) error {
    var f sql.NullFloat64
    if err := f.Scan(value); err != nil {
        return err
    }


    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *nf = NullFloat64{f.Float64, false}
    } else {
        *nf = NullFloat64{f.Float64, true}
    }


    return nil
}


// NullString is an alias for sql.NullString data type
type NullString sql.NullString


// Scan implements the Scanner interface for NullString
func (ns *NullString) Scan(value interface{}) error {
    var s sql.NullString
    if err := s.Scan(value); err != nil {
        return err
    }


    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *ns = NullString{s.String, false}
    } else {
        *ns = NullString{s.String, true}
    }


    return nil
}


// NullTime is an alias for sql.NullTime data type
type NullTime sql.NullTime


// Scan implements the Scanner interface for NullTime
func (nt *NullTime) Scan(value interface{}) error {
    var t sql.NullTime
    if err := t.Scan(value); err != nil {
        return err
    }


    // if nil then make Valid false
    if reflect.TypeOf(value) == nil {
        *nt = NullTime{t.Time, false}
    } else {
        *nt = NullTime{t.Time, true}
    }


    return nil
}


// ConvertValue : Get Data Type and Convert its Value
func ConvertValue(f *excelize.File, worksheetname string, records []TargetTable, dbColIndex int, rowNumber int, colNumber int) interface{} {
    // Get value from cell by given worksheet name and axis.
    char := string(rune(runascii+colNumber)) + strconv.FormatInt(int64(rowNumber), 10)
    v, _ := f.GetCellValue(worksheetname, char)


    switch records[dbColIndex].dataType {
    case "datetime":
        return v
    case "varchar":
        return v
    case "money", "decimal":
        if len(v) == 0 {
            return nil
        }
        t, _ := strconv.ParseFloat(v, 64)
        return t
    case "int":
        if len(v) == 0 {
            return nil
        }
        t, _ := strconv.ParseInt(v, 10, 32)
        return t
    default:
        return v
    }
}


//FormatNow : Format Date Time
func FormatNow() string {
    const layout = "2 Jan 2006 3:04pm"
    now := time.Now().Format(layout)
    return now
}


// Settings
const (
    server        = "YourServerName"
    port          = 1433
    user          = "UserID"
    password      = "Password"
    database      = "YourSchema"
    sourcepath    = "C:\\yourPath\\source.xlsx"
    worksheetname = "Sheet1"
    tablename     = "Sheet1$"
    runascii      = 64
    startRow      = 2
)


var (
    db  *sql.DB
    err error
)


// TargetTable : Target Table's Structure
type TargetTable struct {
    colName          string
    dataType         string
    chaMaxLen        NullInt64
    numericPrecision NullInt64
    numericScale     NullInt64
}


func main() {


    // Create connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;", server, user, password, port, database)


    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal(err)
    }


    //Clean the Target Table
    sqlstr := ""
    sqlstr = fmt.Sprintf("Truncate Table %s;", tablename)
    if _, err = db.Exec(sqlstr); err != nil {
        log.Fatal(err)
    }


    //Read Target Table Fields
    sqlstr = fmt.Sprintf("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'%s' ORDER BY ORDINAL_POSITION;", tablename)
    trows, err := db.Query(sqlstr)
    if err != nil {
        log.Fatal(err)
    }
    defer trows.Close()
    var records []TargetTable


    for trows.Next() {
        var record TargetTable


        err := trows.Scan(&record.colName, &record.dataType, &record.chaMaxLen, &record.numericPrecision, &record.numericScale)
        if err != nil {
            log.Fatal(err)
        }
        records = append(records, record)
    }


    //Read Excel File Content
    f, err := excelize.OpenFile(sourcepath)
    if err != nil {
        log.Fatal(err)
    }


    rows, err := f.GetRows(worksheetname)


    sqlstr = fmt.Sprintf("insert into %s ([%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]) values (@pDates, @pType, @pCode, @pPrice, @pUnit, @pSubTotal, @pBrokeageRate, @pTax, @pStampDuty, @pClearingFee, @pAmount, @pSvcCost);", tablename, records[0].colName, records[1].colName, records[2].colName, records[3].colName, records[4].colName, records[5].colName, records[6].colName, records[7].colName, records[8].colName, records[9].colName, records[10].colName, records[11].colName)
    stmt, err := db.Prepare(sqlstr)


    if err != nil {
        log.Fatal(err)
    }


    // Close the database connection pool after program executes
    defer db.Close()


    ctx := context.Background()


    //Load Data into Target Table
    for i := startRow; i <= len(rows); i++ {
        // Close the statement
        defer stmt.Close()


        var pDates = sql.Named("pDates", ConvertValue(f, worksheetname, records, 0, i, 1))
        var pType = sql.Named("pType", ConvertValue(f, worksheetname, records, 1, i, 2))
        var pCode = sql.Named("pCode", ConvertValue(f, worksheetname, records, 2, i, 3))
        var pPrice = sql.Named("pPrice", ConvertValue(f, worksheetname, records, 3, i, 4))
        var pUnit = sql.Named("pUnit", ConvertValue(f, worksheetname, records, 4, i, 5))
        var pSubTotal = sql.Named("pSubTotal", ConvertValue(f, worksheetname, records, 5, i, 6))
        var pBrokeageRate = sql.Named("pBrokeageRate", ConvertValue(f, worksheetname, records, 6, i, 7))
        var pTax = sql.Named("pTax", ConvertValue(f, worksheetname, records, 7, i, 8))
        var pStampDuty = sql.Named("pStampDuty", ConvertValue(f, worksheetname, records, 8, i, 9))
        var pClearingFee = sql.Named("pClearingFee", ConvertValue(f, worksheetname, records, 9, i, 10))
        var pAmount = sql.Named("pAmount", ConvertValue(f, worksheetname, records, 10, i, 11))
        var pSvcCost = sql.Named("pSvcCost", ConvertValue(f, worksheetname, records, 11, i, 12))


        if _, err := stmt.ExecContext(ctx, pDates, pType, pCode, pPrice, pUnit, pSubTotal, pBrokeageRate, pTax, pStampDuty, pClearingFee, pAmount, pSvcCost); err != nil {
            log.Fatal(err)
        }
    }
    fmt.Println(FormatNow() + " | Data Loaded Successfully!")
}

You should be able to see that the script is being executed successfully.


Then you can examine the data that was imported into table: Sheet1$ in SQL Server.