Powershell CSV Variable Issue

Question

I am having a problem with passing a variable into a CSV. I need to pass an email for a spreadsheet showing all skills. It's the same email for each skill. I just want the $email to populate my csv. It does not pass and only shows the $email instead of the [email protected] in the column.

I am new at powershell so any guidance is greatly appreciated. Thank you.

------------here is my script-------------------------

Add-Content -Path C:\temp\test.csv -Value '"User Name","Skill Name","Level"'

$Email = "[email protected]"

$agent = @(

}

'"$Email","T1","4"'

'"$Email","T2","6"'

'"$Email","T3","7"'

'"$Email","Training","1"'

'"$Email","Supervisor","8"'

)

$agent | foreach { Add-Content -Path C:\temp\temp.csv -Value $_ }

Next time, use a code block to make that easier to read. I think I'm seeing a ' followed by a " at the beginning of each line? If so, variable expansion won't work, you need to use double-quote to start your string, then escape the ones inside by using two double-quotes "". So it'd be """$Email"",""T1"",""4""" for line 1. — DarkMoon
– DarkMoon, Commented Sep 25, 2021 at 22:42

Walter Mitty · Accepted Answer · 2021-09-26 11:54:25Z

1

Because you are new to Powershell, I'm showing you two alternative ways of framing the problem. These might help you get used to some of the features of powershell.

$Email = '[email protected]'

$mytext = @"
"User Name","Skill Name","Level"
"$Email","T1","4"
"$Email","T2","6"
"$Email","T3","7"
"$Email","Training","1"
"$Email","Supervisor","8"
"@


$mytext | Out-file Mycsv.csv

Here, I just set up the Email variable, then create one big here string with the header and the five data records in it. Because I used double quotes on the here string, the variable $Email will be detected inside of it. A here string with single quotes would not have behaved correctly.

Then, I pass $mytext through a pipeline one line at a time, and Out-file collects all this into a file.

Here's the second approach:

$Email = '[email protected]'

$myarray = @(
       [PsCustomobject]@{"User Name" = $Email; "Skill Name" = "T1"; "Level" = 4}
       [PsCustomobject]@{"User Name" = $Email; "Skill Name" = "T2"; "Level" = 6}
       [PsCustomobject]@{"User Name" = $Email; "Skill Name" = "T3"; "Level" = 7}
       [PsCustomobject]@{"User Name" = $Email; "Skill Name" = "Training"; "Level" = 1}
       [PsCustomobject]@{"User Name" = $Email; "Skill Name" = "Supervisor"; "Level" = 8}
)

$myarray | Export-Csv myothercsv.csv

Here, I set up the variable Email, then create an array of custom objects, each with the same named properties.

Then I pass the array through a pipeline to Export-Csv which converts everything to Csv format. It's worth noting that Export-Csv V5 throws in a line that says #TYPE in it. This is not hard to eliminate, using the notype parameter if desired. It's also worth noting that the double quotes in the output file were all added in by Export-csv, and weren't copies of the double quotes in the script.

Edit. Pipelines are a surprisingly easy and flexible way of getting things done in powershell. For this reason, cmdlets like Out-File and Export-Csv are built to work well with pipelines supplying a stream of input. A lot of loop control, initialization, and finalization busy work is being handled behind the scenes by PS.

edited Sep 26, 2021 at 11:54

answered Sep 26, 2021 at 3:13

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

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

4 Comments

DarkMoon Over a year ago

I was tempted to suggest #2 myself; it's easier to scale when you have a larger and more complex dataset. Also note that Export-Csv only puts the type information line in by default in Windows PowerShell (v5 and earlier); PowerShell Core (v6+) doesn't do that anymore, but it'll still take the -NoTypeInformation parameter.

Walter Mitty Over a year ago

Thanks for clarifying the deal about -notype. I'm still running V5. You make a good point about scaling. The larger the scale gets, the more you'll gain by working with a set of objects rather than text that represnts data.

DarkMoon Over a year ago

I keep trying to use the newer PowerShell, but having to come back because core modules still require Windows PowerShell (AzureAD, ExchangeOnlineManagement, MicrosoftTeams, etc).

user16997949 Over a year ago

Thanks Walter and Dark. I ended up all ready doing something similar to Walters first suggestion. It's good to know about #2 for something much more scaleable in the future. Really appreciate the info!

Collectives™ on Stack Overflow

Powershell CSV Variable Issue

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related