views:

881

answers:

2

First Observation, limitation with this BCP format file Data File: (Continuous up to three lines)

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?

Format File:

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 ""

Table structure

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)

When I try to add a another row, it gives this error message,

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)".

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.

This arrangement (three continuous lines) gives an error,

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;;

This arrangement (one line) does not give error, it will insert all 6 rows,

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;;

Second observation, This arrangement of data applies to specific format file

Data file and its arrangement:

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;;

... it inserts three rows

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;;

... it insert just one row

with this kind of format file:

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 ""

Table structure remains the same,

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)

Now, my question, what format file that I'm gonna use to get all data from all 59 columns to be inserted properly?

My actual data (part of it):

087728127;V1.10;R;WithMedia;[email protected];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;[email protected];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;[email protected];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;[email protected];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;[email protected];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;[email protected];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;;

... the data is continuous and only separated by spaces after the double semicolon.

This is my actual format file:

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                                      ""

This is my actual table structure:

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]

... Thanks in advanced.

A: 

Lots of good detail, but you are missing the actual bcp command that you are using.
I suspect a problem with the -c -r and -t options.

In any case, some things to try:

From here

  • Check your line termination options.
  • Hidden characters in the data file.
  • Using spaces for delimiters in a data file.
Noah
A: 

no answer, but need help for format file or any bcp ultility for me to load in data to varchar before loading check if the firt two coloumn is the length of the column and the last column is not null if the column is blank. Thanks a lot