How to save data from a semicolon delimited file using Bulk Insert in SQL Server 2005 and BCP 9?

Question

Here is my sample data: 1;a;b;c;; 2;d;e;f;; 3;g;h;i;; 4;j;k;l;; 5;m;n;o;; 6;p;q;r;;

Here is my sample format file (BCP 9): 9.0 7 1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS 2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS 3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS 4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS 5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS 6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS 7 SQLCHAR 0 0 "\r\n" 0 x Latin1_General_CI_AS

Here is the table structure: create table bcp (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), v varchar(2), dte datetime default getdate())

Problem is that when I used "\r\n", only 1, 4, 6 is save not all, but when I try to use "\0", only first record is save. How I am gonna solve this problem?

Another Question, what is rowterminator for blank spaces after semicolon?

Thanks!

Answer 1

OK, I got this to work for me. I used the following...

Data file (based on your example)...

1;a;b;c;; 
2;d;e;f;; 
3;g;h;i;; 
4;j;k;l;; 
5;m;n;o;; 
6;p;q;r;;

Each line was terminated with a return char - not a continuous line as in your example

Then used the format file...

9.0
7
1 SQLCHAR 0 0 "" 0 x ""
2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 v Latin1_General_CI_AS
7 SQLCHAR 0 0 "" 0 x ""

And got the result set...

  1 1 a b c NULL 20/01/2009 10:17:51
  2 2 d e f NULL 20/01/2009 10:17:51
  3 3 g h i NULL 20/01/2009 10:17:51
  4 4 j k l NULL 20/01/2009 10:17:51
  5 5 m n o NULL 20/01/2009 10:17:51
  6 6 p q r NULL 20/01/2009 10:17:51

I believe the key here is in how to skip the columns for the identity field and the default value in your destination table. There is a good MSDN article on this here

Answer 2

Sorry, it is a continous line ... the sample data that I have given, Rich is just sample, this is the 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;;

As you can see, some columns have spaces in between, and the last column (59) is null, when you try open it in excel, semicolon delimited, you will notice that the last one is null ... It will shows on excel, only 58 columns but actually, it is 59 columns, I used that sample data as similar to this one.

Well anyway, I will try your suggestion.

Here is the format file:

