Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

ahmedsa2018

Programmer
Apr 25, 2018
67
0
0
EG
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

Python:
----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
,@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_l1ca4a.png
 
Try to change in the script above this
Code:
InputDataSet.to_excel(excel_writer,sheet_name=TableName.split(".")[-1],index=False)
to this
Code:
InputDataSet.to_excel(excel_writer,sheet_name=TableName.split(".")[-1],[highlight #FCE94F]header=False[/highlight],index=False)
 
so are there are any solution for that
I appreciate your help and support too much
 
If the above does not work, then first you need to read the header from existing excel file and then write it back to the excel file when writing the data using to_excel() method.
I have null experience with pandas module so I need to try it, how it works..
 
try this
Code:
import shutil
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
[highlight #FCE94F]excel_header = list(pd.read_excel(FullFilePath).columns)
[/highlight]shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], [highlight #FCE94F]header=excel_header[/highlight], index=False)
 
I tried that:
This line
Code:
excel_header = list(pd.read_excel(FullFilePath).columns)
reads the header of the existing excel file into the list:

[pre]excel_header = ['StudentId', 'StudentName'][/pre]

then you can try to write the excel header into the excel file with this line
Code:
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], [highlight #FCE94F]header=excel_header[/highlight], index=False)
I think, that this could work for you.
 
yes working
thank you very much
it solved
thank you for reply
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top