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!