9.0 61 1 SQLCHAR 0 0 "" 0 RawDataId SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 20 ";" 2 Sequence SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 30 ";" 3 Version SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 44 ";" 4 RecordType SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 26 ";" 5 ConnectionType SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 42 ";" 6 SessionID SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 14 ";" 7 ReleaseCause SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 42 ";" 8 StartTime SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 42 ";" 9 AnswerTime SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 42 ";" 10 ReleaseTOD SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 42 ";" 11 WestofGMT SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 42 ";" 12 RelCauseTxt SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 28 ";" 13 RelCauseBin SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 22 ";" 14 1strelease SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 64 ";" 15 OrgTrunkId SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 16 ";" 16 OrgProtocol SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 0 138 ";" 17 OrgSrcNo SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 138 ";" 18 OrgSrcHost SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 0 138 ";" 19 OrgDestNo SQL_Latin1_General_CP1_CI_AS 20 SQLCHAR 0 138 ";" 20 OrgDestHost SQL_Latin1_General_CP1_CI_AS 21 SQLCHAR 0 138 ";" 21 OrgCallID SQL_Latin1_General_CP1_CI_AS 22 SQLCHAR 0 26 ";" 22 OrgRemPayIPAdd SQL_Latin1_General_CP1_CI_AS 23 SQLCHAR 0 16 ";" 23 OrgRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS 24 SQLCHAR 0 26 ";" 24 OrgLocPayIPAdd SQL_Latin1_General_CP1_CI_AS 25 SQLCHAR 0 16 ";" 25 OrgLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS 26 SQLCHAR 0 138 ";" 26 OrgCodecList SQL_Latin1_General_CP1_CI_AS 27 SQLCHAR 0 20 ";" 27 OrgIngrPck SQL_Latin1_General_CP1_CI_AS 28 SQLCHAR 0 20 ";" 28 OrgEgrPck SQL_Latin1_General_CP1_CI_AS 29 SQLCHAR 0 20 ";" 29 OrgIngrOct SQL_Latin1_General_CP1_CI_AS 30 SQLCHAR 0 20 ";" 30 OrgEgrOct SQL_Latin1_General_CP1_CI_AS 31 SQLCHAR 0 20 ";" 31 OrgIngrPckLoss SQL_Latin1_General_CP1_CI_AS 32 SQLCHAR 0 20 ";" 32 OrgIngrDelay SQL_Latin1_General_CP1_CI_AS 33 SQLCHAR 0 20 ";" 33 OrgIngrPckJitter SQL_Latin1_General_CP1_CI_AS 34 SQLCHAR 0 64 ";" 34 TermTrunkId SQL_Latin1_General_CP1_CI_AS 35 SQLCHAR 0 16 ";" 35 TermProtocol SQL_Latin1_General_CP1_CI_AS 36 SQLCHAR 0 138 ";" 36 TermSrcNo SQL_Latin1_General_CP1_CI_AS 37 SQLCHAR 0 138 ";" 37 TermSrcHost SQL_Latin1_General_CP1_CI_AS 38 SQLCHAR 0 138 ";" 38 TermDestNo SQL_Latin1_General_CP1_CI_AS 39 SQLCHAR 0 138 ";" 39 TermDestHost SQL_Latin1_General_CP1_CI_AS 40 SQLCHAR 0 138 ";" 40 TermCallID SQL_Latin1_General_CP1_CI_AS 41 SQLCHAR 0 26 ";" 41 TermRemPayIPAdd SQL_Latin1_General_CP1_CI_AS 42 SQLCHAR 0 16 ";" 42 TermRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS 43 SQLCHAR 0 26 ";" 43 TermLocPayIPAdd SQL_Latin1_General_CP1_CI_AS 44 SQLCHAR 0 16 ";" 44 TermLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS 45 SQLCHAR 0 138 ";" 45 TermCodecList SQL_Latin1_General_CP1_CI_AS 46 SQLCHAR 0 20 ";" 46 TermIngrPck SQL_Latin1_General_CP1_CI_AS 47 SQLCHAR 0 20 ";" 47 TermEgrPck SQL_Latin1_General_CP1_CI_AS 48 SQLCHAR 0 20 ";" 48 TermIngrOct SQL_Latin1_General_CP1_CI_AS 49 SQLCHAR 0 20 ";" 49 TermEgrOct SQL_Latin1_General_CP1_CI_AS 50 SQLCHAR 0 20 ";" 50 TermIngrPckLoss SQL_Latin1_General_CP1_CI_AS 51 SQLCHAR 0 20 ";" 51 TermIngrDelay SQL_Latin1_General_CP1_CI_AS 52 SQLCHAR 0 20 ";" 52 TermIngrPckJitter SQL_Latin1_General_CP1_CI_AS 53 SQLCHAR 0 44 ";" 53 FinRouteInd SQL_Latin1_General_CP1_CI_AS 54 SQLCHAR 0 74 ";" 54 RoutingDigits SQL_Latin1_General_CP1_CI_AS 55 SQLCHAR 0 16 ";" 55 CallDuration SQL_Latin1_General_CP1_CI_AS 56 SQLCHAR 0 16 ";" 56 PostDialDelay SQL_Latin1_General_CP1_CI_AS 57 SQLCHAR 0 16 ";" 57 RingTime SQL_Latin1_General_CP1_CI_AS 58 SQLCHAR 0 20 ";" 58 Duration SQL_Latin1_General_CP1_CI_AS 59 SQLCHAR 0 46 ";" 59 ConfID SQL_Latin1_General_CP1_CI_AS 60 SQLCHAR 0 74 ";" 60 RPIDANI SQL_Latin1_General_CP1_CI_AS 61 SQLCHAR 0 0 ";\n" 0 Status SQL_Latin1_General_CP1_CI_AS

This format file when executed with this sql statement below will only get half of the data on this file, some file do not get anything, that's weird ...

BULK INSERT VSXCDRI.dbo.SansayRawInfo FROM 'C:\20090114-0939-87728127-87728166.CDR' WITH (FORMATFILE = 'C:\SansayRawInfoFormat5.FMT')

Thanks, I will let you know if it works or not .... thanks for the idea

Answer 3

I have an error after executing bulk insert, this message shows,

