Saying my CSV doesn't have a column when it does...

[Steve Ayers Participant Points: 0Rank: Member]

I'm trying to format some old dsquery data into a spreasheet, re-exporting data isn't an option.

the data looks something like

attribute1: blabla
attribute2: blabla
attribute3: blabla

I keep getting:

Export-CSV : Cannot append CSV content to the following file: C:\Users\???\Desktop\File002.csv. The appended object does 
not have a property that corresponds to the following column: autoReplyMessage. To continue with mismatched properties, add the 
-Force parameter, and then retry the command.
At C:\Users\????\Desktop\Format\Format Files.ps1:62 char:54
+ ... ject -TypeName PSObject -Property $props | Export-CSV $output -append
+                                                ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (autoReplyMessage:String) [Export-Csv], InvalidOperationException

Here is my code:

$dir = "C:\Users\Steven.ayers\Desktop\Raw Data"
$output = "$home\Desktop\File002.csv"
$headers=@()
$props=@{}
(get-content $dir\Headers.txt | out-string).split("`n") | % { if ($_ -ne ""){$props.Add(($_).Trim(),$null) }}
New-Object -TypeName PSOBJECT -Property $props | Export-CSV $output -append
gci $dir | % {

$objects = (get-content $_.FullName | out-string) -split "objectClass: top"
"Working on $($_.Name)"

foreach ($object in $objects)
{
    if ($object -ne "")
    {
    $attributes = $object.Split("`n")
    
    foreach ($attr in $attributes)
    {
        if ($attr -ne "")
        {
        $attr = $attr -split ": "
        #Write-host $attr -ForegroundColor Red
        $MatchedValues = $attributes | ? {$_ -like "$($Attr[0]): *"}
        #Write-host $MatchedValues -ForegroundColor Green
            if ($MatchedValues.Count -gt 1)
            {
            #"Duplicates!"
                $NewValue = $null
                $NewValue += "$($Attr[0]): "
    
                    0..(($MatchedValues.Count) -1) | % {
    
                        $NewValue += "$((($MatchedValues[$_]).Trim() -split ": ")[1])"
                        if ($_ -lt (($MatchedValues.Count)-1))
                        {
                        $NewValue += "; "
                        }
                        $attributes = $attributes -replace $MatchedValues[$_], $null
                    }
                
            }
        }
    
    
    }
    
    $attributes = (($attributes | ? { $_ -ne ""}) + $NewValue).Trim()
    #$attributes
    $props=@{}

    foreach ($attr in $attributes)
    {
        if ($attr -ne "")
        {
            $attr = $attr -split ": "
            $props.Add(($Attr[0]).Trim(),$Attr[1])
        }
        
        
    }
    New-Object -TypeName PSObject -Property $props | Export-CSV $output -append
    }

}
}

content of headers.txt:
This has been pulled out of all of the dsquery text files, by putting into an excel, and using "Text to Columns" and dividing all lines by ": " and taking the unique values.

