Skip host file column in bcp format file Sybase

bcp bulk sqlbulkcopy sybase sybase-ase

Question

I have a csv texte file like this :

SicID;PorID;FarId;Company;DecadeId;SocName;FarName
A1;A2;A3;A4;A5;A6;A7
B1;B2;B3;B4;B5;B6;B7
C1;C2;C3;C4;C5;C6;C7

I want to use Bcp Sybase to import this file in my_table but only the SicID, PorID and SocName. So I use this bcp format file :

10.0
8
1   SYBCHAR 0   2   ";" 1   SicId
2   SYBCHAR 0   2   ";" 2   PorId
3   SYBCHAR 0   0   ""  0   FundId
4   SYBCHAR 0   0   ""  0   Company
5   SYBCHAR 0   0   ""  0   DomicileId
6   SYBCHAR 0   2   ";" 3   SocName
7   SYBCHAR 0   0   ""  0   FundName
8   SYBCHAR 0   10  "\r\n" 0    end

Unfortunately, when I import it, my_table doesn't have the good data. It doesn't skip the 3,4,5 host column of the text file and fill my_table with other column with incomprehensible sense.

Is there a solution to skip host column to fill my table like this ?

My_table

SicID;PorID;SocName
A1;A2;A6
B1;B2;B6
C1;C2;C6

Popular Answer

The issue you have is that there is no terminator character on the columns you want to omit, please check the following image (I got it from here):

enter image description here

Basically, if you want to omit a column coming in the source file, just:

  1. Use as Server Column Order = 0
  2. The terminator has to be specified!
  3. The length of the source file is also important.
  4. The server column name is just informative

For example, using the sample data you provided on your question, your .fmt file should looks like this:

10.0
8
1   SYBCHAR 0   2   ";"    1   SicId
2   SYBCHAR 0   2   ";"    2   PorId
3   SYBCHAR 0   2   ";"    0   FarId
4   SYBCHAR 0   2   ";"    0   Company
5   SYBCHAR 0   2   ";"    0   DecadeId
6   SYBCHAR 0   2   ";"    3   SocName
7   SYBCHAR 0   2   ";"    0   FarName
8   SYBCHAR 0   10  "\r\n" 0   end


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why