Msg 4839, Level 16, State 1, Line 1 Cannot perform the bulk load. Invalid collation name for source column 1 in the format file "C:\SansayRawInfoFormat8.FMT". Msg 4822, Level 16, State 1, Line 1 Cannot bulk load. Invalid number of columns in the format file "C:\SansayRawInfoFormat8.FMT".

This is my data file (sample not actual),

1;a1;a2;a3;a4;a5;a6;a7;a8;a9;a10;a11;a12;a13;a14;a15;a16;a17;a18;a19;a20;a21;a22;a23;a24;a25;a26;a27;a28;a29;a30;a31;a32;a33;a34;a35;a36;a37;a38;a39;a40;a41;a42;a43;a44;a45;a46;a47;a48;a49;a50;a51;a52;a53;a54;a55;a56;a57;; 2;b1;b2;b3;b4;b5;b6;b7;b8;b9;b10;b11;b12;b13;b14;b15;b16;b17;b18;b19;b20;b21;b22;b23;b24;b25;b26;b27;b28;b29;b30;b31;b32;b33;b34;b35;b36;b37;b38;b39;b40;b41;b42;b43;b44;b45;b46;b47;b48;b49;b50;b51;b52;b53;b54;b55;b56;b57;; 3;c1;c2;c3;c4;c5;c6;c7;c8;c9;c10;c11;c12;c13;c14;c15;c16;c17;c18;c19;c20;c21;c22;c23;c24;c25;c26;c27;c28;c29;c30;c31;c32;c33;c34;c35;c36;c37;c38;c39;c40;c41;c42;c43;c44;c45;c46;c47;c48;c49;c50;c51;c52;c53;c54;c55;c56;c57;; 4;d1;d2;d3;d4;d5;d6;d7;d8;d9;d10;d11;d12;d13;d14;d15;d16;d17;d18;d19;d20;d21;d22;d23;d24;d25;d26;d27;d28;d29;d30;d31;d32;d33;d34;d35;d36;d37;d38;d39;d40;d41;d42;d43;d44;d45;d46;d47;d48;d49;d50;d51;d52;d53;d54;d55;d56;d57;; 5;e1;e2;e3;e4;e5;e6;e7;e8;e9;e10;e11;e12;e13;e14;e15;e16;e17;e18;e19;e20;e21;e22;e23;e24;e25;e26;e27;e28;e29;e30;e31;e32;e33;e34;e35;e36;e37;e38;e39;e40;e41;e42;e43;e44;e45;e46;e47;e48;e49;e50;e51;e52;e53;e54;e55;e56;e57;; 6;ef1;ef2;ef3;ef4;ef5;ef6;ef7;ef8;ef9;ef10;ef11;ef12;ef13;ef14;ef15;ef16;ef17;ef18;ef19;ef20;ef21;ef22;ef23;ef24;ef25;ef26;ef27;ef28;ef29;ef30;ef31;ef32;ef33;ef34;ef35;ef36;ef37;ef38;ef39;ef40;ef41;ef42;ef43;ef44;ef45;ef46;ef47;ef48;ef49;ef50;ef51;ef52;ef53;ef54;ef55;ef56;ef57;; 7;g1;g2;g3;g4;g5;g6;g7;g8;g9;g10;g11;g12;g13;g14;g15;g16;g17;g18;g19;g20;g21;g22;g23;g24;g25;g26;g27;g28;g29;g30;g31;g32;g33;g34;g35;g36;g37;g38;g39;g40;g41;g42;g43;g44;g45;g46;g47;g48;g49;g50;g51;g52;g53;g54;g55;g56;g57;; 8;h1;h2;h3;h4;h5;h6;h7;h8;h9;h10;h11;h12;h13;h14;h15;h16;h17;h18;h19;h20;h21;h22;h23;h24;h25;h26;h27;h28;h29;h30;h31;h32;h33;h34;h35;h36;h37;h38;h39;h40;h41;h42;h43;h44;h45;h46;h47;h48;h49;h50;h51;h52;h53;h54;h55;h56;h57;;

This is my 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 table structure: CREATE TABLE [dbo].SansayRawInfo NOT NULL, [Sequence] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Version] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RecordType] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ConnectionType] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SessionID] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ReleaseCause] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StartTime] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [AnswerTime] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ReleaseTOD] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WestofGMT] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RelCauseTxt] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RelCauseBin] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [1strelease] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgTrunkId] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgProtocol] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgSrcNo] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgSrcHost] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgDestNo] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgDestHost] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgCallID] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgRemPayIPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgRemPayUDPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgLocPayIPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgLocPayUDPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgCodecList] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgIngrPck] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgEgrPck] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgIngrOct] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgEgrOct] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgIngrPckLoss] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgIngrDelay] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrgIngrPckJitter] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermTrunkId] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermProtocol] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermSrcNo] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermSrcHost] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermDestNo] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermDestHost] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermCallID] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermRemPayIPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermRemPayUDPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermLocPayIPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermLocPayUDPAdd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermCodecList] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermIngrPck] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermEgrPck] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermIngrOct] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermEgrOct] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermIngrPckLoss] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermIngrDelay] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TermIngrPckJitter] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FinRouteInd] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RoutingDigits] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CallDuration] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PostDialDelay] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RingTime] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Duration] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ConfID] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RPIDANI] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Status] [bit] NULL DEFAULT ((0)) ) ON [PRIMARY]

To Rich Andrew, I was able to do the solution that you gave unfortunately it did not work on the actual data. IT just work on the sample data. something wrong with either the format file or the data file needs to be change. Thanks anyway ...

Answer 4

asiaenforcer, my apologies for being off line for week!

Using your data above I think I've got it...

Firstly I had a problem with your destination table - the statement "CREATE TABLE [dbo].SansayRawInfo NOT NULL," is not the correct syntax so from looking at your previous format file I believe you missed out a column (RawDataId) so the destination table I came up with is...

CREATE TABLE [dbo].[SansayRawInfo](
    [RawDataId] [int] IDENTITY(1,1) NOT NULL,
    [Sequence] [varchar](5) NULL,
    [Version] [varchar](5) NULL,
    [RecordType] [varchar](5) NULL,
    [ConnectionType] [varchar](5) NULL,
    [SessionID] [varchar](5) NULL,
    [ReleaseCause] [varchar](5) NULL,
    [StartTime] [varchar](5) NULL,
    [AnswerTime] [varchar](5) NULL,
    [ReleaseTOD] [varchar](5) NULL,
    [WestofGMT] [varchar](5) NULL,
    [RelCauseTxt] [varchar](5) NULL,
    [RelCauseBin] [varchar](5) NULL,
    [1strelease] [varchar](5) NULL,
    [OrgTrunkId] [varchar](5) NULL,
    [OrgProtocol] [varchar](5) NULL,
    [OrgSrcNo] [varchar](5) NULL,
    [OrgSrcHost] [varchar](5) NULL,
    [OrgDestNo] [varchar](5) NULL,
    [OrgDestHost] [varchar](5) NULL,
    [OrgCallID] [varchar](5) NULL,
    [OrgRemPayIPAdd] [varchar](5) NULL,
    [OrgRemPayUDPAdd] [varchar](5) NULL,
    [OrgLocPayIPAdd] [varchar](5) NULL,
    [OrgLocPayUDPAdd] [varchar](5) NULL,
    [OrgCodecList] [varchar](5) NULL,
    [OrgIngrPck] [varchar](5) NULL,
    [OrgEgrPck] [varchar](5) NULL,
    [OrgIngrOct] [varchar](5) NULL,
    [OrgEgrOct] [varchar](5) NULL,
    [OrgIngrPckLoss] [varchar](5) NULL,
    [OrgIngrDelay] [varchar](5) NULL,
    [OrgIngrPckJitter] [varchar](5) NULL,
    [TermTrunkId] [varchar](5) NULL,
    [TermProtocol] [varchar](5) NULL,
    [TermSrcNo] [varchar](5) NULL,
    [TermSrcHost] [varchar](5) NULL,
    [TermDestNo] [varchar](5) NULL,
    [TermDestHost] [varchar](5) NULL,
    [TermCallID] [varchar](5) NULL,
    [TermRemPayIPAdd] [varchar](5) NULL,
    [TermRemPayUDPAdd] [varchar](5) NULL,
    [TermLocPayIPAdd] [varchar](5) NULL,
    [TermLocPayUDPAdd] [varchar](5) NULL,
    [TermCodecList] [varchar](5) NULL,
    [TermIngrPck] [varchar](5) NULL,
    [TermEgrPck] [varchar](5) NULL,
    [TermIngrOct] [varchar](5) NULL,
    [TermEgrOct] [varchar](5) NULL,
    [TermIngrPckLoss] [varchar](5) NULL,
    [TermIngrDelay] [varchar](5) NULL,
    [TermIngrPckJitter] [varchar](5) NULL,
    [FinRouteInd] [varchar](5) NULL,
    [RoutingDigits] [varchar](5) NULL,
    [CallDuration] [varchar](5) NULL,
    [PostDialDelay] [varchar](5) NULL,
    [RingTime] [varchar](5) NULL,
    [Duration] [varchar](5) NULL,
    [ConfID] [varchar](5) NULL,
    [RPIDANI] [varchar](5) NULL,
    [Status] [bit] NULL CONSTRAINT [DF__SansayRaw__Statu__7C8480AE]  DEFAULT ((0))
) ON [PRIMARY]

