bcp changing high ascii values

Question

bcp changing high ascii values

Stephen Grimshaw

Right there with Babe

Points: 738
More actions
December 18, 2017 at 9:56 am

#326940

bcp appears to be changing some high ascii values on import (eg 249 to 168). How can I control/prevent this behaviour?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Stephen Grimshaw - Monday, December 18, 2017 9:56 AM
bcp appears to be changing some high ascii values on import (eg 249 to 168). How can I control/prevent this behaviour?

More information please!
😎

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 2

1. Table
create table ascii_test(string varchar(15))

2. input file attached

3. bcp command
bcp ascii_test in diuc.txt -c -T

4. Here's query on what arrives in the database
select seq,substring(string,seq,1),ascii(substring(string,seq,1))
from ascii_test, (select top 12 seq=row_number() over(order by crdate) from sysobjects) a

5. Here's the result
1 D 68
2 I 73
3 ¨ 168
4 C 67
5 32
6 E 69
7 D 68
8 W 87
9 A 65
10 R 82
11 D 68
12 S 83

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

and the server/db/table collation?
😎
What does this return?
SELECT char(249) AS FROM_CHAR ,char(168) AS TO_CHAR ;

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 4

Stephen Grimshaw

Right there with Babe

Points: 738

December 18, 2017 at 10:44 am

#1972556

FROM_CHAR TO_CHAR
ù ¨

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Eirikur Eiriksson

SSC Guru

Points: 182912

December 18, 2017 at 10:50 am

#1972557

And the collation?
😎

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 6

Thank you for your help on this.
Collation is Latin1_General_CI_AS

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Stephen Grimshaw - Monday, December 18, 2017 10:18 AM
1. Table
create table ascii_test(string varchar(15))
2. input file attached
3. bcp command
bcp ascii_test in diuc.txt -c -T
4. Here's query on what arrives in the database
select seq,substring(string,seq,1),ascii(substring(string,seq,1))
from ascii_test, (select top 12 seq=row_number() over(order by crdate) from sysobjects) a
5. Here's the result
1 D 68
2 I 73
3 Â¨ 168
4 C 67
5 32
6 E 69
7 D 68
8 W 87
9 A 65
10 R 82
11 D 68
12 S 83

???

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Lynn Pettis - Monday, December 18, 2017 11:05 AM
Stephen Grimshaw - Monday, December 18, 2017 10:18 AM
1. Table
create table ascii_test(string varchar(15))
2. input file attached
3. bcp command
bcp ascii_test in diuc.txt -c -T
4. Here's query on what arrives in the database
select seq,substring(string,seq,1),ascii(substring(string,seq,1))
from ascii_test, (select top 12 seq=row_number() over(order by crdate) from sysobjects) a
5. Here's the result
1 D 68
2 I 73
3 Â¨ 168
4 C 67
5 32
6 E 69
7 D 68
8 W 87
9 A 65
10 R 82
11 D 68
12 S 83
???

1. Table
create table ascii_test(string varchar(15))

2. input file attached

3. bcp command
bcp ascii_test in diuc.txt -c -T

4. Here's query on what arrives in the database
select seq,substring(string,seq,1),ascii(substring(string,seq,1))
from ascii_test, (select top 12 seq=row_number() over(order by crdate) from sysobjects) a

5. Here's the result
1 D 68
2 I 73
3 Â¨ 168
4 C 67
5 32
6 E 69
7 D 68
8 W 87
9 A 65
10 R 82
11 D 68
12 S 83

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

Hmmm... what you may want to try is adding the following "switch" to the BCP command...

-C RAW

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 10

Thank you very much. That seems to do the trick.
Now that I have the hint where to look I'll read the right documentation!

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 11

Stephen Grimshaw - Tuesday, December 19, 2017 2:39 AM
Thank you very much. That seems to do the trick.
Now that I have the hint where to look I'll read the right documentation!

Awesome... thank you for the feedback, Stephen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)