Export-CSV does not reflect proper telephonenumber

Welcome Forums General PowerShell Q&A Export-CSV does not reflect proper telephonenumber

Viewing 3 reply threads
  • Author
    Posts
    • #199103
      Participant
      Topics: 17
      Replies: 16
      Points: 45
      Rank: Member

      Most Users in our domain have a phone numbers in the following format,

      +52.5558777777

      It has the ‘+’ sign followed by the country code, a period and then the rest of the number.  I can see this when I perform a Get-ADUser.

      When I take the user and pipe it to export-csv, the format of the phone appears as:

      52.5558777777 (no plus sign)

      or (with a different user who is in AD as +390542677777)

      3.90543E+11 in the row in Excel but when I select the cell in Excel, it shows up as 390542677777

      I simply want it to show up verbatim as it exists in AD.  How do I amend the following so that it reports telephone numbers with the plus sign, no exponential, i.e., as it is in AD.

      Get-Aduser SampleUserXY -Properties officephone,telephonenumber,ipPhone,fax,employeenumber,extensionattribute4 -Credential $credINT | Select employeenumber,”officephone”,`
      “telephonenumber”,ipPhone,fax,extensionattribute4 | Export-CSv -Path “C:\Users\admin\Documents\AD Reports\Telephone\Test2.csv” -NoTypeInformation -Encoding UTF8

      Note: The quotes around telephonenumber were an attempt to correct it. I get the same results without the quotes.

    • #199109
      Participant
      Topics: 4
      Replies: 1908
      Points: 4,259
      Helping Hand
      Rank: Community Hero

      Excel is made to calculate with numbers. So it interprets numbers with the leading plus sign as positive numbers. When you open your CSV file in another program like notepad or VSCode you will see the plus sign. What for do you export the AD user? If you really need to display it in Excel you would need to insert an apostrophe in front of the plus sign.

      But that would affect a further step if you use this CSV to re-import it to the AD.

      Could you please format your code as code using code tag button (“PRE”)? Thanks.

      • This reply was modified 2 months, 1 week ago by Olaf.
      • This reply was modified 2 months, 1 week ago by Olaf.
    • #199262
      Participant
      Topics: 17
      Replies: 16
      Points: 45
      Rank: Member

      Yeah, prefixing with an apostrophe will work but was hoping for a cleaner solution.

      I have been playing with the following:

      Get-Aduser SampleUserXY -Properties officephone,telephonenumber,ipPhone,fax,employeenumber,extensionattribute4 -Credential $credINT | Select employeenumber,”officephone”,`
      “telephonenumber”,ipPhone,fax,extensionattribute4 | ConvertTo-Csv -Delimiter “,” -NoTypeInformation | Out-file “C:\Users\admin\Documents\AD Reports\Telephone\Test.txt”

       

      I then can open it in Excel but before I import, I change the columns to “text” instead of integer.  Better than what I had before but I wish there was something where I did not have to go through such hoops.

    • #199274
      Participant
      Topics: 4
      Replies: 1908
      Points: 4,259
      Helping Hand
      Rank: Community Hero

      Could you please format your code as code using code tag button (“PRE”)? Thanks.

      You can use a calulated property …

      Get-Aduser SampleUserXY -Properties officephone, telephonenumber, ipPhone, fax, employeenumber, extensionattribute4 -Credential $credINT | 
          Select-Object employeenumber, officephone,
              @{Name='telephonenumber';Expression={"'" + $_.telephonenumber}},
              ipPhone, fax, extensionattribute4 | 
                  Export-Csv -Path 'C:\Users\admin\Documents\AD Reports\Telephone\Test.csv' -Delimiter "," -NoTypeInformation
Viewing 3 reply threads
  • You must be logged in to reply to this topic.