×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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

Hi,

Another possibility, no code required, is to query the database that this data is in directly in Excel via Data > Get external data > From other sources > From Microsoft Query...

This query is embedded in the sheet and can be Refreshed on demand.

The column header can be customized in the query to anything you like.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

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

(OP)
thank you fo reply
this is not solution
i need export data to excel file and header on excel keep without chqnge
as sample
excel file
studentid studentname

i export data with different header as
studentid name
1 ahmed
2 eslam

then finally expected result i need
studentid studentname
1 ahmed
2 elsam
this i need to do it by python script change
so please how to do it by python script
how to change python script below to do that
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

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

(OP)
so can you please
SkipVought
help me to solve issue

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

It looks like your script is creating the header in excel file from the column names of your table.
From the SQL code you posted

CODE

CREATE TABLE [dbo].[students](
 [StudentId] [int] NOT NULL,
 [Name] [varchar](50) NULL,
... 
you have the table students with columns StudentId and Name, so the python script creates header StudentId, Name

If you want the header StudentId, StudentName then change the table column name from Name to StudentName or create the new table with that column

CODE

CREATE TABLE [dbo].[students](
 [StudentId] [int] NOT NULL,
 [StudentName] [varchar](50) NULL,
... 
Then populate the table with data and export it to excel using your python script

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

(OP)
i don't need to change column name from sql
i need to keep header on excel as it is without change
so please help me on that
my question can i keep header without change by python
when export data with different header

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

(OP)
when export data to excel i need to keep header exist before as it is without change
no need to change column name from sql server because every time i change header i will change column from sql server
so i need it dynamically and controlled by me

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

(OP)
i need solution from python
can i change this statment
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
or modified it to
keep header excel without overwrite it from exported data

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

you wrote above, that it changes your header

Quote:


my issue is header changed from column name StudentName to Name



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

(OP)
yes
header changed on excel and i don't need to change it
excel file i export to it before exporg data
header is
studentid,studentname

after export data
heaer become
studentid,name
1 ahmed

i need it as below
studentid studentname
1 ahmed

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

Then try to change the arguments from to_excel() method
from

CODE

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

CODE

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

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

(OP)
yes your excellent this is what i need
but this is static
i don't need to get it static i need to get it dynamically from excel
can you help me get header from excel without write it
so if i write studentid,studentname on excel
then get it from excel
meaning header=what header on file already exist

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

if it works then you are using the python module pandas

Here is the documentation of the method to_excel(). You can look what for parameters the method has:
https://pandas.pydata.org/docs/reference/api/panda...

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

(OP)
yes it works
if possible can you help me
to read header dynamically from excel
because may be this query applied for another table
so i need to read it from file dynamically
can you help me please
im beginner on python
so header will be as below

InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header = what header on file studentexport.xlsx, index=False)

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

you provided only this part from the python script

CODE

import shutil
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False) 
but where is the part with import pandas or import pandas as pd ?

I would try to replace the above python code with this code defining the excel writer based on the existing Excel file

CODE

import shutil
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
excel_writer = pd.ExcelWriter(FullFilePath, engine='xlsxwriter')
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(excel_writer,sheet_name=TableName.split(".")[-1],index=False) 
or if it does not work try to change in the code above pd to pandas

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

or define escel_writer without engine

instead of this

CODE

excel_writer = pd.ExcelWriter(FullFilePath, engine='xlsxwriter') 

only this

CODE

excel_writer = pd.ExcelWriter(FullFilePath) 

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

Is it working or not ?

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

(OP)
No
not working
i try this first

SET @PythonScript = N'import shutil
import xlsxwriter
import pandas as pd
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
excel_writer = pd.ExcelWriter(FullFilePath, engine=''xlsxwriter'')
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(excel_writer,sheet_name=TableName.split(".")[-1],index=False)
'
it give me result
StudentId Name
1 ahmed
2 eslam
3 mohamed
i also try pd.ExcelWriter without xlswrite as below

SET @PythonScript = N'import shutil
import xlsxwriter
import pandas as pd
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
excel_writer = pd.ExcelWriter(FullFilePath)
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(excel_writer,sheet_name=TableName.split(".")[-1],index=False)
'
it give me header also as it is exist on sql as below

StudentId Name
1 ahmed
2 eslam
3 mohamed

but expected as

StudentId StudentName
1 ahmed
2 eslam
3 mohamed

so what i do t solve it

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

And as before you tried it, was in the excel file StudentExport.xlsx
the right header StudentId StudentName ?

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

(OP)
yes
on excel file
header is
StudentId,studentName
but after export data
it become studentid,Name

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

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],header=False,index=False) 

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

(OP)
it export data without header
see image below

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

(OP)
so are there are any solution for that
I appreciate your help and support too much

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

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

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

try this

CODE

import shutil
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
excel_header = list(pd.read_excel(FullFilePath).columns)
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=excel_header, index=False) 

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

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:

excel_header = ['StudentId', 'StudentName'] 

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], header=excel_header, index=False) 
I think, that this could work for you.

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

Does it work for you or not ?

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

(OP)
yes working
thank you very much
it solved
thank you for reply

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

ok smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close