I work on sql server 2017 I run script depend on python language .
I run script run query on sql server 2017 to export data to Excel file.
header of excel file before export data as below
StudentId,StudentName
after run query export data from sql server to excel Header changed to
StudentId,Name
why header changed from Student Name to Name ?
I export data to excel by this line
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
I try to change it to
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)
but it give me data without header and header row blank
my script python as below
---drop table #FinalExportList 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)' 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
so can any one help me to export data to excel file without change or overwrite header ?