How to modify python script to append data on file using sql server 2019?
How to modify python script to append data on file using sql server 2019?
(OP)
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 --> server
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
CODE --> 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'
END So 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
Talk To Other Members
RE: How to modify python script to append data on file using sql server 2019?
However, the question is if simply rewriting the old excel file with new file containing all data (i.e. old + new) is not better than trying to append new data to the old file.
RE: How to modify python script to append data on file using sql server 2019?
i'm new on python how to do it please
can you help me
RE: How to modify python script to append data on file using sql server 2019?
ELSE
BEGIN
---what i write here to append data to excel file by python
END
RE: How to modify python script to append data on file using sql server 2019?
mikrom
RE: How to modify python script to append data on file using sql server 2019?
RE: How to modify python script to append data on file using sql server 2019?
When appending try to do exactly the same as when creating
CODE
BEGIN 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