locked
Echange Address List - Using Import-csv RRS feed

  • Question

  • I am using the Import-csv cmdlet to import contact details into active directory for Exchange Global Address list.

    In my CSV file I have names like 'Stéphane' and the 'é' is being lost when I import using Import-csv.

    I see that the Export-csv has an encoding paramater.

    regards

     

    Martin J Moore

     

     

     

     

     

     

     

    Friday, November 18, 2011 9:31 AM

Answers

  • Hi Everyone,

    I am using Visual Studio 2010 to create an add in in outlook and created a project to generate the CSV file from SQL Server. The Help system has got too clever for itself and for me. Since the 2008 version I cannot find a TOC. One would think that it would take 5 minutes to get some help on the subject. I can only assume that no solution is available for VB.

    My original query was on scripting, but migrated to the creation of a unicode file (which I dont think should be necessary because the character that is going missing is visible in my CSV file). It is only when I opened the address book in Outlook that I noticed that the character had been changed to ? (and this was not a real ? but some kind of graphical representation in unicode).

    To try and solve my original problem I will test the following suggestions - 

    Convert my CSV file to Unicode.

    Use The Get-Content cmdlet

    Martin 

    Absolutely as clear as mud.

    What code is creating the file.  Extended ascii does not need a Unicode file.

    In a hex editor what is the numerical value of teh character.

    There is little difference between VS 2009 and VS2010 help.  The unicode and file support in both are identical. They both use the same Net Framework classes for file IO.

    Outlook contact CSV files are not in Unicode.

    It sounds more like you are exporting to a ANSI file but using the wrong code page when creating it in VS.  VS will be all uncode internall unless set otherwise.  You will need to convert the strings or set the correct code page to be sure the unicode is translated when saved to an ansi file.

    NOne of this has anything to do with scripting or PowerSHell. 

    I would check in with the VS2010 developer forum and get advice on how best to set up your strings to be exportable to ansi.

    I agree that this is different in VS2010 but, as best I can rememebr, it is the same in VS2005 and later.  I am not sure about VS2003 since I don't remember doing much file creation with 2003.

    The other approach is to save the CSV as a Unicode file inVS2010 using the method posted above by Karl.  PowerSHell will correctly import a unicode CSV and VS will not have an issue converting characters although you still may have to wrong code page or culture set.

     

     


    jv
    • Edited by jrv Thursday, November 24, 2011 11:18 AM
    • Marked as answer by IamMred Saturday, December 3, 2011 4:58 AM
    Thursday, November 24, 2011 11:18 AM

