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')