Cannot bulk load. Invalid column number

Posted on 2009-04-17
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
    LVL 31

    Expert Comment

    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

    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

    Can we see the format file and sample csv ?
    LVL 51

    Expert Comment

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

    Author Comment

    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

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now