• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

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

ahmedbarbary

New Coder
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:

Code:
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (3, N'Sayed')
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (4, N'Michel')

Python script used

Code:
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

after append data to same path
 
Top