How to append EOF to file using Perl or Python?

Question

I’m trying to bulk insert data to SQL server express database. When doing bcp from Windows XP command prompt, I get the following error:

C:\temp>bcp  in  -T -f  -S 

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 4391

So, there is a problem with EOF. How to append a correct EOF character to this file using Perl or Python?

Answer 1

This is not a problem with missing EOF, but with EOF that is there and is not expected by bcp.

I am not a bcp tool expert, but it looks like there is some problem with format of your data files.

Answer 2

EOF is End Of File. What probably occurred is that the file is not complete; the software expects data, but there is none to be had anymore.

These kinds of things happen when:

• the export is interrupted (quit dump software while dumping)
• while copying the dumpfile aborting the copy
• disk full during dump

these kinds of things.

By the way, though EOF is usually just an end of file, there does exist an EOF character. This is used because terminal (command line) input doesn't really end like a file does, but it sometimes is necessary to pass an EOF to such a utility. I don't think it's used in real files, at least not to indicate an end of file. The file system knows perfectly well when the file has ended, it doesn't need an indicator to find that out.

EDIT shamelessly copied from a comment provided by John Machin

It can happen (uninentionally) in real files. All it needs is (1) a data-entry user to type Ctrl-Z by mistake, see nothing on the screen, type the intended Shift-Z, and keep going and (2) validation software (written by e.g. the company president's nephew) which happily accepts Ctrl-anykey in text fields and your database has a little bomb in it, just waiting for someone to produce a query to a flat file.

Answer 3

Unexpected EOF means that the bcp reader found an EOF when it was expecting more data. This EOF can be:

(1) the actual physical end-of-file (no more bytes to be read). This means that you have mis-formatted data. Check near the end of your file for an incomplete record.

OR

(2) on Windows, where you are, programs reading a file in text mode honour the ancient convention inherited via MS-DOS from CP/M of regarding Ctrl-Z (aka ^Z aka \'x1A' aka SUB aka SUBSTITUTE) as an end-of-file marker when reading from ANY file, not just a terminal. This includes Python -- the behaviour is determined by the C stdlib. Check for '\x1A' in your data.

Update responding to comments in a legible fashion:

In Notepad++, you can make it display unusual characters by doing View / Show Symbol / Show All Characters. You can search by doing Ctrl-F, typing \x1a in the Find What box, and selecting the Extended radio button in the Search panel.

Or you can with a little bit of Python get the line number of the first Ctrl-Z:

bytes = open('bcp.dat', 'rb').read()
zpos = bytes.find('\x1a')
# if zpos is -1, no Ctrl-Z in file
print 1 + bytes[:zpos].count('\r\n')

Where your .dat was created doesn't matter. An unintentional Ctrl-Z can happen anywhere in a file created on any operating system. It is where it is being read as a text file that matters -- Windows? Bang!