Bulk insert with format file to exclude columns
-
Hello,
I've been trying for the past couple of days to get this CSV file import. The file contains more columns than what is required.
Columns after "Days_left" is what im tring to exlcude.
CSV format:
"Service_Tag","System_Type","Ship_Date","Dell_IBU","Description","Provider","Start_Date","End_Date","Days_Left","teemp1","teemp2","teemp3","teemp4","teemp5"
"6zhq971","poweredge 2850","4/24/2005","united states","next business day support","uny","4/24/2006","4/23/2008","0",""
"f5q16b1","poweredge 2950","6/19/2006","united states","bronze software support","dell","6/19/2006","6/18/2009","0","next business day support","uny","6/19/2007","6/18/2009","0",""
"8vbkq31","poweredge 1750","11/14/2003","united states","next business day support","uny","11/13/2004","11/13/2006","0",""
"8sz481s","poweredge 1600sc","6/25/2003","australia","pow (parts only warranty)","dell","6/26/2003","6/26/2006","0","nbd (next business day on-site)","dell","6/26/2003","6/26/2006","0",""
"4r140m1","dell precision mobile workstation m4500","9/3/2010","united states","next business day support","qlx","9/4/2011","9/3/2013","724",""
Format File:
10.0
14
1 SQLCHAR 0 50 "\", \"" 1 ServiceTag "
2 SQLCHAR 0 50 "\", \"" 2 SystemType "
3 SQLCHAR 0 50 "\", \"" 3 ShipDate "
4 SQLCHAR 0 50 "\", \"" 4 DellIBU "
5 SQLCHAR 0 50 "\", \"" 5 Description "
6 SQLCHAR 0 50 "\", \"" 6 Provider "
7 SQLCHAR 0 50 "\", \"" 7 StartDate "
8 SQLCHAR 0 50 "\", \"" 8 EndDate "
9 SQLCHAR 0 50 "\", \"" 9 DaysLeft "
10 SQLCHAR 0 50 "\", \"" 0 teemp1 "
11 SQLCHAR 0 50 "\", \"" 0 teemp2 "
12 SQLCHAR 0 50 "\", \"" 0 teemp3 "
13 SQLCHAR 0 50 "\", \"" 0 teemp4 "
14 SQLCHAR 0 50 "\",""" 0 teemp5 "
TABLE:
CREATE TABLE WARRANTY
(
ServiceTag nvarchar(50) NULL
,SystemType nvarchar(50)NULL
,ShipDate nvarchar(50)NULL
,DellIBU nvarchar(50)NULL
,Description nvarchar(50)NULL
,Provider nvarchar(50)NULL
,StartDate nvarchar(50)NULL
,EndDate nvarchar(50)NULL
,Daysleft nvarchar(50)NULL
,teemp1 nvarchar(50)NULL
,teemp2 nvarchar(50)NULL
,teemp3 nvarchar(50)NULL
,teemp4 nvarchar(50)NULL
,teemp5 nvarchar(50)NULL
)
BULK STATEMENT:
bulk INSERT
Warranty
FROM 'E:\DellStaging\importdataewfile.csv'
WITH
(FORMATFILE='E:\DellStaging\quote.fmt'
)
Result
Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "E:\DellStaging\quote.fmt".
-
The first problem that I see is that the "delimiter" column of the format file has spaces in it that should not be there...
v----------- There's a space here and none of the data matches."\", \""Second, if you intend to "throw away" the data in the columns you've marked as "0" for a column number, then the column name must NOT (IIRC) match any column names in the target table. Give them bogus names in the format file like NotUsed1, NotUsed2, etc.
Third, if you don't intend to use those columns, why did you include them in your target table? There might be a good reason but I had to ask the question.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just checked the data you posted. All rows in the file must have precisely the same number of delimiters... including the header. You have a 14 "column" header yet much of the data only has 10 "columns". This frequently happens when folks export from a spreadsheet and they have no data in the right-most columns. BCP won't handle that with any grace at all.
You have two choices, as I see it. 1) Get the people providing the data to fix it or 2) live with the "ragged-right" nature of the data by importing each row as a whole "blob" and split the data from there.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply