May 21, 2008
Hi,
im attempting the following bulk insert statement:
BULK INSERT gtaRatesTemp FROM 'D: estData.CSV' WITH (FIRSTROW = 3, FORMATFILE = 'D: estFormat.Fmt'
testData.CSV example:
"Country","Country Code","City","City Code","Currency","Item","Item Code","Address 1","Address 2","Address 3","Address 4","Telephone","Rating","Location 1","Location 2","Location 3","No. of","Dates","Dates","Days","Days","Min","Min","Basis","Twin","Single","Triple","Quad","Twin for sole use","Child","Child Age","Child Age","Meals included","Distance to City Centre (Kms)","Child Details","Special Conditions"
"","","","","","","","","","","","","","","","","Rooms","From","To","From","To","Pax","Nights","","Price","Price","Price","Price","Price","Price","From","To","","",""
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","14 Jun 2007","30 Jun 2007","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Jul 2007","31 Oct 2007","Mon","Sun","1","2","Room","231.00","231.00","","","231.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Nov 2007","31 Mar 2008","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","14 Jun 2007","30 Jun 2007","Mon","Sun","1","1","Room","412.50","412.50","","","412.50","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","01 Jul 2007","30 Nov 2007","Mon","Sun","1","1","Room","463.00","463.00","","","463.00","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
testFormat.Fmt:
8.0
36
1 SQLCHAR 0 50 """ 0 country SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "","" 1 countryCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 200 "","" 2 city SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "","" 3 cityCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "","" 4 currency SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 500 "","" 5 item SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "","" 6 itemCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 400 "","" 7 address1 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 400 "","" 8 address2 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 400 "","" 9 address3 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 400 "","" 10 address4 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "","" 11 telephone SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "","" 12 rating SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "","" 13 location1 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "","" 14 location2 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "","" 15 location3 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "","" 16 noOfRooms SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "","" 17 datesFrom SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 100 "","" 18 datesto SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 50 "","" 19 daysFrom SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "","" 20 daysTo SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 100 "","" 21 minPAX SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 100 "","" 22 minNights SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 50 "","" 23 basis SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 100 "","" 24 twinPrice SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 100 "","" 25 singlePrice SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 100 "","" 26 triplePrice SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 100 "","" 27 quadPrice SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 100 "","" 28 twinForSoleUsePrice SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 100 "","" 29 childPrice SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 100 "","" 30 childAgeFrom SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 100 "","" 31 childAgeTo SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "","" 32 mealsIncluded SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 50 "","" 33 distanceToCityCentre SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 500 "","" 34 childDetails SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1500 ""
" 35 specialConditions SQL_Latin1_General_CP1_CI_AS
My problem is that once inserted, column 35(childDetails) and 36(specialConditions) are being merged into one. I think it may be an error with one of the values i have for the terminator in the format file but im unsure what exactly.
Does anyone have any ideas?
View 9 Replies
View Related