Azure Question posted in
The official documentation to get you started can be found here.

Azure – Powershell : Format output from database table to CSV File

When I try to run the powershell command below which is not giving the CSV table format instead its just a string (attached results Screenshot), I need help in table format with these results.

        $scriptCode = @'
        $query = "use test_MO; 
        SELECT Quantity, 'License Metric', 'Product Name', 'Manufacturer (Article)', 'Manufacturer article number (Article)','Article description (Article)','Purchase requisition number','Order number','License status','Maintenance end date'
        FROM licenses lic
        "

        $sql_job = Invoke-Sqlcmd  -ServerInstance $servername -Query "$Query"
        $sql_job
'@

        Remove-Item -Path "sqllicensereport.ps1" -Force
        New-Item -Path . -Name "sqllicensereport.ps1" -ItemType "file" -Value "$scriptCode"  | out-null
        $runquery = Invoke-AzVMRunCommand -ResourceGroupName $ServerRGName  -VMName $ServerName -CommandId 'RunPowerShellScript' -ScriptPath                        "sqllicensereport.ps1" -Parameter @{servername = $servername}
        $temp = $runquery.Value[0].Message | Format-Table -Auto > $outputFile

Note: Export-CSV command but got the length (i put here in the comments, could anyone help here

Output now:

enter image description here

Results Expected from my end in below CSV sample:

enter image description here

Attaching the Output File and I would like to convert it to CSV (row and column format)

PS C:WINDOWSsystem32> $run

Value[0] :
Code : ComponentStatus/StdOut/succeeded
Level : Info
DisplayStatus : Provisioning succeeded
Message : : Microsoft Windows Server Datacenter Core (Single Language)
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-30385
Article description (Article) : Microsoft Windows Server Datacenter 2 CORE Single Language Software Assurance
MPSA
Purchase requisition number :
Order number :
License status : Active with Support
Maintenance end date : 2027-01-31

Quantity : 100
License Metric : Microsoft Core Infrastructure Server Suite Datacenter Core
Product Name : Microsoft Core Infrastructure Server Suite Datacenter Core
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-30467
Article description (Article) : Microsoft Core Infrastructure Server Datacenter 2 CORE mul License incl.
Software Assurance MPSA
Purchase requisition number :
Order number :
License status : Active with Support
Maintenance end date : 2027-01-31

Quantity : 100
License Metric : Microsoft Core Infrastructure Server Suite Datacenter Core
Product Name : Microsoft Core Infrastructure Server Suite Datacenter Core
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-30467
Article description (Article) : Microsoft Core Infrastructure Server Datacenter 2 CORE mul License incl.
Software Assurance MPSA
Purchase requisition number :
Order number :
License status : Active with Support
Maintenance end date : 2027-01-31

Quantity : 100
License Metric : Microsoft Core Infrastructure Server Suite Datacenter Core
Product Name : Microsoft Core Infrastructure Server Suite Datacenter Core
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-90044
Article description (Article) : Microsoft Core Infrastructure Server Suite Datacenter 16 Core mul Software
Assurance MPSA
Purchase requisition number : RGBR0010499
Order number : PCSL0000467
License status : Active with Support
Maintenance end date : 2025-01-31

Quantity : 100
License Metric : Microsoft Windows Server Datacenter Core (SA)
Product Name : Microsoft Windows Server Datacenter Core
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-90058
Article description (Article) : Microsoft Windows Server Datacenter 16 Core incl. Azure Hybrid Benefit mul
Software Assurance MPSA
Purchase requisition number :
Order number :
License status : Active with Support
Maintenance end date : 2025-01-31

Quantity : 100
License Metric : Microsoft Windows Server Standard Core (SA)
Product Name : Microsoft Windows Server Standard Core
Manufacturer (Article) : Microsoft
Manufacturer article number (Article) : AAA-90065
Article description (Article) : Microsoft Windows Server Standard 16 Core mul Software Assurance MPSA
Purchase requisition number :
Order number :
License status : Active with Support
Maintenance end date : 2025-01-31

Value[1] :
Code : ComponentStatus/StdErr/succeeded
Level : Info
DisplayStatus : Provisioning succeeded
Message :
Status : Succeeded
Capacity : 0
Count : 0

PS C:WINDOWSsystem32>

Tags:

2

Answers


  1. 0

    You are using the wrong command.
    The command to get a CSV is Export-Csv

    $temp = $runquery.Value[0].Message |
    Export-Csv -LiteralPath $outputFile -NoTypeInformation

    if you are using Powershell 7 you can remove -NoTypeInformation

    Login or Signup to reply.
  2. 0

    Powershell : Format output from database table to CSV File

    Here is a PowerShell script to convert the data stored in the parameter $outputMessage into an Excel format.

    $outputMessage | ConvertFrom-String -Delimiter ":" -PropertyNames "Property", "Value" | Export-Csv -Path "vmdata1.csv" -NoTypeInformation
Import-Csv -Path "vmdata1.csv" | Export-Excel -Path "venkat.xlsx" -AutoSize -BoldTopRow

    Output:

    enter image description here

    =

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search