How to copy FixedPath excel file to another path with same name combined with cu

  • How to copy Fixed Path excel file to another path with same name combined with current date and fill data on it ?

    I work on python script on sql server 2017

    I need to copy fixed path G:\ExportFiles\StudentExport.xlsx' to destination G:\ImportExportExcel with same name combined with current datetime

    and after that execute query to fill data on it .

    issue on my query below export data from sql server table to excel file static header as column names on table

    but i need to get column name on excel from fixed path template excel because header changed multi time dynamically


    declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
    declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
    CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
    insert into #FinalExportList(TableName,Cols)
    ('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')

    @TableName NVARCHAR(200)
    ,@PythonScript NVARCHAR(MAX) = N''
    ,@ExportFilePath NVARCHAR(MAX) = N''

    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"

    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

    issue on this script export data to excel static but i need header dynamic as i write

    because header changed multiple time

    attached image descripe my issue

    fixed file cop

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

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

  • "...How to copy Fixed Path excel file to another path with same name combined with current date and fill data on it ?...."

    I rename Excel files name to include the date in a DOS Batch file. I don't put in separators. MyFile.XLSX becomes MyFile_20220502_085515.XLSX

    Sounds like your request is a bit more complicated, but I don't quite follow what you need.

    • This reply was modified 1 year ago by  homebrew01.
  • my issue is if i have file already exist

    and this file have header


    then after using query

    it become studentid,Name

    How to keep header on excel file already exist without change

    to do that i try to change



    InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)

    but it give me data without header

    so what i do please


    change will be here

    SET @PythonScript = N'import shutil
    FullFilePath = ExcelFilePath+"StudentExport.xlsx"

Viewing 4 posts - 1 through 3 (of 3 total)

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