Unicode/Collation Issue in Openrowset SQL Server-CodePudding

My CSV has text like this:

Côté fenêtres,
carré

I'm trying to open this CSV file using openrowset in SQL Server like below:-

select * from openrowset(BULK 'C:\Import_Orders\Files\PO.csv', 
FORMATFILE = 'C:\Import_Orders\Format\Cust_441211.fmt.txt') as PO

But the result is like this:

C ¦t ¬ fen ¬tres,
Carr ¬

How can I tackle this issue? Let me know if I need to add anything more to this question.

SQL Version - Microsoft SQL Server 2017 (RTM-CU29-GDR) (KB5014553) - 14.0.3445.2 (X64)

This is the format file:-

11.0
8
1       SQLCHAR             0       250      "|"      1       PARTNO    ""
2       SQLCHAR             0       250      "|"      2       CODE   ""
3       SQLCHAR             0       250      "|"      3       PRICEKG ""
4       SQLCHAR             0       250      "|"      4       FOOTKG    ""
5       SQLCHAR             0       250      "|"      5       LENGTH    ""
6       SQLCHAR             0       250      "|"      6       QTY   ""
7       SQLCHAR             0       250      "|"      7       COLOR ""
8       SQLCHAR             0       250      "\r\n"   8       TOTKG ""

CodePudding user response：

(1) You can try to add an additional parameter CODEPAGE = '65001' to specify a code page to support UNICODE characters.

(2) Use may try to use SQLNCHAR data type instead of SQLCHAR in the format file. For a text file you should always specify SQLCHAR for all fields, unless you have a Unicode file in in UTF‑16 encoding in which case you should use SQLNCHAR.

SQL

SELECT * FROM openrowset(BULK 'C:\Import_Orders\Files\PO.csv', 
FORMATFILE = 'C:\Import_Orders\Format\Cust_441211.fmt.txt',
CODEPAGE = '65001') as PO;