Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on sql server 2017 I run script depend on python language .

I run script run query on sql server 2017 to export data to
Excel file.

header of excel file before export data as below

StudentId,StudentName

after run query export data from sql server to excel
Header changed to
StudentId,Name

why header changed from StudentName to Name ?

I export data to excel by this line
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)


Table student sturcture

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]


    INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')


can any one help me to export data from sql server to excel without change header on excel ?

Expected result will be :

StudentId	StudentName
        1	    ahmed

no need to change input column name from sql because
no practical way to change column name from sql server
and also to can more controls on header exported

I tried this below

but it give me blank row header

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

can any one help me

What I have tried:

---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
Posted
Updated 1-May-22 11:56am
v5
Comments
0x01AA 1-May-22 14:17pm    
These two paramaters should help: InputDataSet.to_excel(......, header=False, startrow= 1)
ahmed_sa 1-May-22 16:34pm    
Thank you for reply
I test that and give me data without header


1 ahmed
2 eslam
3 mohamed

no header for data
see image below
https://www.mediafire.com/view/fgkrnvmg0iox9ka/img_picture_issue.png/file

1 solution

According to your code, the second column of your database is Name, not StudentName. Maybe you should read your own code more closely.
SQL
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')
 
Share this answer
 
Comments
ahmed_sa 1-May-22 13:47pm    
thanks for reply
no no need that
I don't need to change column from sql
no practical to go to table and change column name
i need to export data without overwrite header excel
meaning i will get data and fill excel with header exist on excel without change it
so excel file have header
studentid studentname
export data to these two columns without change header name
so please can you help me please ?
Richard MacCutchan 2-May-22 3:16am    
You need to provide the header names as described in the documentation at pandas.DataFrame.to_excel — pandas 1.4.2 documentation[^].
ahmed_sa 2-May-22 6:56am    
my script is dynamically may be table changed so column will be changed i can't pass it static
so can you help me please
Richard MacCutchan 2-May-22 8:54am    
Then you will have to accept the column names that SQL provides. I do not understand how you can change them if you do not know what they are to start with.
ahmed_sa 2-May-22 9:47am    
sorry you are correct
so how to add header names as described in the documentation at pandas.DataFrame.to_excel

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900