As Requested...
Emily, August 04, 2017 - 8:58 pm UTC
Here are my CSV data and ctl file for sqlldr:
LOAD DATA
infile 'traffData.csv'
badfile 'nyBad.txt'
discardfile 'nyDiscard.txt'
INSERT
INTO TABLE TRAFFICDATA
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
id integer,
speed float,
traveltime integer,
status integer,
dateValidFrom timestamp "YYYY-MM-DD HH24:MI:SS.FF1",
linkId integer,
owner char(25),
transcomID integer,
borough char(25),
linkName char(25)
)
2,21.75,67,0,2015-12-3 00:02:07.0,4616325,NYC_DOT_LIC,4616325,Manhattan,11th ave s ganservoort - west st @ spring st
3,19.26,327,0,2015-12-3 00:02:07.0,4616324,NYC_DOT_LIC,4616324,Manhattan,12th ave @ 45th - 11 ave ganservoort st
4,13.05,250,0,2015-12-3 00:02:07.0,4616338,NYC_DOT_LIC,4616338,Manhattan,12th Ave N 40th - 57th St
106,19.26,119,0,2015-12-3 00:02:07.0,4616323,NYC_DOT_LIC,4616323,Manhattan,12th Ave S 57th St - 45th St
107,49.71,217,0,2015-12-3 12:26:11.0,4616279,NYC_DOT_LIC,4616279,Staten Island,278 E BRUNSWICK AVENUE - SIE E SOUTH AVENUE
119,34.18,191,0,2015-12-3 00:01:28.0,4456502,MTA Bridges & Tunnels,4456502,Manhattan,BBT E Manhattan Portal - Toll Plaza
122,18.02,294,0,2015-12-3 00:02:07.0,4616344,NYC_DOT_LIC,4616344,Manhattan,BBT Manhattan Portal inbound - West St N Watts St
123,26.72,54,0,2015-12-2 12:47:07.0,4616345,NYC_DOT_LIC,4616345,Manhattan,BBT Manhattan Portal inbound - West St S Battery Place
124,37.28,175,0,2015-12-3 00:01:28.0,4456501,MTA Bridges & Tunnels,4456501,Manhattan,BBT W Toll Plaza - Manhattan Portal
129,52.20,84,0,2015-12-3 00:02:06.0,4616246,NYC_DOT_LIC,4616246,Bronx,BE N STRATFORD AVENUE - CASTLE HILL AVE
137,57.17,75,0,2015-12-3 00:02:07.0,4616260,NYC_DOT_LIC,4616260,Bronx,BE S CASTLE HILL AVENUE - STRATFORD AVENUE
140,32.93,92,0,2015-12-3 00:01:27.0,4456479,MTA Bridges & Tunnels,4456479,Queens,BE S TBB EXIT RAMP - MANHATTAN LIFT SPAN
141,44.12,163,0,2015-12-3 00:01:27.0,4456478,MTA Bridges & Tunnels,4456478,Queens,BE S TBB EXIT RAMP - QUEENS ANCHORAGE
145,31.69,80,0,2015-12-3 00:02:07.0,4616342,NYC_DOT_LIC,4616342,Manhattan,BKN Bridge Manhattan Side - FDR N Catherine Slip
149,14.29,484,0,2015-12-3 00:02:07.0,4616339,NYC_DOT_LIC,4616339,Manhattan,BQE N Atlantic Ave - BKN Bridge Manhattan Side
150,13.05,721,0,2015-12-3 00:02:07.0,4616340,NYC_DOT_LIC,4616340,Manhattan,BQE N Atlantic Ave - MAN Bridge Manhattan Side
153,47.22,153,0,2015-12-3 00:02:06.0,4616235,NYC_DOT_LIC,4616235,Brooklyn,BQE N LEONARD STREET - 46TH STREET
154,47.22,87,0,2015-12-3 00:02:06.0,4616229,NYC_DOT_LIC,4616229,Brooklyn,BQE S - GOW S ALTANTIC AVENUE - 9TH STREET
155,42.87,164,0,2015-12-3 00:02:06.0,4616232,NYC_DOT_LIC,4616232,Brooklyn,BQE S 46TH STREET - LEONARD STREET
157,47.22,325,0,2015-12-3 00:02:07.0,4616271,NYC_DOT_LIC,4616271,Brooklyn,BQE S LEONARD STREET - ATLANTIC AVENUE
159,52.82,105,0,2015-12-3 00:02:07.0,4616252,NYC_DOT_LIC,4616252,Bronx,BRP N WATSON AVENUE - FORDHAM ROAD
160,57.17,97,0,2015-12-3 00:02:07.0,4616266,NYC_DOT_LIC,4616266,Bronx,BRP S FORDHAM ROAD - WATSON AVENUE
164,37.28,124,0,2015-12-3 00:01:28.0,4456497,NYC_DOT_LIC,4456497,Queens,BWB N Queens Anchorage - Toll Plaza
165,50.95,49,0,2015-12-3 00:02:07.0,4616305,NYC_DOT_LIC,4616305,Bronx,BWB N Toll Plaza - HRP N Lafayatte Ave
167,52.82,119,0,2015-12-3 00:02:07.0,4616312,NYC_DOT_LIC,4616312,Queens,BWB S Queens Anchorage - WSE S Exit 14 (Linden Pl)
168,47.22,98,0,2015-12-3 00:01:28.0,4456496,NYC_DOT_LIC,4456496,Bronx,BWB S Toll Plaza - Queens Anchorage
169,57.17,161,0,2015-12-3 00:02:07.0,4616355,NYC_DOT_LIC,4616355,Queens,Belt Pkwy E 182nd St - Laurelton Pkwy N @ SSP
170,55.30,68,0,2015-12-3 00:02:07.0,4616356,NYC_DOT_LIC,4616356,Queens,Belt Pkwy W 182nd St - JFK Expressway
171,8.08,1459,0,2015-12-3 00:02:07.0,4616357,NYC_DOT_LIC,4616357,Queens,Belt Pkwy W JFK Expressway - VWE N Jamaica Ave
172,55.30,78,0,2015-12-3 00:02:07.0,4616310,NYC_DOT_LIC,4616310,Bronx,CBE AMSTERDAM AVE (L/LVL) - MORRIS AVE
177,49.71,86,0,2015-12-3 00:02:07.0,4616309,NYC_DOT_LIC,4616309,Bronx,CBE E AMSTERDAM AVE(U/LVL) - MORRIS AVE
178,54.06,154,0,2015-12-3 00:02:07.0,4616297,NYC_DOT_LIC,4616297,Bronx,CBE E CASTLE HILL AVE - BE N WATERBURY AVE
184,59.03,43,0,2015-12-3 00:02:07.0,4616253,NYC_DOT_LIC,4616253,Bronx,CBE E TAYLOR AVENUE - CASTLE HILL AVENUE
185,59.03,43,0,2015-12-3 00:02:07.0,4616267,NYC_DOT_LIC,4616267,Bronx,CBE W CASTLE HILL AVENUE - TAYLOR AVENUE
186,37.28,222,0,2015-12-3 00:01:42.0,4620315,PA-GWBridge,4620315,Bronx,CBE W L/LE V AMSTERDAM AVE - I 95 S LOC LNS
190,3.73,932,0,2015-12-3 00:00:42.0,4620332,PA-GWBridge,4620332,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (L/LVL)
191,1.86,1550,0,2015-12-3 00:00:42.0,4620331,PA-GWBridge,4620331,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (U/LVL)
195,31.69,257,0,2015-12-3 00:01:42.0,4620310,PA-GWBridge,4620310,Bronx,CBE W U/LEV AMSTERDAM AVE - I95 S EXP LNS
199,59.03,185,0,2015-12-3 00:02:07.0,4616351,NYC_DOT_LIC,4616351,Queens,CIP N Hempstead Tpk - LIE
204,6.84,1500,0,2015-12-3 00:02:07.0,4616320,NYC_DOT_LIC,4616320,Queens,CIP N TNB - Whitestone Expwy S Exit 14 (Linden Pl)
205,57.79,277,0,2015-12-3 00:01:56.0,4362247,NYC-DOT-Region 10,4362247,Queens,CIP NB GCP - TNB
207,59.03,129,0,2015-12-3 00:02:07.0,4616352,NYC_DOT_LIC,4616352,Queens,CIP S Hempstead Tpk - Laurelton Pkwy @ SSP
208,59.03,185,0,2015-12-3 00:02:07.0,4616350,NYC_DOT_LIC,4616350,Queens,CIP S LIE - Hempstead Tpk
211,57.79,284,0,2015-12-3 00:01:56.0,4362250,NYC-DOT-Region 10,4362250,Queens,CVE NB GCP - WILLETS PT BLVD
212,52.20,210,0,2015-12-3 00:01:56.0,4362244,NYC-DOT-Region 10,4362244,Queens,CVE NB LIE - WILLETS PT BLVD
213,39.15,60,0,2015-12-3 00:01:27.0,4456450,MTA Bridges & Tunnels,4456450,Manhattan,FDR N - TBB E 116TH STREET - MANHATTAN TRUSS
215,47.22,146,0,2015-12-3 00:02:07.0,4616329,NYC_DOT_LIC,4616329,Manhattan,FDR N 25th - 63rd St
217,49.71,202,0,2015-12-3 00:02:07.0,4616328,NYC_DOT_LIC,4616328,Manhattan,FDR N Catherine Slip - 25th St
221,52.20,196,0,2015-12-3 00:02:07.0,4616333,NYC_DOT_LIC,4616333,Manhattan,FDR S 25th St - Catherine Slip
222,49.09,139,0,2015-12-3 00:02:07.0,4616332,NYC_DOT_LIC,4616332,Manhattan,FDR S 63rd - 25th St
223,31.07,84,0,2015-12-3 00:02:07.0,4616341,NYC_DOT_LIC,4616341,Manhattan,FDR S Catherine Slip - BKN Bridge Manhattan Side
224,8.70,184,0,2015-12-3 00:02:07.0,4616334,NYC_DOT_LIC,4616334,Manhattan,FDR S Catherine Slip - Whitehall St
225,31.69,111,0,2015-12-3 10:56:05.0,4456495,NYC_DOT_LIC,4456495,Manhattan,FDR S Whitehall St - BBT Manhattan Portal outbound
257,50.95,249,0,2015-12-3 00:02:07.0,4616318,NYC_DOT_LIC,4616318,Brooklyn,GOW N 7TH AVENUE - 9TH STREET
258,55.30,78,0,2015-12-3 00:02:06.0,4616220,NYC_DOT_LIC,4616220,Brooklyn,GOW N 92ND STREET - 7TH AVENUE
259,42.87,94,0,2015-12-3 00:02:06.0,4616223,NYC_DOT_LIC,4616223,Brooklyn,GOW N 9TH STREET - ATLANTIC AVENUE
261,8.08,481,0,2015-12-3 00:02:06.0,4616226,NYC_DOT_LIC,4616226,Brooklyn,GOW S 7TH AVENUE - 92ND STREET
262,49.09,259,0,2015-12-3 00:02:07.0,4616319,NYC_DOT_LIC,4616319,Brooklyn,GOW S 9TH STREET - 7TH AVENUE
263,1.86,800,0,2015-12-3 00:01:28.0,4763657,Verrazano-Narrows-Bridge,4763657,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY LOWER LEVEL
264,1.86,827,0,2015-12-2 23:44:27.0,4763655,Verrazano-Narrows-Bridge,4763655,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY UPPER LEVEL
265,57.79,127,0,2015-12-3 00:01:42.0,4620298,PA-GWBridge,4620298,Manhattan,GWB E LOWER LEVEL PLAZA - CBE E LOWER LEVEL AMSTERDAM AVE
295,62.14,66,0,2015-12-3 00:02:07.0,4616250,NYC_DOT_LIC,4616250,Bronx,HRP N LAFAYETTE AVENUE - E TREMONT AVENUE
298,45.98,55,0,2015-12-3 00:01:28.0,4456498,NYC_DOT_LIC,4456498,Bronx,HRP S Lafayette Ave - BWB S Toll Plaza
311,55.30,90,0,2015-12-3 00:01:57.0,4362342,NYC_DOT_LIC,4362342,Queens,LIE E 84TH ST - 108TH ST
313,52.82,286,0,2015-12-3 00:02:07.0,4616365,NYC_DOT_LIC,4616365,Queens,LIE E QMT TOLL PLAZA - 84TH ST
315,3.73,1173,0,2015-12-3 00:02:07.0,4616364,NYC_DOT_LIC,4616364,Queens,LIE W 108TH ST - 84TH ST
316,21.13,720,0,2015-12-3 00:01:28.0,4456516,NYC_DOT_LIC,4456516,Queens,LIE W 84TH ST - QMT TOLL PLAZA
318,52.20,75,0,2015-12-3 00:01:56.0,4362249,NYC-DOT-Region 10,4362249,Queens,LIE WB LITTLE NECK PKWY - NB CIP
319,55.92,171,0,2015-12-3 00:01:56.0,4362251,NYC-DOT-Region 10,4362251,Queens,LIE WB LITTLE NECK PKWY - NB CVE
324,36.04,155,0,2015-12-2 23:13:43.0,4329473,PA -Lincoln Tunnel,4329473,Manhattan,LINCOLN TUNNEL E CENTER TUBE NJ - NY
325,34.80,158,0,2015-12-3 00:01:42.0,4329472,PA -Lincoln Tunnel,4329472,Manhattan,LINCOLN TUNNEL E SOUTH TUBE - NJ - NY
329,36.04,153,0,2015-12-2 23:19:42.0,4329508,PA -Lincoln Tunnel,4329508,Manhattan,LINCOLN TUNNEL W CENTER TUBE NY - NJ
330,21.13,234,0,2015-12-3 00:01:42.0,4329507,PA -Lincoln Tunnel,4329507,Manhattan,LINCOLN TUNNEL W NORTH TUBE NY - NJ
331,55.92,140,0,2015-12-3 00:02:07.0,4616353,NYC_DOT_LIC,4616353,Queens,Laurelton Pkwy N @ SSP - CIP N Hempstead Tpk
332,57.17,161,0,2015-12-3 00:02:07.0,4616354,NYC_DOT_LIC,4616354,Queens,Laurelton Pkwy S @ SSP - Belt Pkwy W 182nd St
339,37.90,234,0,2015-12-3 15:23:00.0,4575278,NYC Thruway Tarrytown,4575278,Bronx,MDE N VAN CORTLAND PARK - NYST N EXIT 1 (MP.48)
344,34.18,130,0,2015-12-3 00:00:42.0,4620330,NYC_DOT_LIC,4620330,Bronx,MDE S HARLEM RIVER PARK - GWB W AMSTERDAM AVENUE LOWER LEVEL
345,9.94,422,0,2015-12-3 00:01:42.0,4620314,NYC_DOT_LIC,4620314,Bronx,MDE S HARLEM RIVER PARK - GWB W AMSTERDAM AVENUE UPPER LEVEL
347,41.01,174,0,2015-12-3 00:01:27.0,4456477,MTA Bridges & Tunnels,4456477,Queens,MDE S TBB EXIT RAMP - QUEENS ANCHORAGE
349,8.08,381,0,2015-12-3 09:44:05.0,4329499,NYC_DOT_LIC,4329499,Staten Island,MLK N WALKER STREET - NJ ROUTE 169
350,49.71,166,0,2015-12-3 00:02:06.0,4616196,NYC_DOT_LIC,4616196,Staten Island,MLK S - SIE E WALKER STREET - WOOLEY AVENUE
351,47.85,111,0,2015-12-3 00:02:06.0,4616210,NYC_DOT_LIC,4616210,Staten Island,MLK S - SIE W WALKER STREET - RICHMOND AVENUE
364,11.81,322,0,2015-12-3 00:01:28.0,4456511,NYC_DOT_LIC,4456511,Manhattan,QMT E Manhattan Side - Toll Plaza
365,1.86,1491,0,2015-12-2 23:54:28.0,4456510,NYC_DOT_LIC,4456510,Queens,QMT W Toll Plaza - Manhattan Side
369,37.28,143,0,2015-12-2 21:01:05.0,4616282,NYC_DOT_LIC,4616282,Staten Island,ROUTE 169 S - MLK WALKER STREET
375,64.00,104,0,2015-12-3 00:02:06.0,4616193,NYC_DOT_LIC,4616193,Staten Island,SIE E BRADLEY AVENUE - CLOVE ROAD
376,52.82,103,0,2015-12-3 05:19:03.0,4616192,NYC_DOT_LIC,4616192,Staten Island,SIE E CLOVE ROAD - FINGERBOARD ROAD
377,60.89,64,0,2015-12-3 00:02:06.0,4616195,NYC_DOT_LIC,4616195,Staten Island,SIE E RICHMOND AVENUE - WOOLEY AVENUE
378,55.30,49,0,2015-12-3 00:02:06.0,4616197,NYC_DOT_LIC,4616197,Staten Island,SIE E SOUTH AVENUE - RICHMOND AVENUE
379,55.30,53,0,2015-12-3 00:01:28.0,4763650,NYC_DOT_LIC,4763650,Staten Island,SIE E VNB E FINGERBOARD ROAD - SI GANTRY LOWER LEVEL
380,55.30,44,0,2015-12-3 00:01:28.0,4763648,NYC_DOT_LIC,4763648,Staten Island,SIE E VNB E FINGERBOARD ROAD - SI GANTRY UPPER LEVEL
381,57.17,28,0,2015-12-3 00:02:06.0,4616194,NYC_DOT_LIC,4616194,Staten Island,SIE E WOOLEY AVENUE - BRADLEY AVENUE
382,39.15,182,0,2015-12-2 22:35:05.0,4616216,NYC_DOT_LIC,4616216,Staten Island,SIE E-MLK N RICHMOND AVENUE - WALKER STREET
383,39.15,191,0,2015-12-2 20:57:06.0,4616217,NYC_DOT_LIC,4616217,Staten Island,SIE W - MLK N WOOLEY AVENUE - WLAKER STREET
384,62.76,110,0,2015-12-3 00:02:06.0,4616199,NYC_DOT_LIC,4616199,Staten Island,SIE W - WSE S SOUTH AVENUE - SOUTH AVENUE
385,52.82,27,0,2015-12-3 00:02:06.0,4616208,NYC_DOT_LIC,4616208,Staten Island,SIE W BRADLEY AVENUE - WOOLEY AVENUE
387,45.98,93,0,2015-12-3 00:02:06.0,4616206,NYC_DOT_LIC,4616206,Staten Island,SIE W FINGERBOARD ROAD - CLOVE ROAD
388,62.14,46,0,2015-12-3 00:02:06.0,4616211,NYC_DOT_LIC,4616211,Staten Island,SIE W RICHMOND AVENUE - SOUTH AVENUE
389,11.18,1003,0,2015-12-3 10:03:36.0,4329480,NYC_DOT_LIC,4329480,Staten Island,SIE W SOUTH AVENUE - 278 W BRUNSWICK AVENUE
390,64.00,61,0,2015-12-3 00:02:06.0,4616209,NYC_DOT_LIC,4616209,Staten Island,SIE W WOOLEY AVENUE - RICHMOND AVENUE
394,44.74,160,0,2015-12-3 00:02:06.0,4616225,NYC_DOT_LIC,4616225,Queens,TBB N QUEENS ANCHORAGE - BE N
395,42.87,188,0,2015-12-3 00:01:27.0,4456476,MTA Bridges & Tunnels,4456476,Queens,TBB N QUEENS ANCHORAGE - MANHATTAN LIFT SPAN
August 07, 2017 - 3:23 am UTC
How did you create that control file and CSV? Because when I use it I end up with a whole bunch of rows dated 15 (not 2015):
select count(*) from trafficdata
where extract(year from datevalidfrom) = 15;
COUNT(*)
40