Click here to Skip to main content
15,396,257 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to prevent change columns name header when export sql server table to excel file for second time?

I work on sql server 2017 i have script python export SQL server table students to excel path

When run script below excel file exported success with data and headers for first time only

when use script below used for export students table to excel for second time header changes from student name to Name

so my issue How to prevent column names header from changes when export students table to excel for second time

so my table students below

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]
GO
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (1, N'ahmed')
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (2, N'eslam')
INSERT [dbo].[students] ([StudentId], [StudentName]) VALUES (3, N'mohamed')
GO


and exactly export students table to excel on path G:\ImportExportExcel

as studentid,studentname

Export headers for first time without any issue

my issue done when change column name from studentname to Name on table students

and export again it become studentid,Name on excel file path

so how to prevent header columns name from change if file exist on path G:\ImportExportExcel

expected result

StudentIdStudentName
1        ahmed
2        eslam
3        mohamed


What I have tried:

when export data to excel i use script below

declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @SchemaName NVARCHAR(MAX)=''
declare @ObjectlisttoExport NVARCHAR(MAX)='dbo.students'
  


  

 

 SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END
 
 DECLARE @ValidPath TABLE (ValidPathCheck BIT)
 
INSERT @ValidPath
EXEC sp_execute_external_script
@language =N'Python',
@script=N'
import pandas as pd
d = os.path.isdir(ExportFilePath)
OutputDataSet = pd.DataFrame([d],columns=["Filename"])'
,@params = N'@ExportFilePath NVARCHAR(MAX)'
,@ExportFilePath = @ExportPath
 

DROP TABLE IF EXISTS #ExportTablesList, #FinalExportList
  
CREATE TABLE #ExportTablesList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
 
--Get the list of objects to be exported
INSERT #ExportTablesList (Cols,TableName)
SELECT  CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp','decimal','bit','int','bigint')
               THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME))
          ELSE C.name END Cols  -- To cover poor data type conversions b/n Python & SQL Server
  ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName
FROM Sys.tables T
JOIN sys.columns C
ON T.object_id = C.object_id
JOIN sys.types TY
ON C.[user_type_id] = TY.[user_type_id]
WHERE Schema_name(T.schema_id) IN (SELECT value FROM STRING_SPLIT(@SchemaName, ','))
-- Ignore the datatypes that are not required to be exported
AND TY.name NOT IN ('geography','varbinary','binary','text', 'ntext', 'image', 'hierarchyid', 'xml', 'sql_variant') 
  
INSERT #ExportTablesList (Cols,TableName)
SELECT  CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp','decimal','bit','int','bigint')
               THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME))
          ELSE C.name END Cols  -- To cover poor data type conversions b/n Python & SQL Server
  ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName
FROM Sys.tables T
JOIN sys.columns C
ON T.object_id = C.object_id
JOIN sys.types TY
ON C.[user_type_id] = TY.[user_type_id]
WHERE CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) IN (SELECT value FROM STRING_SPLIT(@ObjectlisttoExport, ','))
-- Ignore the datatypes that are not required to be exported
AND TY.name NOT IN ('geography','varbinary','binary','text', 'ntext', 'image', 'hierarchyid', 'xml', 'sql_variant') 
  
--Dedup of object list
;WITH dedup
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY TableName,Cols ORDER BY Cols) Rn FROM #ExportTablesList
)
DELETE FROM dedup
WHERE Rn > 1
 
--Forming columns list as comma separated
SELECT TableName,IDENTITY(INT,1,1) AS TableCount
    , STUFF(
        (
        SELECT ', ' + C.Cols
        From #ExportTablesList As C
        WHERE C.TableName = T.TableName
        FOR XML PATH('')
        ), 1, 2, '') AS Cols
INTO #FinalExportList
From #ExportTablesList As T
GROUP BY TableName
  ----select * from #FinalExportList
  
DECLARE @I INT = 1
       ,@TableName NVARCHAR(200)
       ,@SQL NVARCHAR(MAX) = N''
       ,@PythonScript NVARCHAR(MAX) = N''
       ,@ExportFilePath NVARCHAR(MAX) = N''
 

   
 -- Just for testing purpose top 10 records are selected
SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
      ,@TableName = TableName
FROM #FinalExportList WHERE TableCount = @I
  
   
SET @PythonScript = N'
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)'
   
  --- print @PythonScript
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
Posted
Updated 24-Apr-22 21:36pm
Comments

1 solution

Quote:
my issue done when change column name from studentname to Name on table students
If you are changing the name of the column then obviously the header of the column will also change to match the name of the column. You are exporting the "raw" tables. If you want the excel workbook to have headers that are different to the column names you either have to export them from queries that alias the column e.g.
SQL
select [StudentId], [Name] as [StudentName] from students
or go into the workbook afterwards and change the name of the header.

Or dare I say it - don't change the name of the column on the table in the first place.
   
Comments
Maciej Los 25-Apr-22 14:01pm
   
5ed!

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