Bulk insert Unicode data

Hi,
I have to import Unicode data from a txt file to a sql server table.
I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
Datafiletype = WIDECHAR but without success.
The error is "Cannot perform bulk insert. Invalid collation name for source
column xx in format file".
Format file is :
8.0
19
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
19 SQLINT 0 0
(~ is field separator)
Thanks!
MBHi
It is not clear what you are trying to do with field 19? if it is not
required then check out
http://msdn.microsoft.com/library/d...>
bcp_9y43.asp
At a guess try:
18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
19 SQLCHAR 0 0 "\r\n" 0 Dump
John
"Michela Burello" wrote:

> Hi,
> I have to import Unicode data from a txt file to a sql server table.
> I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
> Datafiletype = WIDECHAR but without success.
> The error is "Cannot perform bulk insert. Invalid collation name for sourc
e
> column xx in format file".
> Format file is :
> 8.0
> 19
> 1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
> 2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
> 3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
> 4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
> 5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
> 6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
> 7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
> 8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
> 9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
> 10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
> 11 SQLMONEY 0 8 "~" 12 Price ""
> 12 SQLFLT8 0 8 "~" 13 VAT ""
> 13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
> 14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
> 15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
> 16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
> 17 SQLINT 0 4 "~" 17 Pagina ""
> 18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
> 19 SQLINT 0 0
> (~ is field separator)
> Thanks!
> MB|||Hi,
thank you for your answer but I still have problems!
I tried to bulk insert without field 19.
The error is "Cannot perform bulk insert. Invalid collation name for source
column 18 in format file".
My format file is now:
8.0
18
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
My query is:
bulk insert MyTable
from 'myfile.txt'
with ( fieldterminator = '~',
firstrow = 2,
DATAFILETYPE = 'widechar',
FORMATFILE = 'myformatfile.fmt',
codepage = 1200
)
The table MyTable has this structure:
BatchGroup nvarchar 50
ProductID varchar 100 1
ProducerIDERP nvarchar 100
ProducerName nvarchar 100
ProductDefinitionName varchar 100
Name nvarchar 500 1
MeasureEquivalenceFactor float 8
MeasureUnitID nvarchar 10 1
MeasureUnitIDAlt nvarchar 10
PricingCategoryName nvarchar 100
SupplierName nvarchar 100
Price money 8
VAT float 8
DisplayName nvarchar 500
Tipo varchar 500
SottoTipo varchar 500
Pagina int 4
ImageName varchar 500
ItemID int 4 (identity)
Thanks
MB
"John Bell" wrote:
> Hi
> It is not clear what you are trying to do with field 19? if it is not
> required then check out
> http://msdn.microsoft.com/library/d...
t_bcp_9y43.asp
> At a guess try:
> 18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
> 19 SQLCHAR 0 0 "\r\n" 0 Dump
>
> John
> "Michela Burello" wrote:
>|||On Thu, 28 Apr 2005 00:06:03 -0700, Michela Burello <Michela
Burello@.discussions.microsoft.com> wrote:

>The error is "Cannot perform bulk insert. Invalid collation name for source
>column xx in format file".
(snip)
>18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
Hi Michela,
There is no collation "Latin1_General_CI_A". You need to add either an S
or an I at the end of this line.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
This one works for me!!
8.0
19
1 SQLNCHAR 2 100 "~" 1
BatchGroup SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 100 "~" 2
ProductID SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 200 "~" 3
ProducerIDERP SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 200 "~" 4
ProducerName SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 100 "~" 5
ProductDefinitionName SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 1000 "~" 6
Name SQL_Latin1_General_CP1_CI_AS
7 SQLFLT8 1 8 "~" 7
MeasureEquivalenceFactor ""
8 SQLNCHAR 2 20 "~" 8
MeasureUnitID SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 20 "~" 9
MeasureUnitIDAlt SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 200 "~" 10
PricingCategoryName SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 200 "~" 11
SupplierName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "~" 12
Price ""
13 SQLFLT8 1 8 "~" 13
VAT ""
14 SQLNCHAR 2 1000 "~" 14
DisplayName SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 500 "~" 15
Tipo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 500 "~" 16
SottoTipo SQL_Latin1_General_CP1_CI_AS
17 SQLINT 1 4 "~" 17
Pagina ""
18 SQLCHAR 2 500 "" 18
ImageName SQL_Latin1_General_CP1_CI_AS
19 SQLINT 0 0 "\r\n" 19
ItemID ""
Any the statement:
bulk insert MyTable
from 'C:\temp\myfile.txt'
with ( firstrow = 1,
FORMATFILE = 'C:\temp\myformatfile.fmt'
)
John