ahmedsa2018
Programmer
I need to append data to excel file already exist from table students on SQL Server .
So I need to append data when dbo.fn_FileExists(@FullFilePath)=1
Excel file already Created on D:\ExportExcel\dbo.students.xlsx with student IDs 1 and 2.
student table with old data
StudentId Name
1 ahmed
2 eslam
Table structure:
Code:
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]
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (3, N'Sayed')
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (4, N'Michel')Python script used
Python:
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
---Here code i need to write to append data
print 'Append data'
ENDSo How to append data from table students to excel file using Python script?
Expected result to file after append
after append data to excel
StudentId Name
1 ahmed
2 eslam
3 Sayed
4 Michel