;)
If you’ve ever exported data using Export-Csv PowerShell cmdlet and noticed question-marks (?) instead of data, then you need to use the “encoding” parameter.
Encoding PowerShell exports and imports
I recently migrated user data from a Japanese tenant to a European tenant, and needed to use PowerShell encoding to preserve Japanese characters. PowerShell allows you to export/import while encoding Unicode, UTF7, UTF8, ASCII, UTF32, BigEndianUnicode, Default, and OEM. The default is ASCII.
I used UTF8 to preserve Japanese characters in my export. For example add “-encoding UTF8”.
export sample
PowerShell
| 1 | Get-Mailbox -resultsize Unlimited | Export-Csv c:tempmailbox_export.csv -Encoding UTF8 |
Then you can import the Japanese Characters also using “-encoding UTF8”.
import sample
PowerShell
| 12 | $newusers = Import-Csv c:tempmailbox_export.csv -Encoding UTF8$newusers | % {New-Mailbox -Name $_.Name ……… etc. } |
Modify CSV PowerShell files that are encoded
If you only need to export and import, that’s easy. However what if you want to modify the export-csv-file before you import? If you’ve tried to modify an encoded csv file in Excel before, you’ll know the difficulties you can run into – Excel can basically ruin your csv file. Below is the process I’ve used to get around this.
After you have made changes to your CSV file (using Excel) and are ready to save, do the following.
In Microsoft Excel (save as Unicode text)
Click Menu | Save As | choose “Unicode Text (*.txt)”
In Notepad (replace tabs with commas)
Open the Unicode text file (Some characters can look like a box, this is because Notepad can’t display some Unicode characters, you can ignore this).
Replace tabs with a commas (,). Do this by highlighting a tab character between two column headers and press ctrl+c.
Replace all tab characters with comma using Replace function ctrl+H. Paste in “Find what:” and add a comma (,) in the “Replace with:”. Click “Replace All” .
Click Save As. Then change file extension to *.csv, and type to “all files types”, and change Encoding to “UTF-8”.
In Microsoft Excel (verify data)
Open the newly saved CSV file and verify the data is okay.
Import
Now you’re ready to use this file to import. Remember to use “encoding” parameter when importing.
import sample
PowerShell
| 12 | $newusers = Import-Csv c:tempmailbox_export_modified.csv -Encoding UTF8 $newusers | % {New-Mailbox -Name $_.Name ……… etc. } |
