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
Expand|Select|Wrap|Line Numbers
- InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
Expand|Select|Wrap|Line Numbers
- InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)
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
Expand|Select|Wrap|Line Numbers
- 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')