Bulk Insert Unicode

Good day,

We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.

When the record and row are specified in the format file as:

<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>

the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.

When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:

Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).

This is true regardless of which bulk insert options specified.

What could be going wrong and how can we address it?

Thanks in advance...

Hi,

I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?

regards

|||

Hi knightEknight!

We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....

|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||

Here is a C# example:

// utf8 in, ansi out

StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");

FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");

Encoding ansi = Encoding.Default;

string inLine;

byte[] outBytes;

while (true)

{

inLine = inStream.ReadLine();

if (inLine == null)

break;

outBytes = ansi.GetBytes(inLine + "\r\n");

outStream.Write(outBytes, 0, outBytes.Length);

}

inStream.Close();

outStream.Close();

|||

Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.

Regards,

Lars

sql

Bulk Insert Unicode

Good day,

We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.

When the record and row are specified in the format file as:

<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>

the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.

When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:

Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).

This is true regardless of which bulk insert options specified.

What could be going wrong and how can we address it?

Thanks in advance...

Hi,

I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?

regards

|||

Hi knightEknight!

We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....

|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||

Here is a C# example:

// utf8 in, ansi out

StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");

FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");

Encoding ansi = Encoding.Default;

string inLine;

byte[] outBytes;

while (true)

{

inLine = inStream.ReadLine();

if (inLine == null)

break;

outBytes = ansi.GetBytes(inLine + "\r\n");

outStream.Write(outBytes, 0, outBytes.Length);

}

inStream.Close();

outStream.Close();

|||

Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.

Regards,

Lars

Bulk Insert Unicode

Good day,

We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.

When the record and row are specified in the format file as:

<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>

the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.

When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:

Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).

This is true regardless of which bulk insert options specified.

What could be going wrong and how can we address it?

Thanks in advance...

Hi,

I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?

regards

|||

Hi knightEknight!

We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....

|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||

Here is a C# example:

// utf8 in, ansi out

StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");

FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");

Encoding ansi = Encoding.Default;

string inLine;

byte[] outBytes;

while (true)

{

inLine = inStream.ReadLine();

if (inLine == null)

break;

outBytes = ansi.GetBytes(inLine + "\r\n");

outStream.Write(outBytes, 0, outBytes.Length);

}

inStream.Close();

outStream.Close();

Bulk Insert Unicode

Good day,

We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.

When the record and row are specified in the format file as:

<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>

the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.

When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:

Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).

This is true regardless of which bulk insert options specified.

What could be going wrong and how can we address it?

Thanks in advance...

Hi,

I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?

regards

|||

Hi knightEknight!

We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....

|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||

Here is a C# example:

// utf8 in, ansi out

StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");

FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");

Encoding ansi = Encoding.Default;

string inLine;

byte[] outBytes;

while (true)

{

inLine = inStream.ReadLine();

if (inLine == null)

break;

outBytes = ansi.GetBytes(inLine + "\r\n");

outStream.Write(outBytes, 0, outBytes.Length);

}

inStream.Close();

outStream.Close();

Bulk Insert Unicode

Good day,
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID=3D"23" xsi:type=3D"CharTerm" TERMINATOR=3D"\t" MAX_LENGTH=3D"400"
COLLATION=3D"Latin1_General_CI_AS"/>
<COLUMN SOURCE=3D"23" NAME=3D"FullName" xsi:type=3D"SQLNVARCHAR"/>
the value "Boca Cur=E1" gets imported as "Boca Cur=C3=A1". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...Hi
Can you specify SQLNVARCHAR(50) for example? If I remember well it truncates
the string if you do not specify a length.As it is NVARCHAR , so it is like
NVARCHAR(2)
<rgreene@.icanmarine.com> wrote in message
news:1143039002.249599.122720@.g10g2000cwb.googlegroups.com...
Good day,
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Cur" gets imported as "Boca Curá". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...|||Hi Uri. Thanks for the quick reply.
When I use NCharTerm in the record section of the formatfile and
SQLNVARCHAR(200) in the row section I get:
Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Line 49 in format file "C:\Documents and Settings\rgreene\My
Documents\PlaceName
s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
Any other ideas?|||Well, can you show us a format file? Is it a XML file?
<rgreene@.icanmarine.com> wrote in message
news:1143039873.112229.184880@.g10g2000cwb.googlegroups.com...
> Hi Uri. Thanks for the quick reply.
> When I use NCharTerm in the record section of the formatfile and
> SQLNVARCHAR(200) in the row section I get:
> Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
> Line 49 in format file "C:\Documents and Settings\rgreene\My
> Documents\PlaceName
> s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
> Any other ideas?
>|||Here is the original XML format file (the one that imports, but
changes, the unicode text):
<?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 ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="8"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="6"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RegionCode" xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="2" NAME="UniqueFeatureIdentifier"
xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="UniqueNameIdentifier" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Lat" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="5" NAME="Lon" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="10" NAME="FeatureClassificationCode"
xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="11" NAME="FeatureDesignationCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="12" NAME="PopulatedPlaceClassification"
xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="13" NAME="PrimaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="14" NAME="ADM1Code" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="15" NAME="ADM2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="16" NAME="Dimension" xsi:type="SQLINT"/>
<COLUMN SOURCE="17" NAME="SecondaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="18" NAME="NameType" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="19" NAME="LanguageCode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="22" NAME="SortName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR()"/>
<COLUMN SOURCE="24" NAME="FullNameND" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="25" NAME="ModificationDate"
xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>