This topic contains 4 replies, has 2 voices, and was last updated by 
-
Posts
-
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: blablaI 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], InvalidOperationExceptionHere 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!
-
It'd be helpful to see at least the header row of the resulting CSV. And to see what your $props variable contained.
-
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)
-
EDIT:
After I wrote -Force, my spreadsheet still didn't look right...
-
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.