Is it possible to use OPENROWSET to import fixed width UTF8 encoded files?

Question

I have an example data file with following contents and saved with UTF8 encoding.

oab~opqr
öab~öpqr
öab~öpqr

The format of this file is fixed width with columns 1 to 3 each being allocated 1 character and column 4 reserved 5 characters.

I have created an XML format file as below

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharFixed" ID="Col1" LENGTH="1"/>       
      <FIELD xsi:type="CharFixed" ID="Col2" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col3" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col4" LENGTH="5"/> 
      <FIELD xsi:type="CharTerm" ID="LINE_BREAK" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="Col1" NAME="Col1" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col2" NAME="Col2" xsi:type="SQLNVARCHAR"/> 
      <COLUMN SOURCE="Col3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col4" NAME="Col4" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

Disappointingly running the following SQL...

SELECT *
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

Produces the following results

Col1 Col2 Col3 Col4
---- ---- ---- -----
o    a    b    ~opqr
�    �    a    b~öp
�    �    a    b~öp

from which I conclude the LENGTH is counting bytes rather than characters.

Is there any way I can get this working correctly for fixed character widths with UTF8 encoding?

(Target environment is Azure SQL Database reading from Blob storage)

NB: It was suggested in the comments that adding COLLATION="LATIN1_GENERAL_100_CI_AS_SC_UTF8" to the FIELD elements might help but the results remain unchanged with this.

Answer 1

from which I conclude the LENGTH is counting bytes rather than characters.

This is correct and there is no way to make it characters instead.

The situation is analogous to the n in char(n), varchar(n), nchar(n), and nvarchar(n), where 'n' denotes the number of bytes, not characters. See the documentation:

A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n never defines numbers of characters that can be stored. This is similar to the definition of NCHAR(n) and NVARCHAR(n). The misconception happens because when using single-byte encoding, the storage size of CHAR and VARCHAR is n bytes and the number of characters is also n. However, for multi-byte encoding such as UTF-8, higher Unicode ranges (128-1,114,111) result in one character using two or more bytes.

This is a source of confusion for many, especially since the introduction of UTF-8 support. It was possible before with n(var)char and supplementary characters, but relatively rarely encountered I would say.

It would be nice if SQL Server extended its support for characters rather than bytes in several areas in future (including OPENROWSET).

In the meantime, your workaround is the one I would probably use too.

Answer 2

One workaround is to just change the format file to bring the whole line in, in bulk, and do the substring-ing in TSQL

With format file

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharTerm" ID="WholeLine" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="WholeLine" NAME="WholeLine" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

The following does return the desired results

SELECT SUBSTRING(WholeLine, 1,1) AS Col1,
       SUBSTRING(WholeLine, 2,1) AS Col2,
       SUBSTRING(WholeLine, 3,1) AS Col3,
       SUBSTRING(WholeLine, 4,5) AS Col4
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

Answer 3

Just to put this out there as another option, especially for those who cannot modify the process within SQL Server:

You can also convert the file encoding from UTF-8 to UTF-16 LE (Little Endian; often referred to as simply "Unicode" within many Microsoft products). You would then change the following (from what is posted in the question):

In the format file:

1. Change xsi:type from CharFixed to NCharFixed
2. Double the value for each LENGTH (e.x. 2 -> 4, 5 -> 10)
3. For <FIELD ID="LINE_BREAK" ...>:
   1. Change xsi:type from CharTerm to NCharFixed
   2. Remove TERMINATOR="\r\n
   3. Add LENGTH="4" (use "4" for "\r\n" or 2 for "\n")

In the call to OPENROWSET():

1. Remove , CODEPAGE = '65001' as code page is ignored when using the "NChar*" xsi:types.

 
NOTES

1. Needing to double the LENGTH value is even more (and sadder) evidence that LENGTH is always bytes. Sad that they didn't make it code units so that a LENGTH of "1" would get any BMP character, just like you would expect from NCHAR(1) / NVARCHAR(1) within T-SQL.

2. Why switch CharTerm to NCharFixed instead of NCharTerm? Because I couldn't get NCharTerm to work. My test data was 100% correct, yet using NCharTerm would only ever import the first row. Might be a bug.

3. While this does handle characters that in UTF-8 are either 2 or 3 bytes, neither this option nor UTF-8 handle combining characters. Meaning, the ö in the sample data can either be a single character (as it is in the sample data in the question), or it can be a combination of an unaccented o plus the diacritic mark (2 characters but now 3 bytes in UTF-8 as the diacritic is 2 bytes by itself, or 4 bytes in UTF-16). For example, I created the new character using:

   SELECT N'o'+NCHAR(0x0308); -- ö
   

   and then copy/pasted line 3 to a new line 4, swapping out the first character with what I just created, and changing the "ab" into "cd", just to be able to clearly distinguish the input rows. Doing this resulted in the following error:

   Msg 4832, Level 16, State 1, Line XXXXX
   Bulk load: An unexpected end of file was encountered in the data file.

   I would expect the UTF-8 file to produce the same error given the same character.

   And to be fair, this scenario would also break the approach of pulling each line in entirely and using SUBSTRING to break it up, if not by producing an error, then at least by corrupting the data as SUBSTRING will still see o and ̈ as two separate characters (since they are).