Getting started with BCP

[Former Member]

Hi

I've got a couple of issues with bcp.

I'm trying to load data from a text file to Sybase ASE 16.0 . The DB is on my local machine (Win 7 64-bit).

I'm using this command:

bcp dbname.owner.TO_INTS in "SomeInts.txt" -P password -S server -U sa  -f "bcp_tblTO_INTS.fmt" -e "bcp.errlog.txt"

Here is the format file ("bcp_tblTO_INTS.fmt"😞

10.0

1

1 SYBINT4 0 5 "" 1 SEEMS_THIS_IS_IGNORED

The target table has a single column, defined as an INT:

CREATE TABLE owner.TO_INTS

  (

  FROM_INT INT NOT NULL

  )

  LOCK ALLPAGES

  ON 'default'

GO

I have two problems.

1. bcp will only populate the target table if the data has just four characters in it (ie, values from 1000 to 9999). If I use values outside this range I get:

Unexpected EOF encountered in BCP data-file.

bcp copy in failed

2. The values are not being populated correctly:

Input value (in file)               Populated as (in Sybase)

1000                                   808464433

9999                                   960051513

The file encoding is ANSI. The EOL character is LF.

What am I doing wrong?

Thanks

Dave.

[former_member188958 · Accepted solution]

it looks like your data file may be in ascii (human-readable values) format rather than native binary format.

A format file isn't generally needed when you have a ascii data; you would use the -c parameter with bcp rather than the default -n.  Try:

bcp dbname.owner.TO_INTS in "SomeInts.txt" -P password -S server -U sa  -c

-bret

[former_member188958 · Accepted solution]

To add a bit more explanation, the original command sort of worked for 4-digit values is that the 4 ascii characters map to 4 bytes, i.e. "1000" is 0x31303030.   The integer datatype is also 4 bytes, and on little-endian architectures, 0x31303030 is 808464433

2> select convert(binary(4), "1000")
3> go

----------
0x31303030

(1 row affected)
1> select convert(int, 0x31303030)
2> go

-----------
   808464433

(1 row affected)

[Former Member · Accepted solution]

Great, thanks. Using "-c" fixed everything.