0

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:\WINDOWS\system32> $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:\WINDOWS\system32>

Improve this question
1
  • To export the PowerShell output to excel, you can use Export-Csv Here is the command to export the output to Excel $runquery | Export-Csv -Path "C:\path\to\output\sqllicensereport.csv" -NoTypeInformation Commented May 21, 2024 at 12:33

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the quick update but its not working: Result: "Length" "4096"
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

=

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.