Export-CSV, but only have specific values contain double quotes

PowerShell Help
1

I have a need for a very strict CSV export for a COTS package I am trying to create an automated user load for.

The import utility by the vendor can only have double quotes around values that contain special characters, and nothing else.

So basically, every pscustomobject I’ve created then is a stopper, since the output is enclosed in " " whether the object is empty (I could use $null here), or adding a Get-ADuser property.

Unfortuantely, Export-CSV outputs double quotes around EVERYTHING. Is there a way around this, say, to only have the DISPLAYNAME object contain double quotes?

Gist

[pre]

$destinationCSV = “C:\Temp\export.csv”
‘OU=Users, OU=redacted1, OU=blue, OU=tree, OU=oranges, OU=Agencies, DC=business, DC=net’, ‘OU=Users,OU=redacted2,OU=brown,OU=stick,OU=oranges,OU=Agencies,DC=business,DC=net’, ‘OU=Users,OU=redacted3,OU=5030,OU=leaf,OU=grapes,OU=Agencies,DC=business,DC=net’ | ForEach-Object { Get-ADUser -SearchBase $_ -filter { Enabled -eq $true } -properties samaccountname, givenname, surname, displayname, usdaeauthID, mail, telephonenumber, title, manager, streetAddress, physicalDeliveryOfficeName, state, postalCode, c, l, description |
Select-Object samaccountname, givenname, surname, displayname, authID, mail, telephonenumber, title, @{ Name = ‘manager’; Expression = { (Get-ADUser $.Manager).Name } }, @{ n = ‘StreetAddress’; e = { $.streetaddress -replace "n", ", " } }, physicalDeliveryOfficeName, state, postalCode, c, l, description } |

foreach {
[pscustomobject]@{
MAXPERUSER_STATUS = “ACTIVE”
STATUSDATE = “”
USERID = $.samaccountname
PASSWORDCHECK = “Redacted!”
PASSWORDINPUT = “Redacted!”
GROUPNAME = “”
FIRSTNAME = $.givenname
LASTNAME = $.surname
DISPLAYNAME = $.displayname
DEFSITE = “ENG”
EMAILPSWD = “0”
INACTIVESITES = “0”
LOGINID = $.authID
LOCATIONORG = “TEST”
PERSONID = $.samaccountname
POSTALCODE = $.postalcode
PRIMARYEMAIL = $.mail
PRIMARYPHONE = $.telephonenumber
ADDRESSLINE1 = $.streetAddress -join “;”
ADDRESSLINE2 = $.physicalDeliveryOfficeName
ADDRESSLINE3 = “”
BILLTOADDRESS = “”
BIRTHDATE = “”
CALTYPE = “”
CITY = $.l
COUNTRY = $.c
COUNTY = “”
DEPARTMENT = $.description
EMPLOYEETYPE = “”
EXTERNALREFID = “”
HIREDATE = “”
LASTEVALDATE = “”
LOCATION = “”
REGIONDISTRICT = “”
STATEPROVINCE = $.state
SUPERVISOR = “”
TERMINATIONDATE = “”
TITLE = $.title
MEMO = “”
PASSWORD = “”
PWEXPIRATION = “”
PWHINTANSWER = “”
PWHINTQUESTION = “”
} | Export-Csv -Delimiter “,” $destinationCSV -Append -NoTypeInformation

}

#Add mandatory “header” for MIF to import correctly
$oldCSV = Get-Content $destinationCSV
Set-Content -Path $destinationCSV -Value “EXTSYS1,MXPERUSERInterface,EN”
Add-Content -Path $destinationCSV -Value $oldCSV

[/pre]

 

2

I don’t see any simple parameter in Export-Csv that will do what you are looking for. One way to accomplish this is to use the -replace operator. It will take a regular expression and replace every instance with a specified literal string. For example if you wanted to change ALL " to ’ in a csv file:

(Get-Content -raw .\file.csv) -replace '"', "'" | Set-Content .\file.csv

If there are instances where the " is supposed to be there you would have to determine through regex or iterate over the properties to convert as needed. Additionally, if you go this route you might want to use ConvertTo-Csv instead of Export-Csv in your Foreach-Object loop so you are not writing to the file twice in each iteration, but depending on how many iterations, it may not make a difference.

3

Hmmm … I would recommend to seriously talk with this vendor why he refuses to correctly follow an industry-wide standard. :wink:

You could try to use a newer version of the cmdlet Export-Csv delivered with Powershell v7 what comes with the parameter -UseQuotes. With the value AsNeeded for -UseQuotes only fields containing a delimiter charachter would be quoted.