Cannot bulk load. Invalid column number

Posted on 2009-04-17
Medium Priority
Last Modified: 2012-05-06
Cannot bulk load. Invalid column number in the format file "c:\Temp\TWDelTimes.fmt.txt".

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)

Open in new window

Question by:dreinmann
  • 2
  • 2
  • 2
LVL 31

Expert Comment

ID: 24173002
if you can move to linked server, it would be great. as long as this specific error concern, I would like you to check your .TXT file once, there may be possibility that in some rows, you have less column as against it used to be.
LVL 31

Assisted Solution

RiteshShah earned 800 total points
ID: 24173010
Moreover, try to load data with BCP after once you check your .TXT for columns. Here is small example of BCP and/or linked server.

LVL 51

Accepted Solution

Mark Wills earned 1200 total points
ID: 24176610
Can we see the format file and sample csv ?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 51

Expert Comment

by:Mark Wills
ID: 24176615
what happens if you do a select *

Author Comment

ID: 24184494
Okay, it works now....I think I know what happened though.  Sometime between running it on the server (displayed just fine) and walking 30 feet to a remote computer the C:\Temp folder must have dumped the CSV file.
First, thing this morning I tried running it on the server first (going to retest it) and I got the same error, which got me to looking at the Temp folder to make sure the file was there and it was gone.  Resent the file to C:\Temp and it worked on both.
Windows clears this file out based on Virtual Memory/Page Filing size right?

Author Closing Comment

ID: 31571632
Mark - I gave you some points because you were the one I got the Bulk Load code and format file from before this question and it still works.

Shah - I gave you some points because your link gave me some good info on linked servers and bcp.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question