Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!
  • Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

Python How to keep columns header on excel without change after export data to excel file?

ahmedbarbary

New Coder
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 export data to excel by this line

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

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 data without header and header row blank

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 from Name to StudentName on sql server table create view to use it as excel header .

expected result

StudentId StudentName
1 ahmed

script Query I run it as below for lookup

Code:
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
sql server table

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')
final result pciture.png
 

New Threads

Buy us a coffee!

Back
Top Bottom