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

Welcome Forums General PowerShell Q&A Export-CSV, but only have specific values contain double quotes

  • This topic has 3 replies, 3 voices, and was last updated 2 months ago by
    Participant
    .
Viewing 2 reply threads
  • Author
    Posts
    • #212244
      Participant
      Topics: 6
      Replies: 9
      Points: -7
      Rank: Member

      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

      
      $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
      
      

       

    • #212268
      Participant
      Topics: 4
      Replies: 82
      Points: 250
      Helping Hand
      Rank: Participant

      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.

      • This reply was modified 2 months ago by Mike R..
    • #212292
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

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

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

      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.

Viewing 2 reply threads
  • You must be logged in to reply to this topic.