title: how to specify row delimiter when using bcp with a format file
body: <p>I am using <code>bcp</code> utility to import data into <code>Sybase</code> database on <code>Solaris</code> system</p>
  
  <p>Since the bcp file is generated by another xml parsing script, I have no control on that to make it generate the fields in the order I want. And now the the order of fields in the bcp file is a little different with the order in the database table.</p>
  
  <p>I want to use a format file for bcp tool to control the order of fields loaded into the database, so I have the sample bcp file as below and constructed a format file accordingly:</p>
  
  <p>the bcp file:</p>
  
  <pre><code>603289|Aug 20 2011 12:00AM|YYY aaa OVD|KLYYP8
  603284|Aug 22 2011 12:00AM|XXX bbb OVD|KLPK06
  </code></pre>
  
  <p>the format file:</p>
  
  <pre><code>10.0
  4
  1  SYBCHAR   0  12   "|" 3 ver
  2  SYBCHAR   0  26   "|" 2 first_dt
  3  SYBCHAR   0  60   "|" 4 name1
  4  SYBCHAR   0  10   "|" 1 name2
  </code></pre>
  
  <p>Although I am stuck on the following error:</p>
  
  <pre><code>$bcp my_db..my_tbl in test.bcp -e error -f format.fmt -r\\n -S Sever -U user -P pw
  
  Starting copy...
  CSLIB Message:  - L0/O0/S0/N24/1/0:
  cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
  Unexpected EOF encountered in BCP data-file.
  bcp copy in partially failed
  
  1 rows copied.
  </code></pre>
  
  <p>I suspect the cause for the error is the bcp utility can't recognize the row delimiter which is '\n' character (I have used <code>od -c test.bcp</code> to check this), although the <code>-r\\n</code> option in the command seems doesn't work, which should specify the row delimiter as '\n' character.</p>
  
  <p>Does anyone have a clue?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I did a change on the format file and it works fine now, I changed the field delimiter for the last field from '|' to '\n' as below:</p>
  
  <p>the <strong>new</strong> format file:</p>
  
  <pre><code>10.0
  4
  1  SYBCHAR   0  12   "|" 3 ver
  2  SYBCHAR   0  26   "|" 2 first_dt
  3  SYBCHAR   0  60   "|" 4 name1
  4  SYBCHAR   0  10   "\n" 1 name2
  </code></pre>
  
  <p>As Michael mentioned in comments, it's tricky to use format files. When using the original format file I have tried to add '|' at end of each records, although the file were not processed correctly.</p>
  
  <p>Now the file can be processed correctly, although I am not sure what is used as row delimiter by bcp utility</p>