Powershell - Export CSV file correctly

Question

I hope someone can help me with this. We want to see which computers have a HDD and SDD. I have an excel.csv of the computers. I import the computers. But when I export them I never see the csv or its incomplete. Can you tell what part of my script is incorrect. Thank you

$computers = Import-csv -path "C:\Temp\MediaType\Computers.csv" 
foreach ($computer in $computers) { 
    Write-Host "`nPulling Physical Drive(s) for $computer"
    if((Test-Connection -BufferSize 32 -Count 1 -ComputerName $computer -Quiet)){ 
        Invoke-Command -ComputerName $computer -ScriptBlock { 
            Get-WmiObject -Class MSFT_PhysicalDisk -Namespace root\Microsoft\Windows\Storage | Select-Object sort -Property PSComputerName, Model, SerialNumber, MediaType
            Export-Csv C:\Temp\devices.csv
        }
    }
}

Update: 11/11/2021 Thank you everyone for you help This script worked for me:

$ExportTo = "C:\Temp\devices.csv"
$computers = Import-csv -path "C:\Temp\Computers.csv"
{} | Select "ComputerName", "Status", "Model", "SerialNumber", "MediaType" | Export-Csv $ExportTo
$data = Import-csv -path $ExportTo 
foreach ($computer in $computers) {
$Online = Test-Connection -BufferSize 32 -Count 1 -ComputerName $computer.computer -Quiet
if ($Online) {
Write-Host $computer.computer " is Online"
$OutputMessage = Get-CimInstance -ClassName MSFT_PhysicalDisk -Namespace root\Microsoft\Windows\Storage -ComputerName $computer.computer | Select-Object -Property PSComputerName,@{N='Status';E={'Online'}}, Model, SerialNumber, MediaType
$data.ComputerName = $computer.computer
$data.Status = $OutputMessage.Status
$data.Model = $OutputMessage.Model
$data.SerialNumber = $OutputMessage.SerialNumber
$data.MediaType = $OutputMessage.MediaType
$data | Export-Csv -Path $ExportTo -Append -NoTypeInformation
} else {
Write-Host $computer.computer " is Offline"
$data.ComputerName = $computer.computer
$data.Status = "Offline"
$data.Model = ""
$data.SerialNumber = ""
$data.MediaType = ""
$data | Export-Csv -Path $ExportTo -Append -NoTypeInformation
}
}

Answer 1

Continuing from my comment. . . as is, you would be exporting the results to the remote machine. That's if it was piped properly. You're currently missing a pipe (|) before Export-Csv.

Also, there's no need to invoke the command, as Get-WMIObject has a parameter for remote computers: -ComputerName. It's also a deprecated cmdlet that has been replaced by Get-CimInstance.

$ExportTo  = "C:\Temp\devices.csv" 
$computers = Import-csv -path "C:\Temp\MediaType\Computers.csv" 
    foreach ($computer in $computers) 
    { 
        Write-Host "`nPulling Physical Drive(s) for $computer" 
        if (Test-Connection -BufferSize 32 -Count 1 -ComputerName $computer -Quiet) {
         
            Get-CimInstance -ClassName MSFT_PhysicalDisk -Namespace root\Microsoft\Windows\Storage -ComputerName $computer | 
                Select-Object -Property PSComputerName, Model, SerialNumber, MediaType | 
                Export-Csv -Path $ExportTo -Append -NoTypeInformation

        } 
    }

Side Note: Get-CimInstance accepts an array of strings, meaning you can pass the entirety of $Computers to it. This should allow it to perform the the query in parallel, vs serial (one at a time):

$ExportTo  = "C:\Temp\devices.csv" 
$computers = Import-csv -path "C:\Temp\MediaType\Computers.csv" 

Get-CimInstance -ClassName MSFT_PhysicalDisk -Namespace root\Microsoft\Windows\Storage -ComputerName $computers -ErrorAction SilentlyContinue | 
    Select-Object -Property PSComputerName, Model, SerialNumber, MediaType |
    Export-Csv -Path $ExportTo -Append -NoTypeInformation

---