PLEASE NOTE

• In the above create table script all varchars are length of 5 (based on your sample data NOT your format file) although this should not make a difference as all of your lengths are greater (and none were specified in your create table script).
• There are no collation statements in my script because SQL_Latin1_General_CP1_CI_AS is my database default and I just scripted the table in SSMS. Again this should not cause any problems.

So, my next step was to go through the format files based on the sample raw data you supplied and I came up with the following format file...

9.0 
61 
1 SQLINT 0 0 "" 0 RawDataId "" 
2 SQLCHAR 0 20 ";" 2 Sequence SQL_Latin1_General_CP1_CI_AS 
3 SQLCHAR 0 30 ";" 3 Version SQL_Latin1_General_CP1_CI_AS 
4 SQLCHAR 0 44 ";" 4 RecordType SQL_Latin1_General_CP1_CI_AS 
5 SQLCHAR 0 26 ";" 5 ConnectionType SQL_Latin1_General_CP1_CI_AS 
6 SQLCHAR 0 42 ";" 6 SessionID SQL_Latin1_General_CP1_CI_AS 
7 SQLCHAR 0 14 ";" 7 ReleaseCause SQL_Latin1_General_CP1_CI_AS 
8 SQLCHAR 0 42 ";" 8 StartTime SQL_Latin1_General_CP1_CI_AS 
9 SQLCHAR 0 42 ";" 9 AnswerTime SQL_Latin1_General_CP1_CI_AS 
10 SQLCHAR 0 42 ";" 10 ReleaseTOD SQL_Latin1_General_CP1_CI_AS 
11 SQLCHAR 0 42 ";" 11 WestofGMT SQL_Latin1_General_CP1_CI_AS 
12 SQLCHAR 0 42 ";" 12 RelCauseTxt SQL_Latin1_General_CP1_CI_AS 
13 SQLCHAR 0 28 ";" 13 RelCauseBin SQL_Latin1_General_CP1_CI_AS 
14 SQLCHAR 0 22 ";" 14 1strelease SQL_Latin1_General_CP1_CI_AS 
15 SQLCHAR 0 64 ";" 15 OrgTrunkId SQL_Latin1_General_CP1_CI_AS 
16 SQLCHAR 0 16 ";" 16 OrgProtocol SQL_Latin1_General_CP1_CI_AS 
17 SQLCHAR 0 138 ";" 17 OrgSrcNo SQL_Latin1_General_CP1_CI_AS 
18 SQLCHAR 0 138 ";" 18 OrgSrcHost SQL_Latin1_General_CP1_CI_AS 
19 SQLCHAR 0 138 ";" 19 OrgDestNo SQL_Latin1_General_CP1_CI_AS 
20 SQLCHAR 0 138 ";" 20 OrgDestHost SQL_Latin1_General_CP1_CI_AS 
21 SQLCHAR 0 138 ";" 21 OrgCallID SQL_Latin1_General_CP1_CI_AS 
22 SQLCHAR 0 26 ";" 22 OrgRemPayIPAdd SQL_Latin1_General_CP1_CI_AS 
23 SQLCHAR 0 16 ";" 23 OrgRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS 
24 SQLCHAR 0 26 ";" 24 OrgLocPayIPAdd SQL_Latin1_General_CP1_CI_AS 
25 SQLCHAR 0 16 ";" 25 OrgLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS 
26 SQLCHAR 0 138 ";" 26 OrgCodecList SQL_Latin1_General_CP1_CI_AS 
27 SQLCHAR 0 20 ";" 27 OrgIngrPck SQL_Latin1_General_CP1_CI_AS 
28 SQLCHAR 0 20 ";" 28 OrgEgrPck SQL_Latin1_General_CP1_CI_AS 
29 SQLCHAR 0 20 ";" 29 OrgIngrOct SQL_Latin1_General_CP1_CI_AS 
30 SQLCHAR 0 20 ";" 30 OrgEgrOct SQL_Latin1_General_CP1_CI_AS 
31 SQLCHAR 0 20 ";" 31 OrgIngrPckLoss SQL_Latin1_General_CP1_CI_AS 
32 SQLCHAR 0 20 ";" 32 OrgIngrDelay SQL_Latin1_General_CP1_CI_AS 
33 SQLCHAR 0 20 ";" 33 OrgIngrPckJitter SQL_Latin1_General_CP1_CI_AS 
34 SQLCHAR 0 64 ";" 34 TermTrunkId SQL_Latin1_General_CP1_CI_AS 
35 SQLCHAR 0 16 ";" 35 TermProtocol SQL_Latin1_General_CP1_CI_AS 
36 SQLCHAR 0 138 ";" 36 TermSrcNo SQL_Latin1_General_CP1_CI_AS 
37 SQLCHAR 0 138 ";" 37 TermSrcHost SQL_Latin1_General_CP1_CI_AS 
38 SQLCHAR 0 138 ";" 38 TermDestNo SQL_Latin1_General_CP1_CI_AS 
39 SQLCHAR 0 138 ";" 39 TermDestHost SQL_Latin1_General_CP1_CI_AS 
40 SQLCHAR 0 138 ";" 40 TermCallID SQL_Latin1_General_CP1_CI_AS 
41 SQLCHAR 0 26 ";" 41 TermRemPayIPAdd SQL_Latin1_General_CP1_CI_AS 
42 SQLCHAR 0 16 ";" 42 TermRemPayUDPAdd SQL_Latin1_General_CP1_CI_AS 
43 SQLCHAR 0 26 ";" 43 TermLocPayIPAdd SQL_Latin1_General_CP1_CI_AS 
44 SQLCHAR 0 16 ";" 44 TermLocPayUDPAdd SQL_Latin1_General_CP1_CI_AS 
45 SQLCHAR 0 138 ";" 45 TermCodecList SQL_Latin1_General_CP1_CI_AS 
46 SQLCHAR 0 20 ";" 46 TermIngrPck SQL_Latin1_General_CP1_CI_AS 
47 SQLCHAR 0 20 ";" 47 TermEgrPck SQL_Latin1_General_CP1_CI_AS 
48 SQLCHAR 0 20 ";" 48 TermIngrOct SQL_Latin1_General_CP1_CI_AS 
49 SQLCHAR 0 20 ";" 49 TermEgrOct SQL_Latin1_General_CP1_CI_AS 
50 SQLCHAR 0 20 ";" 50 TermIngrPckLoss SQL_Latin1_General_CP1_CI_AS 
51 SQLCHAR 0 20 ";" 51 TermIngrDelay SQL_Latin1_General_CP1_CI_AS 
52 SQLCHAR 0 20 ";" 52 TermIngrPckJitter SQL_Latin1_General_CP1_CI_AS 
53 SQLCHAR 0 44 ";" 53 FinRouteInd SQL_Latin1_General_CP1_CI_AS 
54 SQLCHAR 0 74 ";" 54 RoutingDigits SQL_Latin1_General_CP1_CI_AS 
55 SQLCHAR 0 16 ";" 55 CallDuration SQL_Latin1_General_CP1_CI_AS 
56 SQLCHAR 0 16 ";" 56 PostDialDelay SQL_Latin1_General_CP1_CI_AS 
57 SQLCHAR 0 16 ";" 57 RingTime SQL_Latin1_General_CP1_CI_AS 
58 SQLCHAR 0 20 ";" 58 Duration SQL_Latin1_General_CP1_CI_AS 
59 SQLCHAR 0 46 ";" 59 ConfID SQL_Latin1_General_CP1_CI_AS 
60 SQLCHAR 0 74 ";" 60 RPIDANI SQL_Latin1_General_CP1_CI_AS 
61 SQLCHAR 0 0 " " 0 Status ""

