locked
Weird import result when trying to import telephone numbers with "+" sign and Import-csv RRS feed

  • Question

  • Hi all,

    I am trying to import a csv generated by Excel which has several phone numbers in, int that format:

    sAMAccoutname;telephoneNumber;EmailAddress
    Bob;+41 12 12345;bob@hallo.to
    Steven;+49 3456 7890-12;steven@hallo.to

    I am importing in this style:

    Import-Csv -path $csvimportfile -Delimiter ';' -Encoding UTF7  | ForEach-Object {  
        if ($_.sAMAccountName -ne "") {
    		$samAccountName = $_.sAMAccountName
    		$telephoneNumber = $_.telephoneNumber
    		$EmailAddress = $_.EmailAddress
        }

    I now got the problem, that the $telephoneNumber variable contains only:

    12 12345
    3456 7890-12

    when cycling through the import. It is loosing the dial prefix...

    Do you have any idea?

    Thanks a lot, folks



    • Edited by ODaeumling Friday, June 29, 2018 12:44 PM
    Friday, June 29, 2018 12:43 PM

All replies

  • Are you sure about the encoding? More common is UTF8. At least try it.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, June 29, 2018 1:13 PM
    Friday, June 29, 2018 1:11 PM
  • OK. Encoding was the thing.

    I first had to use UTF7 due to german Umlaute in the original data, but this leads to misinterpretation of the imported data when using Import-csv.

    To avoid struggling with German Umlaute, e.g. with "Osnabr�cker Stra�e 77" instead "Osnabrücker Straße 77" when importing with Encoding UTF8, I have to convert the file to UTF 8:

    Get-Content $csvimportfile | Set-Content -Encoding utf8 $csvimportfileutf8
    Import-Csv -path $csvimportfileutf8 -Delimiter ';' -Encoding UTF8

    Now I get the proper data format while importing:

    Bob
    +41 12 12345
    bob@hallo.to
    Osnabrücker Straße 77

    Thanks a lot for your help :-)


    Windows / Exchange / Lync/Skype Dinosaur

    Friday, June 29, 2018 1:40 PM
  • Great.

    BTW: Sometimes it's easier to ask a question in your native language. There is a German Powershell Forum as well. There, we are even experienced with German Umlaute und so weiter. ;-)


    Best regards,

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, June 29, 2018 1:52 PM
    Friday, June 29, 2018 1:51 PM