issue excel column header changed if it exist before ?

  • 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

  • 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.

  • i updated my original post

    Question is how to prevent header from change on excel if already exist

  • 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.

  • 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

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply