470,632 Members | 1,583 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

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

1 Bit
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 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
  1. InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
I try to change InputDataSet.to_excel it to keep header on excel file StudentExport.xlsx without change as below

Expand|Select|Wrap|Line Numbers
  1. 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

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
  1. declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
  2. declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
  3. CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
  4. insert into #FinalExportList(TableName,Cols)
  5. values
  6. ('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')
  7.  
  8. DECLARE
  9. @TableName NVARCHAR(200)
  10. ,@SQL NVARCHAR(MAX) = N''
  11. ,@PythonScript NVARCHAR(MAX) = N''
  12. ,@ExportFilePath NVARCHAR(MAX) = N''
  13.  
  14.  
  15. SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END
  16.  
  17.  
  18. -- Just for testing purpose top 10 records are selected
  19. SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
  20. ,@TableName = TableName
  21. FROM #FinalExportList
  22.  
  23.  
  24. SET @PythonScript = N'import shutil
  25. FullFilePath = ExcelFilePath+"StudentExport.xlsx"
  26. shutil.copy(FixedPath,ExportPath)
  27. InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
  28. 'f
  29.  
  30. exec sp_execute_external_script
  31. @language = N'Python'
  32. ,@script = @PythonScript
  33. ,@input_data_1 = @SQL
  34. ,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200),@FixedPath NVARCHAR(MAX),@ExportPath NVARCHAR(MAX)'
  35. ,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
  36. ,@TableName = @TableName
  37. ,@FixedPath=@FixedPath
  38. ,@ExportPath=@ExportPath
  39. sql server table
  40.  
  41. CREATE TABLE [dbo].[students](
  42.  [StudentId] [int] NOT NULL,
  43.  [Name] [varchar](50) NULL,
  44.   CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED 
  45.  (
  46.  [StudentId] ASC
  47.  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  48.  ) ON [PRIMARY]
  49.  GO
  50.  INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')
3 Weeks Ago #1
0 8764

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Server Control | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.