BULK INSERT юникодного файла

 Продолжаем этюды из серии "Вводный курс молодого бойца". Рассмотрим следующую задачку. Имеется текстовый файл Customers.txt

 ALFKI Alfreds Futterkiste Maria Anders Sales Representative

ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner

ANTON Antonio Moreno Taqueria Antonio Moreno Owner

AROUT Around the Horn Thomas Hardy Sales Representative

BERGS Berglunds snabbkop Christina Berglund Order Administrator

BLAUS Blauer See Delikatessen Hanna Moos Sales Representative

BLONP Blondesddsl pere et fils Frederique Citeaux Marketing Manager

BOLID Bolido Comidas preparadas Martin Sommer Owner

BONAP Bon app' Laurence Lebihan Owner

BOTTM Bottom-Dollar Markets Elizabeth Lincoln Accounting Manager

BSBEV B's Beverages Victoria Ashworth Sales Representative

CACTU Cactus Comidas para llevar Patricio Simpson Sales Agent

CENTC Centro comercial Moctezuma Francisco Chang Marketing Manager

CHOPS Chop-suey Chinese Yang Wang Owner

COMMI Comercio Mineiro Pedro Afonso Sales Associate

CONSH Consolidated Holdings Elizabeth Brown Sales Representative

DRACD Drachenblut Delikatessen Sven Ottlieb Order Administrator

DUMON Du monde entier Janine Labrune Owner

Скрипт 1

 

сохраненный в кодировке ANSI:

 

Рис.1

 

Чтобы затащить его в SQL Server, я написал следующий форматный файл Customers_fmt.xml:

 

<?xml version="1.0" encoding="UTF-8"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <RECORD>

  <FIELD ID="1" xsi:type="CharFixed" LENGTH="6"/>

  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100"/>

  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="100"/>

  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100"/>

 </RECORD>

 <ROW>

  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLVARYCHAR"/>

  <COLUMN SOURCE="5" NAME="Title" xsi:type="SQLVARYCHAR"/>

  <COLUMN SOURCE="3" NAME="Имя" xsi:type="SQLVARYCHAR" LENGTH="20"/>

  <COLUMN SOURCE="4" NAME="Фамилия" xsi:type="SQLVARYCHAR" LENGTH="25"/>

 </ROW>

</BCPFORMAT>

Скрипт 2

 

Рис.2

 

с которым все отлично работает:

 

select * from openrowset(bulk 'c:\Temp\Customers.txt', formatfile = 'c:\Temp\Customers_fmt.xml') as t

Скрипт 3

 

Рис.3

 

Однако стоит сохранить импортируемые данные Скрипт 1 как Юникод:

 

Рис.4

 

как Скрипт 3 вернет ошибку

 

Msg 4863, Level 16, State 1, Line 2

Bulk load data conversion error (truncation) for row 1, column 5 (Title).

 

Вопрос: что нужно поправить в форматном файле Скрипт 2, чтобы импорт юникода в SQL Server заработал?

Как правило, сначала кидаются поправлять типы колонок SQLVARYCHAR на SQLNVARCHAR. Просто на уровне рефлекса - если юникод, то nchar/nvarchar, а SQLVARYCHAR / SQLNVARCHAR ближе по ассоциации, чем какой-нибудь CharFixed или CharTerm. На самом деле в нашем случае править надо элементы <FIELD>, а не <COLUMN>. Типы колонок влияют на то, как данные будут представлены внутри SQL Server, a проблема возникает раньше. Данные неправильно читаются еще до того, как они попали в SQL Server. Следует поправить разметку полей в файле на юникодовскую, включая разделители, то есть вместо Скрипт 2

 

<?xml version="1.0" encoding="UTF-8"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <RECORD>

  <FIELD ID="1" xsi:type="NCharFixed" LENGTH="12"/>

  <FIELD ID="2" xsi:type="NCharTerm" TERMINATOR="\t\0" MAX_LENGTH="100" />

  <FIELD ID="3" xsi:type="NCharTerm" TERMINATOR=" \0" MAX_LENGTH="100"/>

  <FIELD ID="4" xsi:type="NCharTerm" TERMINATOR="\t\0" MAX_LENGTH="100"/>

  <FIELD ID="5" xsi:type="NCharTerm" TERMINATOR="\r\0\n\0" MAX_LENGTH="100"/>

 </RECORD>

 <ROW>

  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLVARYCHAR" NULLABLE="NO"/>

  <COLUMN SOURCE="5" NAME="Title" xsi:type="SQLVARYCHAR"/>

  <COLUMN SOURCE="3" NAME="Имя" xsi:type="SQLVARYCHAR" LENGTH="20"/>

  <COLUMN SOURCE="4" NAME="Фамилия" xsi:type="SQLVARYCHAR" LENGTH="25"/>

 </ROW>

</BCPFORMAT>

Скрипт 4

 

Тогда запрос Рис.3 успешно отработает для Customers.txt, сохраненного в кодировке Unicode, так же, как до этого в ANSI.

 

 

Алексей Шуленин