CODEWITHSUNDEEP
linuxunixsap-asebcp
Cynthia
Cynthia

Reputation: 181

bcp format file and -c switch out

I am trying to use bcp to extract records from a Sybase ASE database and write them to a file. I need to exclude two of the fields in the database, so I'm trying to use a format .fmt file.

This is my bcp:

bcp Rdevdb..TBL_XFER out out1.txt -S$serv -U$user -P$pass -fbcp.fmt -c -t\|

I need the | char to be the delimiter.

  1. When I use the -c switch, it seems like it's ignoring the format file all together, is this true?

  2. When I use neither the -n or the -c switch (to tell it what format, native or char) it seems to create the file using native format, for when I open it in my text editor it forces it into a hex editor... is this the case?

  3. when I include the -c, and in the format file I remove the two fields that I do not want, it brings all the db fields into the file, that's why I'm thinking it'd ignoring the .fmt file.

  4. If I remove the -c switch, and run it with the two fields not included into the .fmt file, I get this error: Incorrect host-column number found in bcp format-file.

  5. What combination of switches and what do I include in the .fmt file in order to get my bcp command to bringing me the results I want?

Here's a portion of the .fmt file. There are no spaces in any of the table field names (this does not include field 1 or field 2, as those are the ones I do not want in the file).

10.0
147
3   SYBCHAR 2   6   "|" 3   COVERAGE_CODE
4   SYBCHAR 2   6   "|" 4   DEPT
5   SYBCHAR 2   8   "|" 5   ORG
6   SYBCHAR 2   8   "|" 6   DIV
7   SYBCHAR 2   8   "|" 7   DISTRIBUTE
...

There are 147 fields in the table, though when I include all the fields in the .fmt file, there are a total of 149 with the first two lines that designate the version and the field number.

Thanks for any help.

Upvotes: 2

Views: 2247

Answers (1)

user1505078
user1505078

Reputation: 119

Alternatively you can create a view of the table with only the columns(or even rows) you are interested in, and then use bcp against the view. It seems likely that you are transferring data to a new repository, so you will probably want to stick with using -c as character bcp's are more sharable than native files.

I have not used the -f flag, so I can't really speak to making it work that way.

Upvotes: 2

Related Questions