Saying my CSV doesn't have a column when it does...
| Author | 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 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: 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.. 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.