All replies

  • Martin;

    You can use Get-Content on the file to get the data.

    PowerShell V3's Import-Csv has an Encoding parameter, but that's of small comfort right now...

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Friday, November 18, 2011 8:59 PM
  • Hi Karl,

    Thanks for your response. I am not sure how I can use the Get_Content to replace what I am doing. It appears that the Get_Content does not cater for CSV.

    I first create a new contact:

     

    import-csv c:\mailcontacts\ContactsEXP.csv | foreach {new-mailcontact -name $_.Name -externalemailaddress $_.ExternalEmailAddress -primarysmtpaddress $_.ExternalEmailAddress -organizationalunit 'Europe contact'}  > c:\mailcontacts\ImportLog.txt 2> c:\mailcontacts\ImportErr.txt

     

    And then I update the details:

     

    import-csv c:\mailcontacts\ContactsEXP.csv | `
    foreach {set-contact `
    -identity $_.Name  `
    -company $_.Company `
    -Fax $_.Fax `
    -FirstName $_.FirstName `
    -HomePhone $_.HomePhone `
    -Title $_.Title `
    -Initials $_.Initials `
    -LastName $_.LastName `
    -MobilePhone $_.MobilePhone `
    -PostalCode $_.PostalCode `
    -StateOrProvince $_.StateOrProvince `
    -StreetAddress $_.StreetAddress `
    -confirm:$false `
    }  > c:\mailcontacts\UpdContacttLog.txt 2> c:\mailcontacts\UpdContactErr.txt

     

    I am not sure that the problem is caused by scripting. I have just checked what exchange has replaced the 'é'  with and it is not a ?, as I assumed, but with a unicode character, "St�phan". The 'é'  is asc(233) and should not be a problem with encoding. Maybe I need to precede the 'é' with an escape character.

     

    Thanks again

    Martin

     

     

     

     

    Saturday, November 19, 2011 1:39 PM
  • From your example, it does not look like your csv file has fields with a comma?

    If so, you can do a Get-Content, and then for each line, split on the commas

    $Users = Get-Content .\Users.csv
    Foreach ($User in $Users[-1])
    {
     set-contact `
     -identity $User.Split(",")[0]  `
     -company $User.Split(",")[1] `
     -Fax $User.Split(",")[2] `
     -FirstName $User.Split(",")[3] `
     <etcetera> `
     -confirm:$false `
    }

    Assuming your fields are in order in the .csv ;)

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Monday, November 21, 2011 4:22 PM
  • Hi,

    I'm using PowerShell v2. If the .csv file is in Unicode (e.g., UTF-16), it seems to work fine with import-csv.

    Bill

    • Edited by Bill_Stewart Monday, November 21, 2011 4:26 PM Accidentally submitted before I was finished writing
    Monday, November 21, 2011 4:25 PM
  • Good catch....
    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Monday, November 21, 2011 6:05 PM
  • Hi Bill,

    I use visual basic and cannot find a fileopen method that will allow me to create a unicode file. If I use SQL Query analyzer I can save my query to a file and that allows for encoding selection. I could probably get the user to run this along with the shell scripts.

    Thanks for your input

    regards

    Martin

     

     

     

     

    Wednesday, November 23, 2011 2:43 PM
  • Hi Bill,

    I use visual basic and cannot find a fileopen method that will allow me to create a unicode file. If I use SQL Query analyzer I can save my query to a file and that allows for encoding selection. I could probably get the user to run this along with the shell scripts.

    Thanks for your input

    regards

    Martin

     

     

     

     


    Just use the usual out-File command.

    out-file -file unifile.txt -encoding unicode

    help out-file -full

    You can change the default encoding system wide like this:
    $outputencoding= new-object system.text.unicodeencoding


    jv
    • Edited by jrv Wednesday, November 23, 2011 7:54 PM
    Wednesday, November 23, 2011 7:50 PM
  • I had to do a little testing to be sure but Out-FIle outputs in Unicode by default.

    Import-Csv will import any encoding.

    Export-Csv in V2 and earlier only outputs in ANSI.

     

     


    jv
    Wednesday, November 23, 2011 7:58 PM
  • Hi Bill,

    I use visual basic and cannot find a fileopen method that will allow me to create a unicode file. If I use SQL Query analyzer I can save my query to a file and that allows for encoding selection. I could probably get the user to run this along with the shell scripts.

    Thanks for your input

    regards

    Martin

     

     

     

     


    Martin;

    Look at the File class:

    http://msdn.microsoft.com/en-us/library/system.io.file.aspx#Y0

    Specifically, the File.AppendAllText Method (String, String, Encoding)

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Wednesday, November 23, 2011 8:07 PM
  • Karl - Out-File is a wrapper on AppendAllText.

    They will both work. I believethat  we should stay with Posh CmdLets whenever possible.

     


    jv
    Wednesday, November 23, 2011 8:54 PM
  • Karl - Out-File is a wrapper on AppendAllText.

    They will both work. I believethat  we should stay with Posh CmdLets whenever possible.

     


    jv


    Well, i would too, excelt that the OP specifies that the file is created with VB before he attempts to ue it via PowerShell.

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Wednesday, November 23, 2011 9:06 PM
  • Except that the file class will not work in VB.  It will work in VB.NET C3 or an other CLR language that has access to the Net Framework.

    I missed the VB part.  Good catch.  I still don't think it is VB.NET. I would guess that if it was the OP would already know about file encoding as it is a given on a file creation call.

     


    jv
    Wednesday, November 23, 2011 9:39 PM
  • I noticed he didn't specify Visual Basic .Net, but was really hoping he didn't mean VB6 or previous :)

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Wednesday, November 23, 2011 10:00 PM
  • Karl - I guess we will have to wait for the punch line on this one,

    I bet it's VBA.

     


    jv
    Thursday, November 24, 2011 12:03 AM
  • In the case of VB6, VBA, or VB .NET, then he's asking in the wrong place. He needs to repost in a more appropriate forum.

    Bill

    Thursday, November 24, 2011 12:33 AM
  • Bill - don't be a spoilsport.  We are all waiting for the answer to this.

    Happy Thanksgiving everyone!

     


    jv
    Thursday, November 24, 2011 1:25 AM
  • Hi Everyone,

    I am using Visual Studio 2010 to create an add in in outlook and created a project to generate the CSV file from SQL Server. The Help system has got too clever for itself and for me. Since the 2008 version I cannot find a TOC. One would think that it would take 5 minutes to get some help on the subject. I can only assume that no solution is available for VB.

    My original query was on scripting, but migrated to the creation of a unicode file (which I dont think should be necessary because the character that is going missing is visible in my CSV file). It is only when I opened the address book in Outlook that I noticed that the character had been changed to ? (and this was not a real ? but some kind of graphical representation in unicode).

    To try and solve my original problem I will test the following suggestions - 

    Convert my CSV file to Unicode.

    Use The Get-Content cmdlet

    Martin

     

     

     

     

     

     

     

     

     

     

    Thursday, November 24, 2011 10:34 AM
  • Hi Everyone,

    I am using Visual Studio 2010 to create an add in in outlook and created a project to generate the CSV file from SQL Server. The Help system has got too clever for itself and for me. Since the 2008 version I cannot find a TOC. One would think that it would take 5 minutes to get some help on the subject. I can only assume that no solution is available for VB.

    My original query was on scripting, but migrated to the creation of a unicode file (which I dont think should be necessary because the character that is going missing is visible in my CSV file). It is only when I opened the address book in Outlook that I noticed that the character had been changed to ? (and this was not a real ? but some kind of graphical representation in unicode).

    To try and solve my original problem I will test the following suggestions - 

    Convert my CSV file to Unicode.

    Use The Get-Content cmdlet

    Martin 

    Absolutely as clear as mud.

    What code is creating the file.  Extended ascii does not need a Unicode file.

    In a hex editor what is the numerical value of teh character.

    There is little difference between VS 2009 and VS2010 help.  The unicode and file support in both are identical. They both use the same Net Framework classes for file IO.

    Outlook contact CSV files are not in Unicode.

    It sounds more like you are exporting to a ANSI file but using the wrong code page when creating it in VS.  VS will be all uncode internall unless set otherwise.  You will need to convert the strings or set the correct code page to be sure the unicode is translated when saved to an ansi file.

    NOne of this has anything to do with scripting or PowerSHell. 

    I would check in with the VS2010 developer forum and get advice on how best to set up your strings to be exportable to ansi.

    I agree that this is different in VS2010 but, as best I can rememebr, it is the same in VS2005 and later.  I am not sure about VS2003 since I don't remember doing much file creation with 2003.

    The other approach is to save the CSV as a Unicode file inVS2010 using the method posted above by Karl.  PowerSHell will correctly import a unicode CSV and VS will not have an issue converting characters although you still may have to wrong code page or culture set.

     

     


    jv
    • Edited by jrv Thursday, November 24, 2011 11:18 AM
    • Marked as answer by IamMred Saturday, December 3, 2011 4:58 AM
    Thursday, November 24, 2011 11:18 AM