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

Tagged: 

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 7 months, 4 weeks ago.

  • Author
    Posts
  • #37644
    Profile photo of Steve Ayers
    Steve Ayers
    Participant

    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!

  • #37645
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #37655
    Profile photo of Steve Ayers
    Steve Ayers
    Participant

    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)
    
  • #37659
    Profile photo of Steve Ayers
    Steve Ayers
    Participant

    EDIT:

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

  • #37675
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.