Performing queries one at a time doesn't necessarily mean it's bad. You can actually have more control over the control of flow for your script.

EDIT: Following up on your comment...you're no longer using your if statement to check if the computer is online before connecting. So given that you keep the if statement, and add an else condition, you can create a calculated property to add another property to export of Status. Then, you can pass it a value of Online, or Offline depending on if the machine is online or not:

$ExportTo  = "C:\Temp\devices.csv" 
$computers = Import-csv -path "C:\Temp\MediaType\Computers.csv" 
    foreach ($computer in $computers) 
    { 
        if (Test-Connection -BufferSize 32 -Count 1 -ComputerName $computer -Quiet) {
            Write-Host -Object "`nPulling Physical Drive(s) for $computer" 
            Get-CimInstance -ClassName MSFT_PhysicalDisk -Namespace root\Microsoft\Windows\Storage -ComputerName $computer | 
                Select-Object -Property PSComputerName,@{N='Status';E={'Online'}}, Model, SerialNumber, MediaType | 
                Export-Csv -Path $ExportTo -Append -NoTypeInformation -Force
        }
        else {
            Write-Host -Object "`n$Computer is Offline"
           [PSCustomObject]@{PSComputerName=$Computer;Status='Offline'} | Export-Csv -Path $ExportTo -Append -Force
        } 
    }

Also:

1. Always remember that even if you can ping a machine, it doesn't mean you can connect to it.
2. This can be mitigated by using a CIM Session, or PSSession depending on the type of commands you're running.

Answer 2

To specifically answer the question:

How do I correctly export a CSV file (use Export-Csv)?

You might want to read about PowerShell pipelines and PowerShell cmdlets.
Basically, a cmdlet is a single command that participates in the pipeline semantics of PowerShell. A well written cmdlet is implemented for the Middle of a Pipeline which means that it processes ("streams") each individual item received from the previous cmdlet and passes it immediately to the next cmdlet (similar to how items are processed in an assembly line where you can compare each assembly station as a cmdlet).

To better show this, I have created an easier minimal, complete and verifiable example (MVCE) and replaced your remote command (Invoke-Command ...) which just an fake [pscustomobject]@{ ... } object.
With that;

• I have used Get-Content rather then Import-Csv as your example suggest that Computers.csv is actually a text file which list of computers and not a Csv file which would require a (e.g. Name) header and using this property accordingly (like $Computer.Name).
• To enforce the pipeline advantage/understanding, I am also using the ForEach-Object cmdlet rather than the foreach statement which is usually considered faster but this is probably not the case here as for the foreach statement it is required to preload all $Computers into memory where a well written pipeline will immediately start processing each item (which in your case happens on a remote computer) while still retrieving the next computer name from the file.

Now, coming back on the question "How do I correctly export a CSV file" which a better understanding of the pipeline, you might place Export-Csv within the foreach loop::

Get-Content .\Computers.txt |ForEach-Object {
    [pscustomobject]@{
        PSComputerName = $_
        Model          = "Model"
        SerialNumber   = '{0:000000}' -f (Get-Random 999999)
        MediaType      = "MydiaType"
    } |Export-Csv .\Devices.csv -Append
}

As commented by @lit, this would require the -Append switch which might not be desired as every time you rerun your script this would append the results to the .\Devices.csv file.
Instead you might actually want do this:

Get-Content .\Computers.txt |ForEach-Object {
    [pscustomobject]@{
        PSComputerName = $_
        Model          = "Model"
        SerialNumber   = '{0:000000}' -f (Get-Random 999999)
        MediaType      = "MydiaType"
    }
} |Export-Csv .\Devices.csv

Note the differences: the Export-Csv is placed outside the loop and the -Append switch is removed.

Explanation
As with e.g. the ForEach-Object cmdlet, the Export-Csv cmdlet has internally Begin, Process and End blocks.

• In the Begin block (which runs when the pipeline is started), the Export-Csv cmdlet prepares the csv file with a header row etc. and overwrites any existing file.
• In the Process block (which runs for each item received from the pipeline) it appends each line (data record) to the file.