How to prevent change columns name header when export SQL server table to excel for second time ?

Question

How to prevent change columns name header when export sql server table to excel file for second time?

I work on sql server 2017 i have script python export SQL server table students to excel path

When run script below excel file exported success with data and headers for first time only

when use script below used for export students table to excel for second time header changes from student name to Name

so my issue How to prevent column names header from changes when export students table to excel for second time

so my table students below :

CREATE TABLE [dbo].[students](
[StudentId] [int] NOT NULL,
[StudentName] [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]
GO
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (1, N'ahmed')
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (2, N'eslam')
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (3, N'mohamed')
GO

and exactly export students table to excel on path G:\ImportExportExcel

as studentid,studentname

Export headers for first time without any issue

my issue done when change column name from studentname to Name on table students

and export again it become studentid,Name on excel file path

so how to prevent header columns name from change if file exist on path G:\ImportExportExcel

expected result

StudentIdStudentName
1        ahmed
2        eslam
3        mohamed

code used for export from sql server to excel

declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @SchemaName NVARCHAR(MAX)=''
declare @ObjectlisttoExport NVARCHAR(MAX)='dbo.students'







 SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END

 DECLARE @ValidPath TABLE (ValidPathCheck BIT)

INSERT @ValidPath
EXEC sp_execute_external_script
@language =N'Python',
@script=N'
import pandas as pd
d = os.path.isdir(ExportFilePath)
OutputDataSet = pd.DataFrame([d],columns=["Filename"])'
,@params = N'@ExportFilePath NVARCHAR(MAX)'
,@ExportFilePath = @ExportPath


DROP TABLE IF EXISTS #ExportTablesList, #FinalExportList

CREATE TABLE #ExportTablesList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))

--Get the list of objects to be exported
INSERT #ExportTablesList (Cols,TableName)
SELECT  CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp','decimal','bit','int','bigint')
               THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME))
          ELSE C.name END Cols  -- To cover poor data type conversions b/n Python & SQL Server
  ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName
FROM Sys.tables T
JOIN sys.columns C
ON T.object_id = C.object_id
JOIN sys.types TY
ON C.[user_type_id] = TY.[user_type_id]
WHERE Schema_name(T.schema_id) IN (SELECT value FROM STRING_SPLIT(@SchemaName, ','))
-- Ignore the datatypes that are not required to be exported
AND TY.name NOT IN ('geography','varbinary','binary','text', 'ntext', 'image', 'hierarchyid', 'xml', 'sql_variant') 

INSERT #ExportTablesList (Cols,TableName)
SELECT  CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp','decimal','bit','int','bigint')
               THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME))
          ELSE C.name END Cols  -- To cover poor data type conversions b/n Python & SQL Server
  ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName
FROM Sys.tables T
JOIN sys.columns C
ON T.object_id = C.object_id
JOIN sys.types TY
ON C.[user_type_id] = TY.[user_type_id]
WHERE CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) IN (SELECT value FROM STRING_SPLIT(@ObjectlisttoExport, ','))
-- Ignore the datatypes that are not required to be exported
AND TY.name NOT IN ('geography','varbinary','binary','text', 'ntext', 'image', 'hierarchyid', 'xml', 'sql_variant') 

--Dedup of object list
;WITH dedup
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY TableName,Cols ORDER BY Cols) Rn FROM #ExportTablesList
)
DELETE FROM dedup
WHERE Rn > 1

--Forming columns list as comma separated
SELECT TableName,IDENTITY(INT,1,1) AS TableCount
    , STUFF(
        (
        SELECT ', ' + C.Cols
        From #ExportTablesList As C
        WHERE C.TableName = T.TableName
        FOR XML PATH('')
        ), 1, 2, '') AS Cols
INTO #FinalExportList
From #ExportTablesList As T
GROUP BY TableName
  ----select * from #FinalExportList

DECLARE @I INT = 1
       ,@TableName NVARCHAR(200)
       ,@SQL NVARCHAR(MAX) = N''
       ,@PythonScript NVARCHAR(MAX) = N''
       ,@ExportFilePath NVARCHAR(MAX) = N''



 -- Just for testing purpose top 10 records are selected
SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
      ,@TableName = TableName
FROM #FinalExportList WHERE TableCount = @I


SET @PythonScript = N'
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)'

  --- print @PythonScript
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

Answer 1

Maybe replace both of "ELSE C.name END Cols" with:

 ELSE case C.name when 'Name' then 'StudentName' else C.name end END Cols

Answer 2

Hi @ahmed salah ,

Welcome to Microsoft Q&A!
The members in this forum may not be much familiar with python. So it is not possible to identify possible problems on the python side.
You can try other methods of exporting tables.
Here is an article you can refer to. However, this document does not contain all the methods.
Hope this could give you some thoughts.

Best regards,
Seeya

Answer 3

Your Python script simply outputs the table and the column names in the table.

The simplest answer to your question is to create a view which has the fields and column names you want, and pass that to your script instead of the actual table. Then you will control the headers and fields in the output.

CREATE VIEW dbo.vw_students  
AS  
SELECT  
	StudentId,  
	[Name] as [StudentName]  
FROM dbo.students  

Then

 declare @ObjectlisttoExport NVARCHAR(MAX)='dbo.vw_students'