Bulk Insert - Problem with characters

Hi,
I am using bulk insert to load data from a # delimited text file into SQL
Server database table. There are some characters in the file like
"FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER B
AYER
20L". I need to load the data as it is in the file. I am using the following
code from a stored procedure.
SELECT @.SSQL='BULK INSERT ' +@.tblName+
' FROM ''' + @.in_file_name + '''' +
' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR =
''' + @.RowTer + ''', CODEPAGE = ''850'')'
EXECUTE(@.SSQL)
I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
hold the above value. Its nvarchar(255).
Could you please help me out in this. This is very urgent. Appreciate your
timely help.
Many Thanks.Add "DATAFILETYPE='widechar'" to your statement.
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLSSIGDNGER BAYER 20L" is getting converted to "FL?SSIGD?NGER BAYER
> 20L". I need to load the data as it is in the file. I am using the
> following
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
> =
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||I have tried "DATAFILETYPE='widechar'" but it doesn't help.
"oj" wrote:

> Add "DATAFILETYPE='widechar'" to your statement.
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
>
>|||This quick example is working fine here. I will need more info/data in order
to help.
use Northwind
go
create table test(i int, j nvarchar(255) collate
SQL_Latin1_General_CP1_CI_AS)
go
insert test select 1,N'FLSSIGDNGER BAYER 20L'
go
declare @.sql nvarchar(1000)
set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
+'" -T -w -t"|"'
print @.sql
exec master..xp_cmdshell @.sql
truncate table test
set @.sql='bulk insert test
from ''c:\test.out''
with(fieldterminator=''|'',datafiletype=
''widechar'')'
print @.sql
exec(@.sql)
select * from test
go
drop table test
go
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>I have tried "DATAFILETYPE='widechar'" but it doesn't help.
> "oj" wrote:
>|||The file is created by a third-party program, which among other columns
contains the description column with these characters. We are reading from
the file (as received) using bulk insert. Here is a sample record. Could
there be a problem with how the text file has been created. following is one
of the records in the file.
CH#CH030#0030#5105949253#0001#20050113#0
00000000000020379#FLüSSIGDüNGER
BAYER 20L
CHF#0020379#0301#BI-WDI- CH#BI_LOAD##0000309132#DE#AGLUKON#Düsse
ldorf
11#40549##Postfach 190 343#0000309132#DE#AGLUKON#Düsseldorf
11#40549##Postfach 190 343# 3840#L# 0000.00#EUR# 0000.04#CHF#627#S.
DERUNGS#20050401#050040# 000.00# 000#CHF
"oj" wrote:

> This quick example is working fine here. I will need more info/data in ord
er
> to help.
> use Northwind
> go
> create table test(i int, j nvarchar(255) collate
> SQL_Latin1_General_CP1_CI_AS)
> go
> insert test select 1,N'FLüSSIGDüNGER BAYER 20L'
> go
> declare @.sql nvarchar(1000)
> set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
> +'" -T -w -t"|"'
> print @.sql
> exec master..xp_cmdshell @.sql
> truncate table test
> set @.sql='bulk insert test
> from ''c:\test.out''
> with(fieldterminator=''|'',datafiletype=
''widechar'')'
> print @.sql
> exec(@.sql)
> select * from test
> go
> drop table test
> go
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>
>|||Codepage = 65001 solves my problem.
"SQL Novice" wrote:

> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER
BAYER
> 20L". I need to load the data as it is in the file. I am using the followi
ng
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
=
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||Thanks for the update.
-oj
"SQL Novice" <SQLNovice@.discussions.microsoft.com> wrote in message
news:7C34DF98-97A9-46B5-B7A3-208CB5B1347B@.microsoft.com...
> Codepage = 65001 solves my problem.
> "SQL Novice" wrote:
>