Excel header columns changes when export data from sql server to Excel using pandas?

Python
1

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

I run script on sql server 2017 to export data from sql server 2017 to Excel file already exist .

header of excel file before export data as below

StudentId,StudentName

after run query to 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 ?

Notes Not practical way to change column name on sql server table or rename it to be as excel header .

expected result

StudentId StudentName
1          ahmed
2          eslam
2

It sounds like you’re encountering an issue with the header columns changing when exporting data from SQL Server to Excel using pandas. This could be due to formatting or data type inconsistencies. Make sure your data is properly formatted before exporting. If you’re still facing issues, consider troubleshooting or seeking help. Additionally, if you’re looking for a reliable solution, you might want to consider buy a VPS server to optimize your data handling processes.