Cannot bulk load because the file could not be read.

This is an extremely silly problem that may occur while trying to run a bulk insert non-query in T-SQL. The reason I’ve decided to add a post on the issue is that for some reason google didn’t have the answer for it…

So, using the example provided in msdn, I have a .dat file and an .Fmt one; when I run the following:

BULK INSERT myTestFormatFiles
 FROM 'C:\insert.dat'
 WITH (FORMATFILE = 'C:\insertFormat.fmt');

I get the following error- Cannot bulk load because the file “C:\insertFormat.fmt” could not be read.

What I needed to do was to add a new empty row at the end of the fmt file, just go to the end of the file- end of the row:

4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

– and press enter.

Stupid, isn’t it? Just as an after note, you should also keep an eye open on the row ending character issues (“\r\n”, “\n”) and bulk inserting with files containing double quotes.

Explore posts in the same categories: T-SQL

Tags: ,

You can comment below, or link to this permanent URL from your own site.

5 Comments on “Cannot bulk load because the file could not be read.”

  1. Prem Says:

    thnx.. tat helped…

  2. Aaron reese Says:

    There is no mention of this on MSDNs pages. They seem to assume that everyone has access to bcp and that noone would ever hand-roll their own format files

  3. George Says:

    nice one, was scratching my head

  4. Martin Parry Says:

    OMG – That’s pathetic!!!! Another nice feature M$ – Thanks for solving this though 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: