April 26, 2022 at 2:50 am
I work with SQL Server 2017, I have a Python script to export SQL Server table students to an Excel file.
When I run script below, the data is successfully exported from SQL Server to the Excel file, with data and headers for the path G:\ImportExportExcel.
If I change the column name from student name to Name on student table and export again, the column student name in the Excel is changed to Name.
So I don't need to change header if header already exists
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')
The expected result in the Excel file after change student name to name should be
StudentId StudentName
1 ahmed
Code used for exporting from SQL Server table to Excel:
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
Question is how to prevent header from change on excel if already exist
meaning suppose i export data from student table and excel file generating with studentid and student name
after that i change student name to name on student table
and export again then it must not change header on excel exist
but my issue here is change already exist excel file from studentname to name
and i don't need that
Expected result must be studentid and studentname
April 26, 2022 at 5:52 am
I cannot see a question here.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2022 at 7:38 am
i updated my original post
Question is how to prevent header from change on excel if already exist
April 26, 2022 at 7:57 am
I've read through your code a couple of times and I'm still not sure exactly what it is doing, but I can offer some input.
You appear to be building a SQL query using 'Cols', whose definition I cannot see. However, I presume that the column names in cols are inherited directly from the column names in your students table, so you are ending up wth
SELECT StudentId, Name FROM dbo.Student;
(where cols = "StudentId, Name")
If you replace the text ", Name" with ", StudentName = Name" in cols, your query ends up as
SELECT StudentId, StudentName = Name FROM dbo.Student;
and hopefully that cascades through to your Excel file.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2022 at 11:39 am
thank you for reply
i need any one help me
i will explain issue
firstly i use script below to export data from sql server to excel
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
after that excel file student created on path G:\ImportExportExcel
studentid studentname
1 ahmed
Mow i changed column student name to Name on table student
and export again
here issue displayed because it create excel file with
studentid and Name
studentid Name
1 ahmed
and this header wrong because it remove old header
are there are any solution to prevent modify header on excel file from student name to Name
so are there are any solution to prevent modify student name column header to Name
can you help me please ?
i think i need to modify code on SET @PythonScript = N'
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)'
to not overwite header so how to do that please
April 26, 2022 at 1:22 pm
Other than thanking me, your post appears to have ignored mine.
Please describe why the idea I suggested is not applicable in your case.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply