Zakaria IMAM
Reputation: 11
Why does my BULK INSERT code skip (ignore) the 1st row?
BULK INSERT CDR FROM 'C:\sqldata\myDatafile.txt'
WITH (FORMATFILE='C:\sqldata\CDRimport.fmt', FIRSTROW=3)
I have a problem which cause a headache for me and appreciate your help. My problem is that I'm pulling data from flat files to SQL Server 2008 using BULK INSERT with FORMATFILE.
Everything is working fine and the data is being loaded successfully. However I noticed that the 1st row is always been skipped (ignored). I'm setting FIRSTROW=3 to start from the 3rd row because the 1st contains headers and the 2nd contains datatype and I don't want them to be imported.
To test my case you need the following:
First: create this table:
CREATE TABLE [dbo].[CDR](
[cdrRecordType] [varchar](50) NULL,
[globalCallID_callManagerId] [varchar](50) NULL,
[globalCallID_callId] [varchar](50) NULL,
[origLegCallIdentifier] [varchar](50) NULL,
[dateTimeOrigination] [varchar](50) NULL,
[origNodeId] [varchar](50) NULL,
[origSpan] [varchar](50) NULL,
[origIpAddr] [varchar](50) NULL,
[callingPartyNumber] [varchar](50) NULL,
[callingPartyUnicodeLoginUserID] [varchar](50) NULL,
[origCause_location] [varchar](50) NULL,
[origCause_value] [varchar](50) NULL,
[origPrecedenceLevel] [varchar](50) NULL,
[origMediaTransportAddress_IP] [varchar](50) NULL,
[origMediaTransportAddress_Port] [varchar](50) NULL,
[origMediaCap_payloadCapability] [varchar](50) NULL,
[origMediaCap_maxFramesPerPacket] [varchar](50) NULL,
[origMediaCap_g723BitRate] [varchar](50) NULL,
[origVideoCap_Codec] [varchar](50) NULL,
[origVideoCap_Bandwidth] [varchar](50) NULL,
[origVideoCap_Resolution] [varchar](50) NULL,
[origVideoTransportAddress_IP] [varchar](50) NULL,
[origVideoTransportAddress_Port] [varchar](50) NULL,
[origRSVPAudioStat] [varchar](50) NULL,
[origRSVPVideoStat] [varchar](50) NULL,
[destLegIdentifier] [varchar](50) NULL,
[destNodeId] [varchar](50) NULL,
[destSpan] [varchar](50) NULL,
[destIpAddr] [varchar](50) NULL,
[originalCalledPartyNumber] [varchar](50) NULL,
[finalCalledPartyNumber] [varchar](50) NULL,
[finalCalledPartyUnicodeLoginUserID] [varchar](50) NULL,
[destCause_location] [varchar](50) NULL,
[destCause_value] [varchar](50) NULL,
[destPrecedenceLevel] [varchar](50) NULL,
[destMediaTransportAddress_IP] [varchar](50) NULL,
[destMediaTransportAddress_Port] [varchar](50) NULL,
[destMediaCap_payloadCapability] [varchar](50) NULL,
[destMediaCap_maxFramesPerPacket] [varchar](50) NULL,
[destMediaCap_g723BitRate] [varchar](50) NULL,
[destVideoCap_Codec] [varchar](50) NULL,
[destVideoCap_Bandwidth] [varchar](50) NULL,
[destVideoCap_Resolution] [varchar](50) NULL,
[destVideoTransportAddress_IP] [varchar](50) NULL,
[destVideoTransportAddress_Port] [varchar](50) NULL,
[destRSVPAudioStat] [varchar](50) NULL,
[destRSVPVideoStat] [varchar](50) NULL,
[dateTimeConnect] [varchar](50) NULL,
[dateTimeDisconnect] [varchar](50) NULL,
[lastRedirectDn] [varchar](50) NULL,
[pkid] [varchar](50) NULL,
[originalCalledPartyNumberPartition] [varchar](50) NULL,
[callingPartyNumberPartition] [varchar](50) NULL,
[finalCalledPartyNumberPartition] [varchar](50) NULL,
[lastRedirectDnPartition] [varchar](50) NULL,
[duration] [varchar](50) NULL,
[origDeviceName] [varchar](50) NULL,
[destDeviceName] [varchar](50) NULL,
[origCallTerminationOnBehalfOf] [varchar](50) NULL,
[destCallTerminationOnBehalfOf] [varchar](50) NULL,
[origCalledPartyRedirectOnBehalfOf] [varchar](50) NULL,
[lastRedirectRedirectOnBehalfOf] [varchar](50) NULL,
[origCalledPartyRedirectReason] [varchar](50) NULL,
[lastRedirectRedirectReason] [varchar](50) NULL,
[destConversationId] [varchar](50) NULL,
[globalCallId_ClusterID] [varchar](50) NULL,
[joinOnBehalfOf] [varchar](50) NULL,
[comment] [varchar](50) NULL,
[authCodeDescription] [varchar](50) NULL,
[authorizationLevel] [varchar](50) NULL,
[clientMatterCode] [varchar](50) NULL,
[origDTMFMethod] [varchar](50) NULL,
[destDTMFMethod] [varchar](50) NULL,
[callSecuredStatus] [varchar](50) NULL,
[origConversationId] [varchar](50) NULL,
[origMediaCap_Bandwidth] [varchar](50) NULL,
[destMediaCap_Bandwidth] [varchar](50) NULL,
[authorizationCodeValue] [varchar](50) NULL,
[outpulsedCallingPartyNumber] [varchar](50) NULL,
[outpulsedCalledPartyNumber] [varchar](50) NULL,
[origIpv4v6Addr] [varchar](50) NULL,
[destIpv4v6Addr] [varchar](50) NULL,
[origVideoCap_Codec_Channel2] [varchar](50) NULL,
[origVideoCap_Bandwidth_Channel2] [varchar](50) NULL,
[origVideoCap_Resolution_Channel2] [varchar](50) NULL,
[origVideoTransportAddress_IP_Channel2] [varchar](50) NULL,
[origVideoTransportAddress_Port_Channel2] [varchar](50) NULL,
[origVideoChannel_Role_Channel2] [varchar](50) NULL,
[destVideoCap_Codec_Channel2] [varchar](50) NULL,
[destVideoCap_Bandwidth_Channel2] [varchar](50) NULL,
[destVideoCap_Resolution_Channel2] [varchar](50) NULL,
[destVideoTransportAddress_IP_Channel2] [varchar](50) NULL,
[destVideoTransportAddress_Port_Channel2] [varchar](50) NULL,
[destVideoChannel_Role_Channel2] [varchar](50) NULL
) ON [PRIMARY]
GO
Second:
create new text file C:\sqldata\myDatafile.txt then copy the below data and paste it in that file:
"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","origLegCallIdentifier","dateTimeOrigination","origNodeId","origSpan","origIpAddr","callingPartyNumber","callingPartyUnicodeLoginUserID","origCause_location","origCause_value","origPrecedenceLevel","origMediaTransportAddress_IP","origMediaTransportAddress_Port","origMediaCap_payloadCapability","origMediaCap_maxFramesPerPacket","origMediaCap_g723BitRate","origVideoCap_Codec","origVideoCap_Bandwidth","origVideoCap_Resolution","origVideoTransportAddress_IP","origVideoTransportAddress_Port","origRSVPAudioStat","origRSVPVideoStat","destLegIdentifier","destNodeId","destSpan","destIpAddr","originalCalledPartyNumber","finalCalledPartyNumber","finalCalledPartyUnicodeLoginUserID","destCause_location","destCause_value","destPrecedenceLevel","destMediaTransportAddress_IP","destMediaTransportAddress_Port","destMediaCap_payloadCapability","destMediaCap_maxFramesPerPacket","destMediaCap_g723BitRate","destVideoCap_Codec","destVideoCap_Bandwidth","destVideoCap_Resolution","destVideoTransportAddress_IP","destVideoTransportAddress_Port","destRSVPAudioStat","destRSVPVideoStat","dateTimeConnect","dateTimeDisconnect","lastRedirectDn","pkid","originalCalledPartyNumberPartition","callingPartyNumberPartition","finalCalledPartyNumberPartition","lastRedirectDnPartition","duration","origDeviceName","destDeviceName","origCallTerminationOnBehalfOf","destCallTerminationOnBehalfOf","origCalledPartyRedirectOnBehalfOf","lastRedirectRedirectOnBehalfOf","origCalledPartyRedirectReason","lastRedirectRedirectReason","destConversationId","globalCallId_ClusterID","joinOnBehalfOf","comment","authCodeDescription","authorizationLevel","clientMatterCode","origDTMFMethod","destDTMFMethod","callSecuredStatus","origConversationId","origMediaCap_Bandwidth","destMediaCap_Bandwidth","authorizationCodeValue","outpulsedCallingPartyNumber","outpulsedCalledPartyNumber","origIpv4v6Addr","destIpv4v6Addr","origVideoCap_Codec_Channel2","origVideoCap_Bandwidth_Channel2","origVideoCap_Resolution_Channel2","origVideoTransportAddress_IP_Channel2","origVideoTransportAddress_Port_Channel2","origVideoChannel_Role_Channel2","destVideoCap_Codec_Channel2","destVideoCap_Bandwidth_Channel2","destVideoCap_Resolution_Channel2","destVideoTransportAddress_IP_Channel2","destVideoTransportAddress_Port_Channel2","destVideoChannel_Role_Channel2"
INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),VARCHAR(128),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),VARCHAR(50),VARCHAR(128),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,VARCHAR(50),UNIQUEIDENTIFIER,VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),INTEGER,VARCHAR(129),VARCHAR(129),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),INTEGER,VARCHAR(2048),VARCHAR(50),INTEGER,VARCHAR(32),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(32),VARCHAR(50),VARCHAR(50),VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER
1,2,2759873,35208306,1330847184,2,0,-637263956,"5838","",0,0,4,-637263956,24692,4,20,0,0,0,0,0,0,"0","0",35208307,6,35208307,-2096103670,"90556138315","90556138315","",0,16,4,-2096103670,23090,4,20,0,0,0,0,0,0,"0","0",1330847195,1330847219,"90556138315","37d523ab-680b-41a9-ab6b-d9867974f044","Part_National","Part_DU_CCs_NAT","Part_National","Part_National",24,"SEP180373C4463E","10.255.15.131",0,12,0,0,0,0,0,"StandAloneCluster",0,"","",0,"",3,1,0,0,64,64,"","043699155","90556138315","172.31.4.218","10.255.15.131",0,0,0,0,0,0,0,0,0,0,0,0
1,2,2759107,35206523,1330846644,2,35206523,-737214710,"042861396","",0,0,4,-2129658102,19000,4,20,0,0,0,0,0,0,"0","0",35206524,7,0,1562582956,"6894","6894","",0,16,4,1562582956,18658,4,20,0,0,0,0,0,0,"0","0",1330846645,1330847219,"6894","a1df3ed4-0b42-4974-8c60-50cb9d696d5c","Part_India","","Part_India","Part_India",574,"10.255.14.212","SEP00269944A143",0,12,0,0,0,0,0,"StandAloneCluster",0,"","",0,"",1,3,0,0,64,64,"","","","10.255.15.129","172.31.35.93",0,0,0,0,0,0,0,0,0,0,0,0
Third:
This is the file format: C:\sqldata\CDRimport.fmt
10.0
94
1 SQLCHAR 0 50 "," 1 cdrRecordType Arabic_CI_AS
2 SQLCHAR 0 50 "," 2 globalCallID_callManagerId Arabic_CI_AS
3 SQLCHAR 0 50 "," 3 globalCallID_callId Arabic_CI_AS
4 SQLCHAR 0 50 "," 4 origLegCallIdentifier Arabic_CI_AS
5 SQLCHAR 0 50 "," 5 dateTimeOrigination Arabic_CI_AS
6 SQLCHAR 0 50 "," 6 origNodeId Arabic_CI_AS
7 SQLCHAR 0 50 "," 7 origSpan Arabic_CI_AS
8 SQLCHAR 0 50 ",\"" 8 origIpAddr Arabic_CI_AS
9 SQLCHAR 0 50 "\",\"" 9 callingPartyNumber Arabic_CI_AS
10 SQLCHAR 0 50 "\"," 10 callingPartyUnicodeLoginUserID Arabic_CI_AS
11 SQLCHAR 0 50 "," 11 origCause_location Arabic_CI_AS
12 SQLCHAR 0 50 "," 12 origCause_value Arabic_CI_AS
13 SQLCHAR 0 50 "," 13 origPrecedenceLevel Arabic_CI_AS
14 SQLCHAR 0 50 "," 14 origMediaTransportAddress_IP Arabic_CI_AS
15 SQLCHAR 0 50 "," 15 origMediaTransportAddress_Port Arabic_CI_AS
16 SQLCHAR 0 50 "," 16 origMediaCap_payloadCapability Arabic_CI_AS
17 SQLCHAR 0 50 "," 17 origMediaCap_maxFramesPerPacket Arabic_CI_AS
18 SQLCHAR 0 50 "," 18 origMediaCap_g723BitRate Arabic_CI_AS
19 SQLCHAR 0 50 "," 19 origVideoCap_Codec Arabic_CI_AS
20 SQLCHAR 0 50 "," 20 origVideoCap_Bandwidth Arabic_CI_AS
21 SQLCHAR 0 50 "," 21 origVideoCap_Resolution Arabic_CI_AS
22 SQLCHAR 0 50 "," 22 origVideoTransportAddress_IP Arabic_CI_AS
23 SQLCHAR 0 50 ",\"" 23 origVideoTransportAddress_Port Arabic_CI_AS
24 SQLCHAR 0 50 "\",\"" 24 origRSVPAudioStat Arabic_CI_AS
25 SQLCHAR 0 50 "\"," 25 origRSVPVideoStat Arabic_CI_AS
26 SQLCHAR 0 50 "," 26 destLegIdentifier Arabic_CI_AS
27 SQLCHAR 0 50 "," 27 destNodeId Arabic_CI_AS
28 SQLCHAR 0 50 "," 28 destSpan Arabic_CI_AS
29 SQLCHAR 0 50 ",\"" 29 destIpAddr Arabic_CI_AS
30 SQLCHAR 0 50 "\",\"" 30 originalCalledPartyNumber Arabic_CI_AS
31 SQLCHAR 0 50 "\",\"" 31 finalCalledPartyNumber Arabic_CI_AS
32 SQLCHAR 0 50 "\"," 32 finalCalledPartyUnicodeLoginUserID Arabic_CI_AS
33 SQLCHAR 0 50 "," 33 destCause_location Arabic_CI_AS
34 SQLCHAR 0 50 "," 34 destCause_value Arabic_CI_AS
35 SQLCHAR 0 50 "," 35 destPrecedenceLevel Arabic_CI_AS
36 SQLCHAR 0 50 "," 36 destMediaTransportAddress_IP Arabic_CI_AS
37 SQLCHAR 0 50 "," 37 destMediaTransportAddress_Port Arabic_CI_AS
38 SQLCHAR 0 50 "," 38 destMediaCap_payloadCapability Arabic_CI_AS
39 SQLCHAR 0 50 "," 39 destMediaCap_maxFramesPerPacket Arabic_CI_AS
40 SQLCHAR 0 50 "," 40 destMediaCap_g723BitRate Arabic_CI_AS
41 SQLCHAR 0 50 "," 41 destVideoCap_Codec Arabic_CI_AS
42 SQLCHAR 0 50 "," 42 destVideoCap_Bandwidth Arabic_CI_AS
43 SQLCHAR 0 50 "," 43 destVideoCap_Resolution Arabic_CI_AS
44 SQLCHAR 0 50 "," 44 destVideoTransportAddress_IP Arabic_CI_AS
45 SQLCHAR 0 50 ",\"" 45 destVideoTransportAddress_Port Arabic_CI_AS
46 SQLCHAR 0 50 "\",\"" 46 destRSVPAudioStat Arabic_CI_AS
47 SQLCHAR 0 50 "\"," 47 destRSVPVideoStat Arabic_CI_AS
48 SQLCHAR 0 50 "," 48 dateTimeConnect Arabic_CI_AS
49 SQLCHAR 0 50 ",\"" 49 dateTimeDisconnect Arabic_CI_AS
50 SQLCHAR 0 50 "\",\"" 50 lastRedirectDn Arabic_CI_AS
51 SQLCHAR 0 50 "\",\"" 51 pkid Arabic_CI_AS
52 SQLCHAR 0 50 "\",\"" 52 originalCalledPartyNumberPartition Arabic_CI_AS
53 SQLCHAR 0 50 "\",\"" 53 callingPartyNumberPartition Arabic_CI_AS
54 SQLCHAR 0 50 "\",\"" 54 finalCalledPartyNumberPartition Arabic_CI_AS
55 SQLCHAR 0 50 "\"," 55 lastRedirectDnPartition Arabic_CI_AS
56 SQLCHAR 0 50 ",\"" 56 duration Arabic_CI_AS
57 SQLCHAR 0 50 "\",\"" 57 origDeviceName Arabic_CI_AS
58 SQLCHAR 0 50 "\"," 58 destDeviceName Arabic_CI_AS
59 SQLCHAR 0 50 "," 59 origCallTerminationOnBehalfOf Arabic_CI_AS
60 SQLCHAR 0 50 "," 60 destCallTerminationOnBehalfOf Arabic_CI_AS
61 SQLCHAR 0 50 "," 61 origCalledPartyRedirectOnBehalfOf Arabic_CI_AS
62 SQLCHAR 0 50 "," 62 lastRedirectRedirectOnBehalfOf Arabic_CI_AS
63 SQLCHAR 0 50 "," 63 origCalledPartyRedirectReason Arabic_CI_AS
64 SQLCHAR 0 50 "," 64 lastRedirectRedirectReason Arabic_CI_AS
65 SQLCHAR 0 50 ",\"" 65 destConversationId Arabic_CI_AS
66 SQLCHAR 0 50 "\"," 66 globalCallId_ClusterID Arabic_CI_AS
67 SQLCHAR 0 50 ",\"" 67 joinOnBehalfOf Arabic_CI_AS
68 SQLCHAR 0 50 "\",\"" 68 comment Arabic_CI_AS
69 SQLCHAR 0 50 "\"," 69 authCodeDescription Arabic_CI_AS
70 SQLCHAR 0 50 ",\"" 70 authorizationLevel Arabic_CI_AS
71 SQLCHAR 0 50 "\"," 71 clientMatterCode Arabic_CI_AS
72 SQLCHAR 0 50 "," 72 origDTMFMethod Arabic_CI_AS
73 SQLCHAR 0 50 "," 73 destDTMFMethod Arabic_CI_AS
74 SQLCHAR 0 50 "," 74 callSecuredStatus Arabic_CI_AS
75 SQLCHAR 0 50 "," 75 origConversationId Arabic_CI_AS
76 SQLCHAR 0 50 "," 76 origMediaCap_Bandwidth Arabic_CI_AS
77 SQLCHAR 0 50 ",\"" 77 destMediaCap_Bandwidth Arabic_CI_AS
78 SQLCHAR 0 50 "\",\"" 78 authorizationCodeValue Arabic_CI_AS
79 SQLCHAR 0 50 "\",\"" 79 outpulsedCallingPartyNumber Arabic_CI_AS
80 SQLCHAR 0 50 "\",\"" 80 outpulsedCalledPartyNumber Arabic_CI_AS
81 SQLCHAR 0 50 "\",\"" 81 origIpv4v6Addr Arabic_CI_AS
82 SQLCHAR 0 50 "\"," 82 destIpv4v6Addr Arabic_CI_AS
83 SQLCHAR 0 50 "," 83 origVideoCap_Codec_Channel2 Arabic_CI_AS
84 SQLCHAR 0 50 "," 84 origVideoCap_Bandwidth_Channel2 Arabic_CI_AS
85 SQLCHAR 0 50 "," 85 origVideoCap_Resolution_Channel2 Arabic_CI_AS
86 SQLCHAR 0 50 "," 86 origVideoTransportAddress_IP_Channel2 Arabic_CI_AS
87 SQLCHAR 0 50 "," 87 origVideoTransportAddress_Port_Channel2 Arabic_CI_AS
88 SQLCHAR 0 50 "," 88 origVideoChannel_Role_Channel2 Arabic_CI_AS
89 SQLCHAR 0 50 "," 89 destVideoCap_Codec_Channel2 Arabic_CI_AS
90 SQLCHAR 0 50 "," 90 destVideoCap_Bandwidth_Channel2 Arabic_CI_AS
91 SQLCHAR 0 50 "," 91 destVideoCap_Resolution_Channel2 Arabic_CI_AS
92 SQLCHAR 0 50 "," 92 destVideoTransportAddress_IP_Channel2 Arabic_CI_AS
93 SQLCHAR 0 50 "," 93 destVideoTransportAddress_Port_Channel2 Arabic_CI_AS
94 SQLCHAR 0 50 "\n" 94 destVideoChannel_Role_Channel2 Arabic_CI_AS
Finally, run this SQL command:
BULK INSERT CDR FROM 'C:\sqldata\myDatafile.txt'
WITH (FORMATFILE='C:\sqldata\CDRimport.fmt', FIRSTROW=3)
It should insert 2 rows, however it only inserts 1 row. Does anyone have any idea why this is the case?
Upvotes: 1
Views: 7405
Answers (1)
Alex Weitzer
Reputation: 181
From books online: "The FIRSTROW attribute is not intended to skip column headers." http://msdn.microsoft.com/en-US/library/ms188365(v=sql.90).aspx
Basically, MS is moving away from Bulk Insert/bcp, in favor of SSIS.
The reason your header rows are throwing off the bulk insert is that your format file specifies that a row should always have exactly 57 commas, 36 quote and comma combinations, and a newline (order matters too.) Since your second header row doesn't have the quote-comma delimeters, it's going to the next line to find the rest of the columns. By the way, bulk insert also doesn't support extra delimiters in your values, even if they're escaped or contained within quotes. Based on your format file, I'm guessing you anticipate they might.
Upvotes: 1
Related Questions
- Bulk insert SQL command cannot insert first row
- SQL Bulk Insert with FIRSTROW parameter skips the following line
- BULK INSERT fails with last row
- SQL Bulk insert ignores first data row
- SQL BULK INSERT tries to insert all rows to insert into last column of first row
- SQL Server BULK INSERT: why "The FIRSTROW attribute is not intended to skip column headers"?
- BULK INSERT missing last row?
- T SQL Bulk Insert skipping first row with or without header
- SQL INSERT weirdly skipping first record
- BULK INSERT from CSV does not read last row