1

I write this bulk insert format file:

8.0
7
1       SQLCHAR       1       9       " "      1     f1           SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       1       5       "           "     2     f2        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       1       12     " "      3     f3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       1       10     " "     4     f4         SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       1       8     "   "   5     f5         SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       1       5     "   "   6     f6         SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       1       5     "\n"   7     f7         SQL_Latin1_General_CP1_CI_AS

and my file data is:

43266200 6827           43295200 1393/05/23 14:26:26     18      1
43266200 6819           43295200 1393/05/23 14:28:45    113      1

But when i run this query:

bulk insert goy
from 'd:\test.txt'
with(
    formatfile='d:\format.fmt'
);

sql server error is:

Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "d:\format.fmt" is incomplete or could not be read. Operating system error code (null).

how can i solve this problem?

behzad razzaqi
  • 107
  • 2
  • 2
  • 12
  • 1
    Check this answer - http://stackoverflow.com/a/8270755/1080354 – gotqn Nov 02 '14 at 09:53
  • 2
    Your data shows seven columns but your format file only defines six. That's probably what it means by "the file "d:\format.fmt" is incomplete" – Ann L. Nov 02 '14 at 12:00

1 Answers1

3

Add a new line at the end of the fmt file. then save. it will work

Sathiyamoorthy
  • 58
  • 1
  • 8