Skip to Content
avatar image
Former Member

Getting started with BCP

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 07, 2015 at 04:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 07, 2015 at 04:22 PM

    I'd suggest a couple options:

    ----------------------- option #1

    NOTE: I don't normally use format files so I don't recall (off the top of my head) the proper/correct format of the *fmt file, so short of re-reading the manuals I use the following method to generate a properly/correctly formatted *fmt file:

    - use the INSERT command to populate the Sybase table with a few of your data values (4-digit, 5-digit, 8-digit, etc)

    - use bcp to pull the data from your table and have bcp create the *fmt file for you

    - try using the *fmt file (created by bcp/out) to bcp your data file into the table

    ----------------------- option #2

    Since your data file is just integers with a <lf>, consider skipping the format file ...

    bcp dbname.owner.TO_INTS in "SomeInts.txt" -P password -S server -U sa -c -r "\n" -e "bcp.errlog.txt"


    The -r option allows you to designate the row delimiter.


    NOTE: To be honest I don't recall (off the top of my head, again) if <lf> is '\n' or '\r' or '\n\r' or '\r\n', so try each to see which works best. I usually use the UNIX 'od' command to verify the line terminator.  I'd suggest using a small file (eg, 5-10 values) to make sure you've got the right -r setting before trying to load a huge data set.


    NOTE: The -t option allows you to define the column delimiter, eg, -t '\t' says you've got a tab between each column.

    Add comment
    10|10000 characters needed characters exceeded