Azure – How to send output of Powershell script to CSV

azurecsvpowershell

I have been given the following script to connect to an Azure subscription, get all the certificates and output some of the certificate properties.
The script works fine,
What I am trying to do is send the output into a CSV file so I can sort by certificate expiry-date (for example) and manipulate the output into spreadsheet reports (for instance).

Powershell Script

Set-AzureSubscription 'MySubscription'
Select-AzureSubscription 'MySubscription'

$enc = [system.Text.Encoding]::UTF8
$azcerts = Get-AzureService | Get-AzureCertificate
foreach ($azcert in $azcerts) {      
    $bytes = $enc.GetBytes($azcert.data) 
    $cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate -ArgumentList @(,$bytes)    
    $subjectstring = $cert.Subject
    $expirydate = $cert.GetExpirationDateString()
    $servicename = $azcert.ServiceName
    $thumprint = $azcert.Thumbprint
    $cnstring = $subjectstring -replace "(CN=)(.*?),.*",'$2'    
    Write-Output "$servicename, $cnstring, $expirydate, $thumprint"    }

What I have tried…

When I try to replace the last line in the for-each loop with this I get a useless CSV file with only one column "#TYPE System.String"

"$servicename, $cnstring, $expirydate, $thumprint" | Export-Csv "outputfile.csv" -append -NoTypeInformation

Best Solution

You've made you're own CSV lines in the loop so you can just write to disk:

"$servicename, $cnstring, $expirydate, $thumprint" | Out-File -FilePath output.csv -Append -Encoding ASCII

before the loop write your headers:

"servicename, cnstring, expirydate, thumprint" | Out-File -FilePath output.csv -Encoding ASCII

For Export-Csv or ConvertTo-Csv to work you'd need to pipe a collection of objects with these fields as properties. You could make such a collection using New-Object:

New-Object -TypeName PsObject -Property @{servicename=$servicename;cnstring=$cnstring <ETC...>}

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.