Author: ahmed_101

I work on sql server 2017 I run script depend on python language v 3.10 .

I need to export data to excel fileStudentExport.xlsx already exist, and keep header without change after export.

header of excel file StudentExport.xlsx before export data to it as below

StudentId,StudentName

after run script query to export data to StudentExport.xlsx and Header changed to

StudentId,Name

my issue is header changed from column name StudentName to Name (exist on sql)

I try to change InputDataSet.to_excel it to keep header on excel file StudentExport.xlsx without change as below

InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)

but it give me blank header

script Query I run it as below for lookup

 declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
    declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
    CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
    insert into #FinalExportList(TableName,Cols)
    values
    ('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')

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


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


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


    SET @PythonScript = N'import shutil
    FullFilePath = ExcelFilePath+"StudentExport.xlsx"
    shutil.copy(FixedPath,ExportPath)
    InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
    'f

    exec sp_execute_external_script
    @language = N'Python'
    ,@script = @PythonScript
    ,@input_data_1 = @SQL
    ,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200),@FixedPath NVARCHAR(MAX),@ExportPath NVARCHAR(MAX)'
    ,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
    ,@TableName = @TableName
    ,@FixedPath=@FixedPath
    ,@ExportPath=@ExportPath


    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]
     GO
     INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')




expected result
StudentId StudentName
1          ahmed