CODEWITHSUNDEEP
sybasesap-asesqlbulkcopybcpbulk
user3820903
user3820903

Reputation: 23

Skip host file column in bcp format file Sybase

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

Upvotes: 0

Views: 1421

Answers (2)

Marco Vargas
Marco Vargas

Reputation: 1327

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

Upvotes: 2

Mike Gardner
Mike Gardner

Reputation: 6651

ASE format files can't be used to omit, reorder or skip columns the way they can in SQL Server. In this case you should bcp into a temp table, and then select the rows into the production table.

Alternatively, you could create a script (python, shell, etc) that would pre-process the file to remove the unneeded columns.

Upvotes: -1

Related Questions