How to prevent change columns name header when export sql server table to exce?

  • 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

    when export data to excel i use script below

    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

    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

     

     

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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