Powershell cmdlet Import-csv, special characters get lost…

An idea how to handle special characters from Powershell:

cat list.csv > list_temp.csv # make utf8
$header = "FirstName","LastName","EmailAddress","Displayname" $csvinfo = import-csv "list_temp.csv" -del ";"  -head $header
ForEach ($line in $csvinfo) {New-MailContact -Name $line.DisplayName -FirstName $line.FirstName -LastName $line.LastName -ExternalEmailAddress $line.EmailAddress -DisplayName $line.DisplayName}
del list_temp.csv

Important part: defaults to reading in utf8 encoding, workaround: cat list.csv > list_temp.csv # make utf8 😉

5 Comments

  1. The only problem with the cat (get-content) command :
    if there is in the csv file, a column containing a “carriage return” (0D) or a “end of line” (0A) alone, you will get in the resulting file a pair carriage return + end of line (000D000A).
    Special characters do not get lost, but you may have additional characters!

    Reply

  2. The only problem with the Get-Content command is :
    If there’s in the csv file, a column containing a “carriage return” (0x0D) or a “end of line” (0x0A) alone (i.e. not a pair 0D0A), you will get a pair in the created file.

    => The only way I found to convert a Windows ANSII (1252) encoded file, into UTF-8 using PowerShell is :

    $InFile = ‘C:\list.csv’
    $OutFile = $InFile + ‘.utf8’
    $objFileByteArray = [System.IO.File]::ReadAllBytes($InFile)
    $objOutFileByteArray = New-Object ‘byte[]’ (2 * ($objFileByteArray.Length + 1))
    $objOutFileByteArray[0] = 254; $objOutFileByteArray[1] = 255;
    for ($i=0; $i -lt $objFileByteArray.Length; $i++) { $objOutFileByteArray[(2*($i+1))] = 0 ; $objOutFileByteArray[(2*($i+1))+1] = $objFileByteArray[$i] }
    [System.IO.File]::WriteAllBytes($OutFile, $objOutFileByteArray)

    Reply

  3. Mistake in my previous post, this is not utf-8 but unicode conversion (and incorrect for some characters).

    => I found a better way to convert from windows-1252 to unicode :

    $objInStream = [System.IO.File]::OpenRead($InFile)
    $objOutStream = [System.IO.File]::OpenWrite($OutFile)
    $objBinReader = New-Object System.IO.BinaryReader($objInStream, [System.Text.Encoding]::Default)
    $objBinWriter = New-Object System.IO.BinaryWriter($objOutStream, [System.Text.Encoding]::Unicode)
    for ($i=0; $i -lt $objInStream.Length; $i++) { $objBinWriter.Write( $objBinReader.ReadChar() ) }
    $objBinReader.Close() ; $objBinWriter.Close()

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s