Maybe replace both of "ELSE C.name END Cols" with:
ELSE case C.name when 'Name' then 'StudentName' else C.name end END Cols
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
Maybe replace both of "ELSE C.name END Cols" with:
ELSE case C.name when 'Name' then 'StudentName' else C.name end END Cols
Hi @ahmed salah ,
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
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'