French characters are not imported properly with bcp

[Wael]

Hi
I have a script that uses bcp to import data from an ascii text file
into SQL tables. The french characters are not copied properly. They
are converted to letters of the alphabet. I tried to change all the
fields to nvarchar instead of varchar and nchar instead of char, but I
got Greek characters instead.

How can I fix this?

Here is some code:
--------------------------
CREATE TABLE [dbo].[1_HLGT_HLT_COMP _f9.0] (
[hlgt_code] [int] NOT NULL ,
[hlt_code] [int] NOT NULL
) ON [PRIMARY]
GO

PRINT 'HLGT_HLT'
DECLARE @s as nvarchar(300)
SET @s='bcp MedDRA..[1_hlgt_hlt_comp _f9.0] in ' + char(34) +
'F:\MedDRA\9.0F rench\MedAscii\ hlgt_hlt.asc' + char(34) + ' -c -t' +
char(34) + '$' + char(34) + ' -r$\n -e' + char(34) +
'F:\MedDRA\9.0F rench\logs\hlgt _hlt.err.txt' + char(34) + ' -b250 -m50
-SDEV -Usa -Ppassword -h' + char(34) + 'TABLOCK' + char(34)
EXEC master..xp_cmds hell @s

[Erland Sommarskog]

Re: French characters are not imported properly with bcp

Wael (sedky@rocketma il.com) writes:[color=blue]
> I have a script that uses bcp to import data from an ascii text file
> into SQL tables. The french characters are not copied properly. They
> are converted to letters of the alphabet. I tried to change all the
> fields to nvarchar instead of varchar and nchar instead of char, but I
> got Greek characters instead.[/color]

Add -C RAW to the list of BCP options. By default, BCP assumes that
characters are in the OEM code page and will convert them to the ANSI
code page. Which results in a mess when data is already in the ANSI
code page.

[color=blue]
> How can I fix this?
>
> Here is some code:
> --------------------------
> CREATE TABLE [dbo].[1_HLGT_HLT_COMP _f9.0] (
> [hlgt_code] [int] NOT NULL ,
> [hlt_code] [int] NOT NULL
> ) ON [PRIMARY]
> GO[/color]

Eh, there are character columns in that table?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wael]

Re: French characters are not imported properly with bcp

Does this require any special install on the server? I tried -C863 for
Canadian French, but the server does not recognize that code page.

[Erland Sommarskog]

Re: French characters are not imported properly with bcp

Wael (sedky@rocketma il.com) writes:[color=blue]
> Does this require any special install on the server? I tried -C863 for
> Canadian French, but the server does not recognize that code page.[/color]

I have not heard of 863 before, but it sounds like an OEM code page.

Playing around a little, it appears that you can only use code pages
for which there are one or more collations. For instace, I tried CP852
which is Eastern Europe, but this gave me an error. On the the other
hand, 874 worked. (There are a number of Thai collations with that code
page.)

But is your file really in code page 863? And in such case how different
would it be from code page 850? (My standard source for checking out
code pages is down, so I can't check right now.)

Did you try -C RAW?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wael]

Re: French characters are not imported properly with bcp

I tried -C RAW but i got an error. If you check the windows regional
settings, you'll see 863 is the code page for Canadian French.

Regional and Language Options\Advance d

[Erland Sommarskog]

Re: French characters are not imported properly with bcp

Wael (sedky@rocketma il.com) writes:[color=blue]
> I tried -C RAW but i got an error.[/color]

An error? Care to specify what? It's difficult to assist without knowing
the error message.
[color=blue]
> If you check the windows regional
> settings, you'll see 863 is the code page for Canadian French.
>
> Regional and Language Options\Advance d[/color]

Yes, I also see that it is an OEM code page. Is the data file in this
code page?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wael]

Re: French characters are not imported properly with bcp

For question number 1, the error is pasted below.
For the second one I am not sure. The original text is a plain text
file.

NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 863 is
not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column
level collations
NULL
BCP copy in failed
NULL

(9 row(s) affected)

[Erland Sommarskog]

Re: French characters are not imported properly with bcp

Wael (sedky@rocketma il.com) writes:[color=blue]
> For question number 1, the error is pasted below.
> For the second one I am not sure. The original text is a plain text
> file.[/color]

If you the open file in Notepad, does it look good then? If it does,
it is not an OEM file. If the French characters are replaced with
other single characters, it could be CP863.

If the file looks bad in Notepad, does it look good when you type it
from a command-line window.

I'm a little surprised of the message you got with -C RAW. I was under
the impression that this would suppress all code-page conversion.

Anyway, if the file looks good in Notepad, use -C ACP or -C 1252.

If it looks goot in the command-line window, try -C 850. I don't know
about CP 863, but I would expected to be a version of CP 850.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wael]

Re: French characters are not imported properly with bcp

It worked. ACP worked.

THank you very much.