BCP

• David Kranes

  SSCrazy

  Points: 2523

  More actions

  June 17, 2008 at 11:43 am

  #208466

  Hello I am trying to use BCP to load a table and it keeps failing. Herer is the table definition:

  CREATE TABLE [dbo].[tbl1(

  [tender_id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,

  [store_num] [numeric](5, 0) NOT NULL,

  [register_num] [char](3) NOT NULL,

  [trans_num] [int] NOT NULL,

  [transaction_time] [datetime] NOT NULL,

  [acct_num_type_code] [char](3) NOT NULL,

  [acct_num] [varchar](40) NOT NULL,

  CONSTRAINT [XPKtbl1] PRIMARY KEY CLUSTERED

  (

  [tender_id] ASC,

  [store_num] ASC,

  [register_num] ASC,

  [trans_num] ASC,

  [transaction_time] ASC,

  [acct_num_type_code] ASC,

  [acct_num] ASC

  ) ON [TRANSTNDDATA]

  Here is sample date:

  2110320|10|003|78309|2008-01-05 14:41:00|006|5449178795223373|

  2110321|2|002|65723|2008-01-05 14:42:00|006|5459790000223302|

  2110322|9|003|22217|2008-01-05 14:42:00|006|371555925922335|

  2110323|9|004|55784|2008-01-05 14:42:00|006|54436877092323202|

  2110324|15|005|86666|2008-01-05 14:42:00|006|42642804080523232|

  2110325|14|003|61317|2008-01-05 14:42:00|008|901230732323232|

  2110326|14|003|61317|2008-01-05 14:42:00|008|901230754232323|

  2110327|14|003|61317|2008-01-05 14:42:00|006|37252664748232323|

  2110328|11|002|40123|2008-01-05 14:42:00|006|51139201202323232|

  2110329|10|003|78310|2008-01-05 14:42:00|006|371713962454545|

  2110330|6|001|35287|2008-01-05 14:43:00|006|47192781592435234|

  2110331|14|003|61318|2008-01-05 14:44:00|006|42717824234237010|

  2110332|8|002|53381|2008-01-05 14:43:00|008|901224234454544424|

  2110333|8|003|64626|2008-01-05 14:44:00|006|414781456456456411|

  2110334|9|004|55785|2008-01-05 14:44:00|006|54661600567567567|

  2110335|6|003|18334|2008-01-05 14:44:00|006|41475756756756753|

  2110336|4|004|66851|2008-01-05 14:44:00|006|529149745645645233|

  How do I formulate the bcp command and specify "|" as the field terminator.

  Thank you

  -David

  Best Regards,

  ~David

• Todd Engen

  SSCrazy Eights

  Points: 9869

  More actions

  June 17, 2008 at 12:38 pm

  #829981

  What's the error message you're getting when it fails?

  You should be able to specify the '|' as the column delimeter and a '|' plus a newline character as the row delimiter like this.

  bcp DB1.dbo.tbl1 in c:\import\myinput.txt -c -t"|" -r"|\n" -T -E

  Edit: Added -E for identity column

• David Kranes

  SSCrazy

  Points: 2523

  More actions

  June 17, 2008 at 1:23 pm

  #830004

  It says 0 rows copied whth an EOF comminication error.

  -David

  Best Regards,

  ~David

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  June 17, 2008 at 8:39 pm

  #830166

  Get something that's capable of opening the file in the "binary" mode... I'm betting that the end of line character is neither a carriage return (0d in hex) nor a new line character (0a in hex). My guess from what you posted is that it's 0b in hex... a vertical tab character.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)