Click here to Skip to main content
15,902,885 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.

student table with old data
Table structure:
studentid Name
1 ahmed
2 eslam

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.

student table with old data

Table structure:

SQL
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')


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?

Notes when dbo.fn_FileExists(@FullFilePath)=0 i create excel file with data on students table and i don't need any thing for create excel file .

Expected result to file after append

studentid  Name
1          ahmed
2          eslam
3          Sayed
4          Michel


What I have tried:

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
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900