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.
Run immediately | In case we only need to import the data once. |
Save SSIS Package | This is an useful option that enable us to save the importing task as a SSIS Package, which can be used for repeating tasks. |
--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
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.
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
Truncate Table Sheet1$;
The column "Dates" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.
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.
'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.
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.
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
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.
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.
<?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.
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);
}
}
}
}
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
Author
Commented:I will have a look on the question you have mentioned. cheers
Commented: