Python Forum
How to keep columns header on excel without change after export data to excel file?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to keep columns header on excel without change after export data to excel file?
#1
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 wihout 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 Header changed to

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

Exactly I need Export data to excel StudentExport.xlsx and depend on header exist on excel file

not depend on columns header of table exist on Sql server .

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 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

blank header
1 ahmed
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

----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)
'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
,@[email protected]
,@[email protected]
sql server table

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')
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Converting a json file to a dataframe with rows and columns eyavuz21 13 383 Jan-29-2023, 03:59 PM
Last Post: eyavuz21
  Import XML file directly into Excel spreadsheet demdej 0 184 Jan-24-2023, 02:48 PM
Last Post: demdej
  how to read txt file, and write into excel with multiply sheet jacklee26 14 939 Jan-21-2023, 06:57 AM
Last Post: jacklee26
  excel lookups Deephect 0 182 Jan-13-2023, 12:46 PM
Last Post: Deephect
  Create Excel Line Chart Programmatically dee 3 260 Dec-30-2022, 08:44 PM
Last Post: dee
  Extracting Data into Columns using pdfplumber arvin 17 660 Dec-17-2022, 11:59 AM
Last Post: arvin
Thumbs Up Need to compare the Excel file name with a directory text file. veeran1991 1 182 Dec-15-2022, 04:32 PM
Last Post: Larz60+
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 395 Dec-12-2022, 08:22 PM
Last Post: jh67
  Excel file reading problem max70990 1 180 Dec-11-2022, 07:00 PM
Last Post: deanhystad
  How to loop through all excel files and sheets in folder jadelola 1 372 Dec-01-2022, 06:12 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020