Importing data using SQL Server Import Wizard results in weird characters

Question

SQL Server 2016 Import and Export Wizard

Source IS Flat file (.txt):

Please note the data in column 3: it should read Île-de-France

Answer 1

This is the correct procedure for importing tab-delimited text in utf8 encoding.

In the firs page of the Wizard, instead of checking "Unicode" (that's quite confusing because UTF-8 'is' Unicode) leave it unchecked and select Code page 65001

Now characters are being correctly visualized:

Answer 2

This is Unicode Error that's y words is not showing properly. Go to the advance tab and select your third column as shown in fig:

Choose data type string to "text stream [DT_TEXT]", If "text stream [DT_TEXT]" will not work then please try with other data type also. Ref this Fig :