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)
    values
    ('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')

    DECLARE
    @TableName NVARCHAR(200)
    ,@SQL NVARCHAR(MAX) = N''
    ,@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"
    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

    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 month, 3 weeks ago by  homebrew01.
  • my issue is if i have file already exist

    and this file have header

    studentid,studentname

    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],index=False)

    to

    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"
    shutil.copy(FixedPath,ExportPath)
    InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
    '

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

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