Cannot bulk load. Invalid column number in the format file "c:\Temp\TWDelTimes.fmt.tx
At first, I got a error: You do not have permission to use the bulk load statement.
I issued 'Administer bulk operations' permission to the user, but then I got this error.
The Bulk Load works fine when I openview in the Server, but not from the program on a remote cpu.
Using OPENROWSET for bulk loading the csv....see code below.
I was having problems with my other OPENROWSETs...see link....and I switched them to Linked Servers to fix it. Maybe I should do the same with this? I started to, but got stuck on how to apply the format file and skipping the firstrows.
SELECT f1 AS CustID, f2 AS ActArriveTime, f3 AS DayTxt, CONVERT(NUMERIC(10, 4), A.f4) AS DelDuration, f5 AS ArrivalDtTm, CONVERT(NUMERIC(10, 4), A.f6)
AS Cases, f7 AS DrvrID, f8 AS Route
FROM OPENROWSET(BULK 'c:\Temp\TWDelTimes.csv', FORMATFILE = 'c:\Temp\TWDelTimes.fmt.txt', FIRSTROW = 3) AS A
WHERE (LEN(A.f7) > 0) AND (CONVERT(NUMERIC(10, 4), A.f6) > 0) AND (CONVERT(NUMERIC(10, 4), A.f4) > 0)