Home > Back-end >  Powershell, Missing a property in a column after creating it when trying to Export-Csv
Powershell, Missing a property in a column after creating it when trying to Export-Csv

Time:08-15

So, I'm trying to create a csv file and then append result data to it. I feel like I'm just misusing something here so I figured I'd ask.

$headerText = ('"SamAccountName","Password",'   "`n")
New-Item C:\Users\Administrator\Desktop\test.csv | Add-Content -value $headerText

#stuff happens



Get-RandoPass| Export-Csv -NoTypeInformation -Path 'C:\Users\Administrator\Desktop\test.csv' -Append


Export-Csv : Cannot append CSV content to the following file: C:\Users\Administrator\Desktop\test.csv. The 
appended object does not have a property that corresponds to the following column: 
SamAccountName. To continue with mismatched properties, add the -Force parameter, and 
then retry the command.
At line:226 char:22
  ... mPassword | Export-Csv -NoTypeInformation -Path 'C:\Users\Administrator\Desktop\test.csv'  ...
                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : InvalidData: (SamAccountName:String) [Export-Csv], Inva 
   lidOperationException
      FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.Po 
   werShell.Commands.ExportCsvCommand

CodePudding user response:

Always, always create and test code paths that show results one step at a time, to make sure you are getting what you'd expect before moving to the next thing.

For example: Why are you doing this?

$headerText = ('"SamAccountName","Password",'   "`n")
'New-Item D:\temp\test.csv' | Add-Content -value $headerText

If you wrote and tested just this section, you'd see right away that this approach is not prudent.

$headerText = ('"SamAccountName","Password",'   "`n")
'New-Item D:\temp\test.csv' | 
Add-Content -value $headerText -WhatIf
#Results
<#
Add-Content : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its 
properties do not match any of the parameters that take pipeline input.
At line:2 char:31
  'New-Item D:\temp\test.csv' | Add-Content -value $headerText -WhatIf
                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : InvalidArgument: (New-Item D:\temp\test.csv:String) [Add-Content], ParameterBindingException
      FullyQualifiedErrorId : InputObjectNotBound,Microsoft.PowerShell.Commands.AddContentCommand
#>

These are two separate commands, and as you can see, the pipeline is not possible. So, until you address that, nothing else matters.

$headerText = ('"SamAccountName","Password",'   "`n")
New-Item -Path 'D:\Temp' -Name test.csv -ItemType File -Force
Add-Content -Path 'D:\temp\test.csv' -Value $headerText -WhatIf


    Directory: D:\Temp


Mode                 LastWriteTime         Length Name                                                                                                                
----                 -------------         ------ ----                                                                                                                
-a----         13-Aug-22     18:46              0 test.csv                                                                                                            
What if: Performing the operation "Add Content" on target "Path: D:\temp\test.csv".

Refactor suggestion

  • You are overcomplicating this. There is no need for New-Item at all, as the ADDS cmdlets will have headers. You should really just combine the commands and output using the Export-Csv cmdlet.
  • Don't use shorthand/aliases in scripts. It's bad practice and makes scripts hard to read and maintain. They are fine to use in throwaway code of interactive command line stuff.
  • Take advantage of Powershell natural line break to avoid long code lines for better readability.

I do not have ADDS handy, but something like this should get you what you are after.

Get-ADDistinguishedName -ADComputer $env:COMPUTERNAME | 
ForEach-Object{
    (($PSItem) -split ',' | 
    Where-Object {$PSItem -match '^DC'}) -join ','
}

Get-ADUser -Filter '*' -SearchBase $DNv | 
Select-Object SamAccountname, @{Name = 'Password';Expression = {Get-RandoPass}} | 
Export-Csv -NoTypeInformation -Path 'D:\temp\test.csv'

That formula is known as a PS calculated property. Take time to read up on them, as well as hash tables and PSCuistomObject to make your effort more efficient/proficient.

Combining output from multiple cmdlets is a common thing.

https://duckduckgo.com/?q='combine adds cmdlet output'&t=h_&ia=web

Update as per our comments below.

# Generate one random password
Add-Type -AssemblyName System.Web
[System.Web.Security.Membership]::GeneratePassword(15,2)
# Results
<#
_ruj5y1}jyHix1F
#>

# Generate one or more random passwords for user input
$PasswordCount = [Microsoft.VisualBasic.Interaction]::InputBox(
                    'Number of passwords needed:', 
                    'Generate Passwords', 
                    'Enter the number of passwords to generate here.'
                   )
Add-Type -AssemblyName System.Web
1..$PasswordCount | 
ForEach-Object {[System.Web.Security.Membership]::GeneratePassword(15,2)}
# Results
<#
!KE(b]2@h@s3iHt
?aZ[E()ElN-Q88R
liTKWM|UE/Dj&3;
aUP}r8n9bBJj]nY
PuQvOPs2f/QAi6?
#>

This can easily be turned into a proper function to call in other code.

Function New-RandomPassword 
{
    <#
    .Synopsis
       Short description
    .DESCRIPTION
       Long description
    .EXAMPLE
       Example of how to use this cmdlet
    .EXAMPLE
       Another example of how to use this cmdlet
    #>

    [CmdletBinding(SupportsShouldProcess)]
    [Alias('nrp')]

    Param
    (
    
    )

    Add-Type -AssemblyName  System.Drawing,
                            PresentationCore,
                            PresentationFramework,
                            System.Windows.Forms,
                            Microsoft.VisualBasic,
                            System.Web
    [System.Windows.Forms.Application]::EnableVisualStyles()
 
        $PasswordCount = [Microsoft.VisualBasic.Interaction]::
                  InputBox(
                            'Number of passwords needed:', 
                            'Generate Passwords', 
                            'Enter the number of passwords to generate here.'
                           )

        1..$PasswordCount | 
        ForEach-Object {[System.Web.Security.Membership]::GeneratePassword(15,2)}
}

Get-ADDistinguishedName -ADComputer $env:COMPUTERNAME | 
ForEach-Object{
    (($PSItem) -split ',' | 
    Where-Object {$PSItem -match '^DC'}) -join ','
}

Get-ADUser -Filter '*' -SearchBase $DNv | 
Select-Object SamAccountname, @{Name = 'Passwords';Expression = {New-RandomPassword}} | 
Export-Csv -NoTypeInformation -Path 'D:\temp\test.csv' -Append

Note:

This dialog box will show and ask for the number of passwords needed for every SamAccontname you pass to it. This is by design for any loop construct.

If you are after only asking the question once as input and for that number of passwords that are to be used for each SamAccountname passed in (which would be odd - as that is the same Passwords for everybody), then that function must be called standalone, the pass those results in the loop, not the function.

Meaning this:

$PasswordList = New-RandomPassword 

Get-ADUser -Filter '*' -SearchBase $DNv | 
Select-Object SamAccountname, @{Name = 'Passwords';Expression = {$PasswordList}} | 
Export-Csv -NoTypeInformation -Path 'D:\temp\test.csv' -Append

Update based on your comment:

Function Get-PasswordCount
{
    <#
    .Synopsis
       Short description
    .DESCRIPTION
       Long description
    .EXAMPLE
       Example of how to use this cmdlet
    .EXAMPLE
       Another example of how to use this cmdlet
    #>

    [CmdletBinding(SupportsShouldProcess)]
    [Alias('gpc')]

    Param
    (
    
    )

    Add-Type -AssemblyName  System.Drawing,
                            PresentationCore,
                            PresentationFramework,
                            System.Windows.Forms,
                            Microsoft.VisualBasic,
                            System.Web
    [System.Windows.Forms.Application]::EnableVisualStyles()
 
        [Microsoft.VisualBasic.Interaction]::
                  InputBox(
                            'Passwords per SamAccountname', 
                            'Select random Passwords per account', 
                            'Enter the number of passwords to use per SamAccountname.'
                           )
}

# Do not put this in a loop, run separately
($PasswordList = New-RandomPassword)
# Results
<#
{BCE!xP@-QZbbW[
>i4F5/fFR;NOJ^r
xo>%[email protected]
...
#>

# Do not put this in a loop, run separately
($SamAccountPasswordCount = Get-PasswordCount)
# Results
<#
5
#>

# This is in the loop
($SamAccountPasswordlist = $PasswordList | 
Get-Random -Count $SamAccountPasswordCount)
# Results
<#
(@rFcb|69SZR:n9
G7UiI?^bzu*Z!]1
bB>^])PxVI9q-VO
A6b_.J!gYF:7[o?
>i4F5/fFR;NOJ^r
#>


Get-ADUser -Filter '*' -SearchBase $DNv | 
Select-Object SamAccountname, @{Name = 'Passwords';Expression = {$SamAccountPasswordlist}} | 
Export-Csv -NoTypeInformation -Path 'D:\temp\test.csv' -Append
  • Related