I need to append data to excel file already exist from table students on SQL Server .
Excel file already Created on D:\ExportExcel\dbo.students.xlsx with student IDs 1 and 2.
Table Insert Commands:
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (3, N'Sayed')
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (4, N'Michel')
Python script used
DECLARE @PythonScript NVARCHAR(MAX) = N''
declare @SQL NVARCHAR(MAX) = N'select studentid,Name from dbo.students;'
declare @ExportPath varchar(max)='D:\ExportExcel\'
declare @TableName varchar(max)='dbo.students'
declare @FullFilePath varchar(max) = concat(@ExportPath,@TableName+'.xlsx')
SET @PythonScript = N'
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)'
EXEC sp_execute_external_script
@language = N'Python'
,@script = @PythonScript
,@input_data_1 = @SQL
,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200)'
,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
,@TableName = @TableName
When use Python script explain it will not append data to Excel file from table students. Meaning it will not add student IDs 3 and 4 .
So How to append data from table students to excel file using Python script?
Expected result to file after append
