I've developed that solution in Python through the VS 2017:
This is the Excel spreadsheet I'm loading:
These are the codes:
#ExcelConnection.py
=============================================================================================================
from xlrd import open_workbook
def NZTAVehicleNZNewRegistrationConn():
book = open_workbook('C:\Temp\\NUCARS.xls')
sheet = book.sheet_by_name('NUCARS')
return sheet
def NZTAVehicleImportedRegistrationConn():
book = open_workbook('C:\Temp\\064X-P.xls')
sheet = book.sheet_by_name('064X-P')
return sheet
def NZTADeregistrationAgentConn():
book = open_workbook('C:\Temp\\MR15_REPORT_TU_AGENTS.xls')
sheet = book.sheet_by_name('MR15_REPORT_TU_AGENTS')
return sheet
def NZTAVehiclebySaleTypeConn():
book = open_workbook('C:\Temp\\058-D.xls')
sheet = book.sheet_by_name('058-D')
return sheet
def NZTAVehicleDeregistrationConn():
book = open_workbook('C:\Temp\\REPORT_DEREG_MONTHLY.xls')
sheet = book.sheet_by_name('REPORT_DEREG_MONTHLY')
return sheet
def NZTAVehicleNZNewRegistrationTruckConn():
book = open_workbook('C:\Temp\\TRUCKS_REG_MONTHLY_NZNEW.xls')
sheet = book.sheet_by_name('TRUCKS_REG_MONTHLY_NZNEW')
return sheet
def NZTARentalVehicleConn():
book = open_workbook('C:\Temp\\RNTLCARS_by_PDISTRICT.xls')
sheet = book.sheet_by_name('RNTLCARS_by_PDISTRICT')
return sheet
def NZTAVehicleTurnersConn():
book = open_workbook('C:\Temp\\TURNERS.xls') #Stage.NZTAVehiclebySaleType
sheet = book.sheet_by_name('TURNERS')
return sheet
def NZTAVehicleUsedRegistrationTruckConn():
book = open_workbook('C:\Temp\\TRUCKS_REG_MONTHLY_USED.xls') #Stage.NZTAVehicleNZNewRegistrationTruck
sheet = book.sheet_by_name('TRUCKS_REG_MONTHLY_USED')
return sheet
#SQLConnection.py
=============================================================================================================
import pandas as pd
import pyodbc
def OpenConn():
connStr = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=10.250.11.121;DATABASE=TDW;Trusted_Connection=yes')
return connStr.cursor()
#NZTADeregistrationAgent.py
=============================================================================================================
import SQLConnection
import ExcelConnection
cursor = SQLConnection.OpenConn()
sheet = ExcelConnection.NZTADeregistrationAgentConn()
for row in range(sheet.nrows):
try:
if row == 0:
pass
else:
cursor.execute("Insert [TDW].[Stage].[NZTADeregistrationAgent] ([DeRegMonth],[Outlet],[VehicleType],[TotalDeregistered],[NumberOfInsuranceWrittenOff],[NumberOfPermanentlyTakenOfRoad],[NumberOfDestroyedUseless],[NumberOfTradePlateReturned]) Values ("+ ''.join(str(e).replace('[','').replace(']','') for e in [sheet.row_values(row)]) + ")")
cursor.commit()
except Exception as err:
print("NZTADeregistrationAgent loading has failed due to the error: "+ str(err))
break
cursor.close()
#NZTAVehicleImportedRegistration.py
=============================================================================================================
import SQLConnection
import ExcelConnection
cursor = SQLConnection.OpenConn()
sheet = ExcelConnection.NZTAVehicleImportedRegistrationConn()
for row in range(sheet.nrows):
try:
if row == 0:
MonthYear = (sheet.cell_value(0,0))
MonthYear = MonthYear[:MonthYear.find('-')]
if row == 1:
pass
if row > 1:
cursor.execute("Insert [TDW].[Stage].[NZTAVehicleImportedRegistration] ([Month],[VehicleMake],[Model],[CountryOfPreviousRegistration],[WHA],[AUC],[HAM],[THA],[TAU],[ROT],[GIS],[NAP],[NEW],[WAN],[PAL],[MAS],[WEL],[NEL],[BLE],[GRE],[WES],[CHR],[TIM],[OAM],[DUN],[INV],[Total]) Values ('"+ MonthYear + "'," + ''.join(str(e).replace('[','').replace(']','') for e in [sheet.row_values(row)]) + ")")
cursor.commit()
except Exception as err:
print("NZTAVehicleImportedRegistration loading has failed due to the error: "+ str(err))
break
cursor.close()
#NZTAVehicleNZNewRegistration.py
=============================================================================================================
import SQLConnection
import ExcelConnection
cursor = SQLConnection.OpenConn()
sheet = ExcelConnection.NZTAVehicleNZNewRegistrationConn()
for row in range(sheet.nrows):
try:
colData = []
if row == 0:
Year = (sheet.cell_value(0,3))
Year = Year[:Year.find('/')]
if row > 0:
for col in range(15):
if col < sheet.ncols:
colData.append(sheet.cell_value(row,col))
else:
colData.append('0')
cursor.execute("Insert [TDW].[Stage].[NZTAVehicleNZNewRegistration] ([Year],[VehicleType],[VehicleMake],[VehicleModel],[Month1],[Month2],[Month3],[Month4],[Month5],[Month6],[Month7],[Month8],[Month9],[Month10],[Month11],[Month12]) Values ('"+ Year + "'," + ''.join(str(colData[:]).replace('[','').replace(']','')) + ")")
cursor.commit()
except Exception as err:
print("NZTAVehicleNZNewRegistration loading has failed due to the error: "+ str(err))
break
cursor.close()
This is the way to call over through the SQL Jobs:
