Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I work with SQL Server 2017, I have a Python script to export SQL Server table students to an Excel file.

How to modify InputDataSet.to_excel to not change header , excel file path G:\ImportExportExcel when excel file exist before .

What I have tried:

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], StudentName')

 DECLARE 
        @TableName NVARCHAR(200)
        ,@SQL NVARCHAR(MAX) = N''
        ,@PythonScript NVARCHAR(MAX) = N''
        ,@ExportFilePath NVARCHAR(MAX) = N''

        declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
    
  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'
FullFilePath = ExcelFilePath+TableName+".xlsx"
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)'
      ,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
      ,@TableName = @TableName
tables structure with data

 CREATE TABLE [dbo].[students]
    (
         [StudentId] [int] NOT NULL,
         [StudentName] [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], [StudentName]) VALUES (1, N'ahmed')
Posted
Updated 26-Apr-22 3:47am
Comments

1 solution

This is yet another repost of the same question which I answered here How to prevent change columns name header when export SQL server table to excel for second time ?[^]

It was also answered here : issue excel column header changed if it exist before ? – SQLServerCentral Forums[^] which was also a repost on that forum. You also ignored the answer there, which is not going to make you popular amongst the volunteers on either site.

One "trick" you could use is to start your data from Row 2 and set the header to false - but you will need extra code to determine if the file already exists or not (N.B. Code untested)
Python
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], startrow=2,index=False,Header=False)
 
Share this answer
 
Comments
ahmed_sa 27-Apr-22 0:11am    
thank you for reply
really i need to solve issue but can't know what i do
i need to overwrite excel file already exist without change header
CHill60 27-Apr-22 3:57am    
I have offered you several means of achieving this. Which ones have you tried and what went wrong?
ahmed_sa 27-Apr-22 4:05am    
I Need overwrite excel sheet without change header
as example
i have excel sheet have studentid and student name
when export i will get data for same header
CHill60 27-Apr-22 4:09am    
I know what you want to do, you keep repeating it. I asked you which of the potential solutions offered have you tried and what went wrong. I will also state again the obvious solution which is "Don't change the name of the column"

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