Export-CSV does not reflect proper telephonenumber

PowerShell Help
1

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.

2

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.

3

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.

4

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