How to modify python script to append data on file using sql server 2019?

Question

Post reply

How to modify python script to append data on file using sql server 2019?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

August 2, 2022 at 10:48 am

#4071284

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

finalResult

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246980 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

it remain without change

meaning after apply script

nothing change

but not append to existing file

an nothing change

Phil Parkin SSC Guru Points: 246980 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel