Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

ahmedsa2018

Programmer
Apr 25, 2018
67
0
0
EG

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'
END
When use Python script 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 excel

StudentId Name
1 ahmed
2 eslam
3 Sayed
4 Michel

 
ahmedsa2018 said:
When use Python script it will not append data to Excel file from table students.
Yes, because you probably didn't have written this part.

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.
 
so please how to do that
i'm new on python how to do it please
can you help me
 
so what code i can write it on else statment to append data

ELSE
BEGIN
---what i write here to append data to excel file by python
END
 
If you didn't solved this problem in the meantime then try following:

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top