0
Answer

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

ahmed salah

ahmed salah
2y
517
1

I need to append data to excel file from table students on SQL Server if Excel file already exist.

So I need to append data when dbo.fn_FileExists(@FullFilePath)=1 using an Else statement to append new data to the Excel file from Table students.

I have excel file already Created on D:\ExportExcel\dbo.students.xlsx with student IDs 1 and 2.

StudentId	Name
1	        ahmed
2	        eslam

Table structure:

 

 

CREATE TABLE [dbo].[students](
    [StudentId] [int] NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED 
(
    [StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table insert command

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')
--IF File Not Exist
if(dbo.fn_FileExists(@FullFilePath)=0)
BEGIN
   ---print 'Create File'
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
END
ELSE
BEGIN
---append data
print 'Append data'
END

?

If I use Python script above again it will not append data to Excel file from table students. Meaning it will not add student id 3 and 4 .

So How to append data from table students to excel file using Python script?

 

Expected result to file after append

StudentId	Name
1	        ahmed
2	        eslam
3	        Sayed
4	        Michel
Next Recommended Forum