Powershell - Using ConvertFrom-csv

Question

I'm brand new to Powershell. I have a variable that contains comma separated values. What I want to do is read each entry in the csv string variable, and assign it to a variable. I am using ConvertFrom-csv to separate the data with headers.

How can I assign each value to a variable, or even better, use ConvertTo-csv to create a new csv string which only has, for example, columns 2/3/6/7 in it?

I would ultimately want to write that data out to a new csv file.

Here is my test code:

#Setup the variable
$Data = "test1,test2,test3,1234,5678,1/1/2021,12/31/2021" 
 
$Data | ConvertFrom-csv -Header Header1,Header2, Header3, Header4, Header5, Header6, Header7
 
# Verify that an object has been created.
$Data | 
    ConvertFrom-csv -Header Header1,Header2, Header3, Header4, Header5, Header6, Header7 |
    Get-Member


#Show header1
    Write-Host "--------Value from $Data----------------------------------------"
    $Data[0]   #doesn't work, only displays the first character of the string
    Write-Host "-----------------------------------------------------------------"

Santiago Squarzon · Answer 1 · 2021-11-05T05:00:10.177

How can I assign each value to a variable, or even better, use ConvertTo-Csv to create a new csv string which only has, for example, columns 2/3/6/7 in it?

This is one way of automating this:

# Define the CSV without headers
$Data = "test1,test2,test3,1234,5678,1/1/2021,12/31/2021"

# Set the number of headers needed
$headers = $Data.Split(',') | ForEach-Object -Begin { $i = 1 } -Process {
    "Header$i"; $i++
}

# Set the desired columns we want
$desiredColumns = 2,3,6,7 | ForEach-Object { $_ - 1 } | ForEach-Object {
    $headers[$_]
}

# Convert to CSV and filter by Desired Columns
$Data | ConvertFrom-Csv -Header $headers | Select-Object $desiredColumns

Result

Header2 Header3 Header6  Header7   
------- ------- -------  -------   
test2   test3   1/1/2021 12/31/2021

Result as CSV

$Data | ConvertFrom-Csv -Header $headers |
Select-Object $desiredColumns | ConvertTo-Csv -NoTypeInformation

"Header2","Header3","Header6","Header7"
"test2","test3","1/1/2021","12/31/2021"

Thanks Santiago! That works great. One more question, is there any way to remove the quotation marks from the data being written to the new csv string? — HurricaneCoffee, Nov 05 '21 at 06:03
@HurricaneCoffee [ConvertTo-CsvNoQuotes](https://stackoverflow.com/a/60681762/9898643) — Theo, Nov 05 '21 at 12:55

score 1 · Accepted Answer · answered Nov 05 '21 at 11:40

Let me suggest a different approach. If you use ConvertFrom-Csv and assign the result of a variable ($data), this will be an array of Custom Objects. You can run this through a loop that steps through the elements of the array , one at a time, and then through an inner loop that steps through the properties of each object one at a time, setting a variable with the same name as the field header and the same value as the current record's value.

I don't have code that does exactly what you want. But I'm including code that I wrote a few years back that does something similar only using Import-Csv instead of ConverFrom-Csv.

   Import-Csv $driver | % {
       $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
       Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)} 
   }

Focus on the first inner loop. Each property of the current object will have a name that came from the header and a value that came from the current record of the Csv file. You can ignore the line that says ExpandString. That's just what I choose to do with the variables once they have been defined.

Powershell - Using ConvertFrom-csv

2 Answers2