Skip host file column in bcp format file Sybase

bcp bulk sqlbulkcopy sybase sybase-ase

Question

I have the following CSV text file:

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

Only the SicID, PorID, and SocName from this file should be imported into my table using BCP Sybase. So I use the following 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, my table doesn't contain the accurate data when I import it. The text file's 3, 4, and 5 host columns are not skipped, and my table is not filled with additional columns that make no sense.

Is there a way to populate my table this way without using the host column?

My_table

SicID;PorID;SocName
A1;A2;A6
B1;B2;B6
C1;C2;C6
1
0
8/5/2015 10:04:24 AM

Popular Answer

Your problem is that the columns you wish to remove don't include terminator characters. To confirm, look at the following picture (It came from zherez.):

enter image description here

Basically, you may omit a column from the source file by doing the following:

  1. Use with Server Column Order set to 0.
  2. It is necessary to specify the terminator!
  3. Another crucial factor is the original file's size.
  4. The name of the server column is only descriptive.

Your.fmt file, for instance, should have the following appearance given the sample data you gave in your question:

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
2
10/28/2016 6:10:22 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow