question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked TomPhillips-1744 answered

How to prevent change columns name header when export SQL server table to excel for second time ?

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

code used for export from sql server to excel


 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




sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Maybe replace both of "ELSE C.name END Cols" with:

ELSE case C.name when 'Name' then 'StudentName' else C.name end END Cols


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

Welcome to Microsoft Q&A!
The members in this forum may not be much familiar with python. So it is not possible to identify possible problems on the python side.
You can try other methods of exporting tables.
Here is an article you can refer to. However, this document does not contain all the methods.
Hope this could give you some thoughts.

Best regards,
Seeya

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so how can help me solving this issues
post on python forums
or
sql server
also please which export methods another from sql server query
instead of iusing open rowset
because i already use it
so are another method export and impor data

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Your Python script simply outputs the table and the column names in the table.

The simplest answer to your question is to create a view which has the fields and column names you want, and pass that to your script instead of the actual table. Then you will control the headers and fields in the output.

 CREATE VIEW dbo.vw_students
 AS
 SELECT
     StudentId,
     [Name] as [StudentName]
 FROM dbo.students


Then

  declare @ObjectlisttoExport NVARCHAR(MAX)='dbo.vw_students'


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.