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] GOStep 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 dbatoolsOnce 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-SQLAlchemyThe 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: