BCP

• Ashesh

  SSCommitted

  Points: 1961

  More actions

  July 26, 2006 at 6:26 am

  #366943

  Hi everybody,

  I require to pick fixed width characters from a flat files to import with BCP utilities. e.g 830-860 colums i require for a field. There is no delimiter present in the flat files.

  Can anybody there help me out.

  Thanks in advance.

  ---

  Thanks,
  Ashesh
  

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  July 26, 2006 at 3:50 pm

  #651761

  Look up 'format files' in BOL.  The section 'Using Format Files' will tell you how to set up a format file that tells BCP what the fixed length of each entry in the file. 

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• Ashesh

  SSCommitted

  Points: 1961

  More actions

  July 27, 2006 at 1:16 am

  #651800

  Hi!

  Thanks for the response. I've already scanned thru BOL & tried to get something from net in this issue. The BOL section which you have mentioned works verywell when delimiter character can be identified. However my requirement is just like I want substring from the record by defining positions for the field.

   

  Thanks again.

  ---

  Thanks,
  Ashesh
  

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  July 27, 2006 at 2:42 am

  #651810

  Look again at the BCP format file in BOL, then look at the Fewer and More fields examples, notice that "" is valid for the Terminator column, in this situation BCP will use the Host file data length to parse the fixed length columns into fields:

  The Authors.fmt file for fixed length format text files:

  8.0

  9

  1  SQLCHAR  0  11 ""       1  au_id       SQL_Latin1_General_Cp437_BIN

  2  SQLCHAR  0  40 ""       2  au_lname    SQL_Latin1_General_Cp437_BIN

  3  SQLCHAR  0  20 ""       3  au_fname    SQL_Latin1_General_Cp437_BIN

  4  SQLCHAR  0  12 ""       4  phone       SQL_Latin1_General_Cp437_BIN

  5  SQLCHAR  0  40 ""       5  address     SQL_Latin1_General_Cp437_BIN

  6  SQLCHAR  0  20 ""       6  city        SQL_Latin1_General_Cp437_BIN

  7  SQLCHAR  0  2  ""       7  state       SQL_Latin1_General_Cp437_BIN

  8  SQLCHAR  0  5  ""       8  zip         SQL_Latin1_General_Cp437_BIN

  9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN

  I agree that BOL could have done a better job of documenting this kind of thing.

  Andy