accountExpires
adminCount
ADsPath
altRecipient
authOrigBL
autoReplyMessage
badPasswordTime
badPwdCount
Block 3544 
c
cn
co
codePage
comment
company
countryCode
deletedItemFlags
deliverAndRedirect
department
description
directReports
displayName
displayNamePrintable
distinguishedName
dLMemDefault
dLMemRejectPerms
dLMemSubmitPerms
dSCorePropagationData
employeeID
extensionAttribute1
extensionAttribute10
extensionAttribute11
extensionAttribute12
extensionAttribute13
extensionAttribute14
extensionAttribute15
extensionAttribute2
extensionAttribute3
extensionAttribute4
extensionAttribute5
extensionAttribute9
facsimileTelephoneNumber
garbageCollPeriod
givenName
gPLink
gPOptions
homeDirectory
homeDrive
homeMDB
homeMTA
homePhone
info
initials
instanceType
l
lastKnownParent
lastLogoff
lastLogon
lastLogonTimestamp
legacyExchangeDN
lockoutTime
logonCount
logonHours
mail
mailNickname
managedObjects
manager
mAPIRecipient
mDBOverHardQuotaLimit
mDBOverQuotaLimit
mDBStorageQuota
mDBUseDefaults
memberOf
mobile
msDS-SupportedEncryptionTypes
msExchADCGlobalNames
msExchALObjectVersion
msExchAssistantName
msExchBlockedSendersHash
msExchELCMailboxFlags
msExchExtensionCustomAttribute5
msExchHideFromAddressLists
msExchHomeServerName
msExchMailboxGuid
msExchMailboxMoveFlags
msExchMailboxMoveSourceMDBLink
msExchMailboxMoveStatus
msExchMailboxMoveTargetMDBLink
msExchMailboxSecurityDescriptor
msExchMasterAccountSid
msExchMobileMailboxFlags
msExchObjectsDeletedThisPeriod
msExchPoliciesExcluded
msExchPoliciesIncluded
msExchPreviousAccountSid
msExchPreviousHomeMDB
msExchRBACPolicyLink
msExchRecipientDisplayType
msExchRecipientTypeDetails
msExchRequireAuthToSendTo
msExchSafeSendersHash
msExchShadowMailNickname
msExchTextMessagingState
msExchUMDtmfMap
msExchUserAccountControl
msExchUserCulture
msExchVersion
msExchWhenMailboxCreated
mSMQDigests
mSMQSignCertificates
msNPAllowDialin
name
objectCategory
objectClass
objectGUID
objectSid
operatorCount
ou
Persiaran Apec
physicalDeliveryOfficeName
postalCode
postOfficeBox
preferredLanguage
primaryGroupID
profilePath
protocolSettings
proxyAddresses
publicDelegates
publicDelegatesBL
pwdLastSet
replicatedObjectVersion
replicationSignature
sAMAccountName
sAMAccountType
scriptPath
securityProtocol
showInAddressBook
sIDHistory
sn
st
streetAddress
telephoneNumber
textEncodedORAddress
title
userAccountControl
userCertificate
userParameters
userPrincipalName
userSMIMECertificate
userWorkstations
uSNChanged
uSNCreated
whenChanged
whenCreated
wWWHomePage

I'm sorry I can't share any dsquery data, as it's sensitive information!

[Don Jones Keymaster Points: 1,811Rank: Community Hero]

It'd be helpful to see at least the header row of the resulting CSV. And to see what your $props variable contained.

[Steve Ayers Participant Points: 0Rank: Member]

I changed line 5 to:

(get-content $dir\Headers.txt | out-string).split("`n") | % { if ($_ -ne ""){$props.Add(($_).Trim(),"DATA") }}

To atleast get some data in there.

This is the output of the csv:

#TYPE System.Management.Automation.PSCustomObject
"autoReplyMessage","homeDrive","managedObjects","logonCount","l","wWWHomePage","msExchMobileMailboxFlags","mDBOverQuotaLimit","scriptPath","msExchVersion","deletedItemFlags","sAMAccountType","distinguishedName","co","msExchPoliciesIncluded","msExchShadowMailNickname","mDBStorageQuota","msExchUserAccountControl","title","msExchMailboxMoveSourceMDBLink","msExchHideFromAddressLists","deliverAndRedirect","displayNamePrintable","badPasswordTime","msExchRecipientDisplayType","replicationSignature","publicDelegatesBL","msExchWhenMailboxCreated","msExchPoliciesExcluded","userParameters","manager","lastLogon","mDBUseDefaults","c","countryCode","Persiaran Apec","ADsPath","dLMemRejectPerms","givenName","extensionAttribute14","instanceType","Block 3544","msNPAllowDialin","msExchSafeSendersHash","msExchMailboxMoveFlags","whenChanged","telephoneNumber","extensionAttribute3","extensionAttribute5","msExchMailboxGuid","primaryGroupID","logonHours","securityProtocol","mail","extensionAttribute1","description","profilePath","gPLink","garbageCollPeriod","mDBOverHardQuotaLimit","msExchRecipientTypeDetails","extensionAttribute11","msExchRequireAuthToSendTo","postOfficeBox","altRecipient","authOrigBL","msExchPreviousAccountSid","homeDirectory","directReports","sn","preferredLanguage","homeMTA","extensionAttribute13","accountExpires","extensionAttribute4","replicatedObjectVersion","mAPIRecipient","msExchUMDtmfMap","userAccountControl","pwdLastSet","publicDelegates","legacyExchangeDN","company","msExchMailboxMoveTargetMDBLink","uSNCreated","codePage","userPrincipalName","msExchELCMailboxFlags","st","dSCorePropagationData","msExchTextMessagingState","extensionAttribute2","msExchExtensionCustomAttribute5","lastKnownParent","protocolSettings","adminCount","badPwdCount","mSMQDigests","cn","operatorCount","msExchPreviousHomeMDB","proxyAddresses","extensionAttribute12","name","msDS-SupportedEncryptionTypes","ou","msExchMailboxMoveStatus","dLMemSubmitPerms","textEncodedORAddress","physicalDeliveryOfficeName","streetAddress","msExchUserCulture","userCertificate","userSMIMECertificate","userWorkstations","displayName","msExchBlockedSendersHash","comment","msExchObjectsDeletedThisPeriod","extensionAttribute15","sIDHistory","sAMAccountName","extensionAttribute9","msExchAssistantName","info","mobile","department","msExchHomeServerName","memberOf","employeeID","msExchADCGlobalNames","gPOptions","msExchMasterAccountSid","msExchRBACPolicyLink","homePhone","extensionAttribute10","objectGUID","lockoutTime","msExchMailboxSecurityDescriptor","mailNickname","uSNChanged","initials","facsimileTelephoneNumber","lastLogonTimestamp","objectSid","msExchALObjectVersion","homeMDB","lastLogoff","dLMemDefault","objectCategory","showInAddressBook","whenCreated","postalCode","objectClass","mSMQSignCertificates"
"DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA","DATA"

I put in this to catch the error:

    try {
    New-Object -TypeName PSObject -Property $props | Export-CSV $output -append
    }
    catch {
    $props > text.txt ; ii text.txt
    Write-host $_.Exception
    exit
    }

The first time this fails, here is the data is fails on..
Props:

Name                           Value                                                                                                 
----                           -----                                                                                                 
uSNCreated                     28481                                                                                                 
name                           Recipients                                                                                            
whenChanged                    09/15/2011 11:37:49                                                                                   
objectClass                    organizationalUnit                                                                                    
uSNChanged                     28481                                                                                                 
dSCorePropagationData          SENSITIVE DATA
ADsPath                        SENSITIVE DATA 
distinguishedName              SENSITIVE DATA                          
ou                             Recipients                                                                                            
whenCreated                    01/10/2006 15:45:32                                                                                   
instanceType                   4                                                                                                     
objectGUID                     SENSITIVE DATA                                                                   
objectCategory                 SENSITIVE DATA  

Error:

System.InvalidOperationException: Cannot append CSV content to the following file: C:\Users\???\Desktop\File002.csv. The appe
nded object does not have a property that corresponds to the following column: autoReplyMessage. To continue with mismatched propertie
s, add the -Force parameter, and then retry the command.
   at System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)

[Steve Ayers Participant Points: 0Rank: Member]

EDIT:

After I wrote -Force, my spreadsheet still didn't look right...

[Don Jones Keymaster Points: 1,811Rank: Community Hero]

So here's the thing. When you emit the headers for a CSV, PowerShell assumes that all subsequent data rows will contain exactly those columns, no more, no less. The error you're getting indicates that you attempted to export data rows that didn't have all the fields listed in the header. Your output of $props confirms that. Yes, you can -force it, but it won't be a valid CSV as a result.

So this isn't a problem with your PowerShell code. It's a problem with your source data – it's not consistent.

That means, I suspect, your'e going to have to (a) construct an output object that has ALL the properties your CSV header defines, (b) fill those in with the data you have, and (c) accept that some properties will be blank on some rows. It's fine to emit a property with a null value – Export-CSV comprehends that, and the resulting CSV will be valid. But you can't simply omit the property.

[Author]

Posts