0

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
Joy Rex
  • 608
  • 7
  • 32
user3820903
  • 23
  • 1
  • 5

2 Answers2

2

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
Marco Vargas
  • 1,232
  • 13
  • 31
  • Have you gotten this to work. I remember spending a lot of time testing different options without success. – Mike Gardner Nov 15 '16 at 15:37
  • Yes Sr, actually I also had issues when trying to run it the first time, and read these posts, then when finally it worked for me, I posted the answer here... – Marco Vargas Nov 16 '16 at 18:05
-1

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.

Community
  • 1
  • 1
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34