How to modify python script to append data on file using sql server 2019?
-
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 = @TableNameWhen 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
-
What happens when you run this? Does the existing data in Excel get overwritten, or does the Excel file remain unchanged?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
it remain without change
meaning after apply script
nothing change
but not append to existing file
an nothing change
-
If you remove the existing data from the Excel file, does the new data get written to the file?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply