Archive for the ‘T-SQL’ category

Cannot bulk load because the file could not be read.

January 1, 2010

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.