none
Writing data output to azure storage account RRS feed

  • Question

  • Hi All.

     

    I have written a script that will do the following.

    Export data to 2 csv files.

    Combine the csv files onto a single excel workbook.

    Password protect the files and save the file onto C:\file.

     

    Now I want to change the script such that it runs within an azure setup, running the code from a pipeline as opposed to my local machine, the excel file should also be saved on an azure storage account.

     

     

    $csvFilePath = "D:\extract\A.csv"  
    $excelFilePath = "D:\extract\B.xls"
    $csvFilePath2 = "D:\extract\C.csv"  
    $results | export-csv  $csvFilePath   -NoTypeInformation -Force
    $results2 | export-csv  $csvFilePath2   -NoTypeInformation -Force
    $filepath='D:\extract\*'
    $excelfile = "D:\extract\Extract_ $(get-date -format ddMMyyyy).xls" 
    function Add-Worksheet{
        Param(
            $filepath
        )
            Try{
                Write-Host $_.Fullname -ForegroundColor green  
                $ws=$wb.Sheets.Add($mv,$mv,$mv,$filepath)
                $ws.Cells.EntireColumn.AutoFit()
            }
            Catch{
                Write-Host "File cannot be loaded: $filepath" -ForegroundColor red
            }
    }
    $mv=[System.Reflection.Missing]::Value 
    $Excel = New-Object -Com Excel.Application
    $Excel.visible=$false
    $wb=$Excel.Workbooks.Add()
    Get-ChildItem $filepath -Include *.csv |
        Sort-Object Name -desc|
        ForEach-Object{
            Add-Worksheet $_.Fullname
        }
    $wb.Worksheets.Item('Sheet1').Delete()
    $xlExcel8 = 56
    $Excel.DisplayAlerts = $false;
    $wb.SaveAs($excelfile,$xlExcel8,"password") 
    $wb.Close()
    $excel.quit() 
    $excel = $null
    $Attach += $excelFilePath
    $Attach +=$csvFilePath 
    #Delete the old csv files.
    Get-ChildItem $filepath -include *.csv -Recurse | Remove-Item


    Wednesday, October 14, 2020 10:48 AM