Applications

How to Export and Import foreign characters with PowerShell

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 exports and imports

I recently migrated user data from a Japanese tenant to a European tenant, and needed to use 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.

Import foreign characters with PowerShell

I used UTF8 to preserve Japanese characters in my export. For example add “-encoding UTF8”.

Then you can import the Japanese Characters also using “-encoding UTF8”.

 

Modify CSV 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)”

Import foreign characters with PowerShell

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.

Import foreign characters with PowerShell

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” .

Import foreign characters with PowerShell

Click Save As. Then change file extension to *.csv, and type to “all files types”, and change Encoding to “UTF-8”.

Import foreign characters with PowerShell

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.

 

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.