sql server programming: Need to Bulk Insert from a txt file:
***************************************

Problem Description - There are special characters present in the file. For
e.g., Montréal, Côte-de-Liesse… through Bulk Insert, they are getting saved
in SQL Server as Montr+¬al, C+¦te-de-Liesse…
-----------------------------------------
Bulk insert script:

bulk insert tblName from 'C:\Sample.txt'
with (formatfile = 'C:\FmtFile.FMT', firstRow = 2, rows_per_batch = 100,
Keepnulls, tablock)
-----------------------------------------
FmtFile.FMT

7.0
2
1 SQLCHAR 0 50 "|" 1 CODE
2 SQLCHAR 0 100 "\r\n" 2 NAME
-----------------------------------------
Sample.txt

Code|Name
ABC|Montréal
XYZ|Côte-de-Liesse
-----------------------------------------
table schema - tblName

Create table tblName
(
Code varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
-----------------------------------------

Note:
When we use simple Insert statement, it works fine
create table #t (c varchar(100))
insert into #t select 'Montréal'
select * from #t
drop table #t
Montréal will get saved as Montréal only, whereas the behavior varies when
we use Bulk Insert.

Sorry. Ignore my previous post
try tho use this option in your bulk insert
CODEPAGE = 'RAW'

and if that doesn't work

CODEPAGE = 'ACP'

Haven't tried these options. Let me know which one works.




[quoted text, click to view]