title: What bcp format file that I'm gonna used to insert 59 columns?
body: <p>First Observation, limitation with this BCP format file
  Data File: (Continuous up to three lines)</p>
  
  <pre><code>1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 
  4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 
  6;p;q;r;s;t;u;;What bcp format file that I'm gonna used to insert 59 columns?
  </code></pre>
  
  <p>Format File:</p>
  
  <pre><code>9.0
  9
  1  SQLCHAR 0 0 "" 0 x ""
  2  SQLCHAR 0 0 ";" 2  col1  Latin1_General_CI_AS
  3  SQLCHAR 0 0 ";" 3  col2  Latin1_General_CI_AS
  4  SQLCHAR 0 0 ";" 4  col3  Latin1_General_CI_AS
  5  SQLCHAR 0 0 ";" 5  col4  Latin1_General_CI_AS
  6  SQLCHAR 0 0 ";" 6  col5  Latin1_General_CI_AS
  7  SQLCHAR 0 0 ";" 7  col6  Latin1_General_CI_AS
  8  SQLCHAR 0 0 ";" 8  col7  Latin1_General_CI_AS
  9  SQLCHAR 0 0 "" 0 x ""
  </code></pre>
  
  <p>Table structure</p>
  
  <pre><code>Create Table bcp2 (id bigint identity(1,1), col1 varchar(2), col2 varchar(2), col3 varchar(2), col4 varchar(2), col5 varchar(2), col6 varchar(2), col7 varchar(2), dte bit default 0)
  </code></pre>
  
  <p>When I try to add a another row, it gives this error message,</p>
  
  <p>Msg 4832, Level 16, State 1, Line 1
  Bulk load: An unexpected end of file was encountered in the data file.
  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
  Msg 7330, Level 16, State 2, Line 1
  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".</p>
  
  <p>If I arrange the data like this, it gives an error message,
  Msg 8152, Level 16, State 14, Line 1
  String or binary data would be truncated.
  The statement has been terminated.</p>
  
  <p>This arrangement (three continuous lines) gives an error,</p>
  
  <pre><code>1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 
  4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 
  6;p;q;r;s;t;u;;
  </code></pre>
  
  <p>This arrangement (one line) does not give error, it will insert all 6 rows,</p>
  
  <pre><code>1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 6;p;q;r;s;t;u;;
  </code></pre>
  
  <p>Second observation, This arrangement of data applies to specific format file</p>
  
  <p>Data file and its arrangement:</p>
  
  <pre><code>1;a;b;c;d;e;f;g;h;i;; 2;d;e;f;g;h;i;j;k;l;; 3;g;h;i;j;k;l;m;n;o;; 
  4;j;k;l;m;n;o;p;q;r;; 5;m;n;o;p;q;r;s;t;u;; 
  6;p;q;r;s;t;u;v;w;x;;
  </code></pre>
  
  <p>... it inserts three rows</p>
  
  <pre><code>1;a;b;c;d;e;f;g;h;i;; 2;d;e;f;g;h;i;j;k;l;; 3;g;h;i;j;k;l;m;n;o;; 4;j;k;l;m;n;o;p;q;r;; 5;m;n;o;p;q;r;s;t;u;; 6;p;q;r;s;t;u;v;w;x;;
  </code></pre>
  
  <p>... it insert just one row</p>
  
  <p>with this kind of format file:</p>
  
  <pre><code>9.0
  12
  1  SQLCHAR 0 0 "" 0 x ""
  2  SQLCHAR 0 0 ";" 2  col1  Latin1_General_CI_AS
  3  SQLCHAR 0 0 ";" 3  col2  Latin1_General_CI_AS
  4  SQLCHAR 0 0 ";" 4  col3  Latin1_General_CI_AS
  5  SQLCHAR 0 0 ";" 5  col4  Latin1_General_CI_AS
  6  SQLCHAR 0 0 ";" 6  col5  Latin1_General_CI_AS
  7  SQLCHAR 0 0 ";" 7  col6  Latin1_General_CI_AS
  8  SQLCHAR 0 0 ";" 8  col7  Latin1_General_CI_AS
  9  SQLCHAR 0 0 ";" 9  col8  Latin1_General_CI_AS
  10 SQLCHAR 0 0 ";" 10 col9  Latin1_General_CI_AS
  11 SQLCHAR 0 0 ";" 11 col10 Latin1_General_CI_AS
  12 SQLCHAR 0 0 "\r\n" 0 x ""
  </code></pre>
  
  <p>Table structure remains the same,</p>
  
  <pre><code>Create Table bcp (id bigint identity(1,1), col1 varchar(2), col2 varchar(2), col3 varchar(2), col4 varchar(2), col5 varchar(2), col6 varchar(2), col7 varchar(2), col8 varchar(2), col9 varchar(2), col10 varchar(2), dte bit default 0)
  </code></pre>
  
  <p>Now, my question, what format file that I'm gonna use to get all data from all 59 columns to be inserted properly?</p>
  
  <p>My actual data (part of it):</p>
  
  <pre><code>087728127;V1.10;R;WithMedia;58-1231925646@208.74.75.250;0002;Wed Jan 14 09:34:06 2009;NA;Wed Jan 14 09:35:06 2009;0;Origination CANCEL; 487;O;000054;SIP;0067*116;116.93.51.23;01149614244656;208.74.75.250;5372a7cc31a45f9a237780a125ce14ca@116.93.51.23;116.93.51.23;14840;208.74.75.250;12348;G.711u_64k,G.711a_64k,RFC 2833;0;0;0;0;0;0;0;050500;SIP;3072151001;208.74.75.250;49614244656;208.80.156.52;58-1-1231925646@208.74.75.250;0.0.0.0;0;208.74.75.250;12350;NA;0;0;0;0;0;0;0;F;01149614244656;0;60;0;0;0000003A 496DB18E 484B6AB4 FA4B4AD0;;
  087728128;V1.10;R;WithMedia;62-1231925689@208.74.75.250;0002;Wed Jan 14 09:34:49 2009;NA;Wed Jan 14 09:35:12 2009;0;Origination CANCEL; 487;O;050450;SIP;6326378682;116.93.51.98;011420556844118;208.74.75.250;call-00EC569B-CFE0-DD11-0216-4B6@116.93.51.98;116.93.51.98;11096;208.74.75.250;12364;G.729,RFC 2833;437;799;13988;25568;0;0;0;050001;SIP;6326378682;208.74.75.250;011420556844118;204.9.203.210;62-1-1231925689@208.74.75.250;204.9.203.210;11092;208.74.75.250;12366;G.729,RFC 2833;799;437;25568;13988;0;0;0;F;011420556844118;0;7;16;0;0000003E 496DB1B9 44AC3D38 FA4B4AD0;;
  </code></pre>
  
  <p>... the data is continuous and only separated by spaces after the double semicolon.</p>
  
  <p>This is my actual format file:</p>
  
  <pre><code>9.0
  61
  1       SQLCHAR       0        0      ""         0     x                                      ""
  2       SQLCHAR       0        0      ";"        2     Sequence                               Latin1_General_CP1_CI_AS
  3       SQLCHAR       0        0      ";"        3     Version                                Latin1_General_CP1_CI_AS
  4       SQLCHAR       0        0      ";"        4     RecordType                             Latin1_General_CP1_CI_AS
  5       SQLCHAR       0        0      ";"        5     ConnectionType                         Latin1_General_CP1_CI_AS
  6       SQLCHAR       0        0      ";"        6     SessionID                              Latin1_General_CP1_CI_AS
  7       SQLCHAR       0        0      ";"        7     ReleaseCause                           Latin1_General_CP1_CI_AS
  8       SQLCHAR       0        0      ";"        8     StartTime                              Latin1_General_CP1_CI_AS
  9       SQLCHAR       0        0      ";"        9     AnswerTime                             Latin1_General_CP1_CI_AS
  10      SQLCHAR       0        0      ";"        10    ReleaseTOD                             Latin1_General_CP1_CI_AS
  11      SQLCHAR       0        0      ";"        11    WestofGMT                              Latin1_General_CP1_CI_AS
  12      SQLCHAR       0        0      ";"        12    RelCauseTxt                            Latin1_General_CP1_CI_AS
  13      SQLCHAR       0        0      ";"        13    RelCauseBin                            Latin1_General_CP1_CI_AS
  14      SQLCHAR       0        0      ";"        14    1strelease                             Latin1_General_CP1_CI_AS
  15      SQLCHAR       0        0      ";"        15    OrgTrunkId                             Latin1_General_CP1_CI_AS
  16      SQLCHAR       0        0      ";"        16    OrgProtocol                            Latin1_General_CP1_CI_AS
  17      SQLCHAR       0        0      ";"        17    OrgSrcNo                               Latin1_General_CP1_CI_AS
  18      SQLCHAR       0        0      ";"        18    OrgSrcHost                             Latin1_General_CP1_CI_AS
  19      SQLCHAR       0        0      ";"        19    OrgDestNo                              Latin1_General_CP1_CI_AS
  20      SQLCHAR       0        0      ";"        20    OrgDestHost                            Latin1_General_CP1_CI_AS
  21      SQLCHAR       0        0      ";"        21    OrgCallID                              Latin1_General_CP1_CI_AS
  22      SQLCHAR       0        0      ";"        22    OrgRemPayIPAdd                         Latin1_General_CP1_CI_AS
  23      SQLCHAR       0        0      ";"        23    OrgRemPayUDPAdd                        Latin1_General_CP1_CI_AS
  24      SQLCHAR       0        0      ";"        24    OrgLocPayIPAdd                         Latin1_General_CP1_CI_AS
  25      SQLCHAR       0        0      ";"        25    OrgLocPayUDPAdd                        Latin1_General_CP1_CI_AS
  26      SQLCHAR       0        0      ";"        26    OrgCodecList                           Latin1_General_CP1_CI_AS
  27      SQLCHAR       0        0      ";"        27    OrgIngrPck                             Latin1_General_CP1_CI_AS
  28      SQLCHAR       0        0      ";"        28    OrgEgrPck                              Latin1_General_CP1_CI_AS
  29      SQLCHAR       0        0      ";"        29    OrgIngrOct                             Latin1_General_CP1_CI_AS
  30      SQLCHAR       0        0      ";"        30    OrgEgrOct                              Latin1_General_CP1_CI_AS
  31      SQLCHAR       0        0      ";"        31    OrgIngrPckLoss                         Latin1_General_CP1_CI_AS
  32      SQLCHAR       0        0      ";"        32    OrgIngrDelay                           Latin1_General_CP1_CI_AS
  33      SQLCHAR       0        0      ";"        33    OrgIngrPckJitter                       Latin1_General_CP1_CI_AS
  34      SQLCHAR       0        0      ";"        34    TermTrunkId                            Latin1_General_CP1_CI_AS
  35      SQLCHAR       0        0      ";"        35    TermProtocol                           Latin1_General_CP1_CI_AS
  36      SQLCHAR       0        0      ";"        36    TermSrcNo                              Latin1_General_CP1_CI_AS
  37      SQLCHAR       0        0      ";"        37    TermSrcHost                            Latin1_General_CP1_CI_AS
  38      SQLCHAR       0        0      ";"        38    TermDestNo                             Latin1_General_CP1_CI_AS
  39      SQLCHAR       0        0      ";"        39    TermDestHost                           Latin1_General_CP1_CI_AS
  40      SQLCHAR       0        0      ";"        40    TermCallID                             Latin1_General_CP1_CI_AS
  41      SQLCHAR       0        0      ";"        41    TermRemPayIPAdd                        Latin1_General_CP1_CI_AS
  42      SQLCHAR       0        0      ";"        42    TermRemPayUDPAdd                       Latin1_General_CP1_CI_AS
  43      SQLCHAR       0        0      ";"        43    TermLocPayIPAdd                        Latin1_General_CP1_CI_AS
  44      SQLCHAR       0        0      ";"        44    TermLocPayUDPAdd                       Latin1_General_CP1_CI_AS
  45      SQLCHAR       0        0      ";"        45    TermCodecList                          Latin1_General_CP1_CI_AS
  46      SQLCHAR       0        0      ";"        46    TermIngrPck                            Latin1_General_CP1_CI_AS
  47      SQLCHAR       0        0      ";"        47    TermEgrPck                             Latin1_General_CP1_CI_AS
  48      SQLCHAR       0        0      ";"        48    TermIngrOct                            Latin1_General_CP1_CI_AS
  49      SQLCHAR       0        0      ";"        49    TermEgrOct                             Latin1_General_CP1_CI_AS
  50      SQLCHAR       0        0      ";"        50    TermIngrPckLoss                        Latin1_General_CP1_CI_AS
  51      SQLCHAR       0        0      ";"        51    TermIngrDelay                          Latin1_General_CP1_CI_AS
  52      SQLCHAR       0        0      ";"        52    TermIngrPckJitter                      Latin1_General_CP1_CI_AS
  53      SQLCHAR       0        0      ";"        53    FinRouteInd                            Latin1_General_CP1_CI_AS
  54      SQLCHAR       0        0      ";"        54    RoutingDigits                          Latin1_General_CP1_CI_AS
  55      SQLCHAR       0        0      ";"        55    CallDuration                           Latin1_General_CP1_CI_AS
  56      SQLCHAR       0        0      ";"        56    PostDialDelay                          Latin1_General_CP1_CI_AS
  57      SQLCHAR       0        0      ";"        57    RingTime                               Latin1_General_CP1_CI_AS
  58      SQLCHAR       0        0      ";"        58    Duration                               Latin1_General_CP1_CI_AS
  59      SQLCHAR       0        0      ";"        59    ConfID                                 Latin1_General_CP1_CI_AS
  60      SQLCHAR       0        0      ";"        60    RPIDANI                                Latin1_General_CP1_CI_AS
  61      SQLCHAR       0        0      ""          0    x                                      ""
  </code></pre>
  
  <p>This is my actual table structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[SansayRawInfo](
      [RawDataId] [bigint] IDENTITY(1,1) NOT NULL,
      [Sequence] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Version] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RecordType] [varchar](44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ConnectionType] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SessionID] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ReleaseCause] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [StartTime] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [AnswerTime] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ReleaseTOD] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [WestofGMT] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RelCauseTxt] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RelCauseBin] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [1strelease] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgTrunkId] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgProtocol] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgSrcNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgSrcHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgDestNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgDestHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgCallID] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgRemPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgRemPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgLocPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgLocPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgCodecList] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgIngrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgEgrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgIngrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgEgrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgIngrPckLoss] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgIngrDelay] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrgIngrPckJitter] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermTrunkId] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermProtocol] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermSrcNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermSrcHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermDestNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermDestHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermCallID] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermRemPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermRemPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermLocPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermLocPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermCodecList] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermIngrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermEgrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermIngrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermEgrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermIngrPckLoss] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermIngrDelay] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TermIngrPckJitter] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FinRouteInd] [varchar](44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RoutingDigits] [varchar](74) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CallDuration] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PostDialDelay] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RingTime] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Duration] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ConfID] [varchar](46) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RPIDANI] [varchar](74) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Status] [bit] NULL DEFAULT ((0))
  ) ON [PRIMARY]
  </code></pre>
  
  <p>... Thanks in advance.</p>