I think the key bit here is the single space as a line delimiter in the last line 61 SQLCHAR 0 0 " " 0 Status ""

I then ran the below statement...

BULK INSERT dbo.SansayRawInfo 
FROM 'C:\MYPATH\data.txt' 
WITH (FORMATFILE = 'C:\MYPATH\fmt.fmt')

And it imported the first 7 rows correctly. The last row did not import because in my data file there was not a trailing space line terminator as there were on all of the other rows. When I added in this final space to the data file I got all 8 rows. If your source data is system generated I would hope that it will print out the trailing space - if not you need to revisit your source data because SQL Server requires it to be consistent across the whole set and may miss out inconsistent rows.

In the end my result data looked like the below...

8   1 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18 a19 a20 a21 a22 a23 a24 a25 a26 a27 a28 a29 a30 a31 a32 a33 a34 a35 a36 a37 a38 a39 a40 a41 a42 a43 a44 a45 a46 a47 a48 a49 a50 a51 a52 a53 a54 a55 a56 a57 NULL 0
9   2 b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b13 b14 b15 b16 b17 b18 b19 b20 b21 b22 b23 b24 b25 b26 b27 b28 b29 b30 b31 b32 b33 b34 b35 b36 b37 b38 b39 b40 b41 b42 b43 b44 b45 b46 b47 b48 b49 b50 b51 b52 b53 b54 b55 b56 b57 NULL 0
10  3 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c35 c36 c37 c38 c39 c40 c41 c42 c43 c44 c45 c46 c47 c48 c49 c50 c51 c52 c53 c54 c55 c56 c57 NULL 0
11  4 d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13 d14 d15 d16 d17 d18 d19 d20 d21 d22 d23 d24 d25 d26 d27 d28 d29 d30 d31 d32 d33 d34 d35 d36 d37 d38 d39 d40 d41 d42 d43 d44 d45 d46 d47 d48 d49 d50 d51 d52 d53 d54 d55 d56 d57 NULL 0
12  5 e1 e2 e3 e4 e5 e6 e7 e8 e9 e10 e11 e12 e13 e14 e15 e16 e17 e18 e19 e20 e21 e22 e23 e24 e25 e26 e27 e28 e29 e30 e31 e32 e33 e34 e35 e36 e37 e38 e39 e40 e41 e42 e43 e44 e45 e46 e47 e48 e49 e50 e51 e52 e53 e54 e55 e56 e57 NULL 0
13  6 ef1 ef2 ef3 ef4 ef5 ef6 ef7 ef8 ef9 ef10 ef11 ef12 ef13 ef14 ef15 ef16 ef17 ef18 ef19 ef20 ef21 ef22 ef23 ef24 ef25 ef26 ef27 ef28 ef29 ef30 ef31 ef32 ef33 ef34 ef35 ef36 ef37 ef38 ef39 ef40 ef41 ef42 ef43 ef44 ef45 ef46 ef47 ef48 ef49 ef50 ef51 ef52 ef53 ef54 ef55 ef56 ef57 NULL 0
14  7 g1 g2 g3 g4 g5 g6 g7 g8 g9 g10 g11 g12 g13 g14 g15 g16 g17 g18 g19 g20 g21 g22 g23 g24 g25 g26 g27 g28 g29 g30 g31 g32 g33 g34 g35 g36 g37 g38 g39 g40 g41 g42 g43 g44 g45 g46 g47 g48 g49 g50 g51 g52 g53 g54 g55 g56 g57 NULL 0
15  8 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23 h24 h25 h26 h27 h28 h29 h30 h31 h32 h33 h34 h35 h36 h37 h38 h39 h40 h41 h42 h43 h44 h45 h46 h47 h48 h49 h50 h51 h52 h53 h54 h55 h56 h57 NULL 0

PLEASE NOTE

My RawDataId's start at 8 because the table was previously cleared and repopulated.

Hope this helps somewhat and that you do not have to revisit your source data because that can be right pain :)