1

I'm having an EOF issue when trying to bcp a .csv file I generated with Python's csv.writer. I've done lots of googling with no luck, so I turn to you helpful folks on SO

Here's the error message (which is triggered on the subprocess.call() line):

Starting copy...
Unexpected EOF encountered in BCP data-file.
bcp copy in failed

Here's the code:

sel_str = 'select blahblahblah...'
result = engine.execute(sel_str)  #engine is a SQLAlchemy engine instance

# write to disk temporarily to be able to bcp the results to the db temp table
with open('tempscratch.csv','wb') as temp_bcp_file:
    csvw = csv.writer(temp_bcp_file)
    for r in result:
        csvw.writerow(r)
        temp_bcp_file.flush()

# upload the temp scratch file
bcp_string = 'bcp tempdb..collection in @INFILE -c -U username -P password -S DSN'
bcp_string = string.replace(bcp_string,'@INFILE','tempscratch.csv')
result_code = subprocess.call(bcp_string, shell=True)

I looked at the tempscratch.csv file in a text editor and didn't see any weird EOF or other control characters. Moreover, I looked at other .csv files for comparison, and there doesn't seem to be a standardized EOF that bcp is looking for.

Also, yes this is hacky, pulling down a result set, writing it to disk and then reuploading it to the db with bcp. I have to do this because SQLAlchemy does not support multi-line statements (aka DDL and DML) in the same execute() command. Further, this connection is with a Sybase db, which does not support SQLAlchemy's wonderful ORM :( (which is why I'm using execute() in the first place)

ignorantslut
  • 457
  • 2
  • 8
  • 17
  • Have you printed `bcp_string` and tried to execute that in a terminal? Maybe the command string is just wrong. You should avoid `shell=True`. Compile your command by creating a list of the name of the executable and its arguments, like `["bcp", "tempdb..collection", "in", ...]`. – Dr. Jan-Philip Gehrcke May 24 '12 at 15:03
  • @Jan-PhilipGehrcke The string is correct, but the bcp command returns the same error in the console. Also, thanks for the security tip :) – ignorantslut May 24 '12 at 15:09
  • I've still no idea what is wrong, but one more comment to your code: why do you need the `flush()` call? After leaving the `with` context, the file is properly closed and buffers have been, of course, flushed before. – Dr. Jan-Philip Gehrcke May 24 '12 at 15:12
  • @Jan-PhilipGehrcke I thought the EOF issue may be due to the fact that the file wasn't being closed properly, so I followed the example in [this SO question](http://stackoverflow.com/questions/3976711/csvwriter-not-saving-data-to-file-why). Is it extraneous? – ignorantslut May 24 '12 at 15:16
  • It is only helpful, if you'd like to monitor the file content with another process (e.g. with `tail -f`) while the file is still written (i.e. during `for r in result`). If you do not want to do this, `flush()` is actually wasting time. After the `with open...` block, all buffers are flushed and the file is in any case properly closed. – Dr. Jan-Philip Gehrcke May 25 '12 at 08:44
  • I realize this is ancient, but did this ever work? Imqa – Davos Sep 24 '17 at 11:13
  • I realize this is ancient, but did this ever work? Uploading to tempdb, was the table still available for subsequent processing via SQL Alchemy? I would think the session scope would end, or was tempdb just fake code for the example? I am writing something to automate BCP in Python and seeing if anyone else had done it. In my case using the SQL Server tools for Linux. – Davos Sep 24 '17 at 11:19

1 Answers1

5

From what I can tell, the bcp default field delimiter is the tab character '\t' while Python's csv writer defaults to the comma. Try this...

# write to disk temporarily to be able to bcp the results to the db temp table
with open('tempscratch.csv','wb') as temp_bcp_file:
    csvw = csv.writer(temp_bcp_file, delimiter = '\t')
    for r in result:
        csvw.writerow(r)
    temp_bcp_file.flush()
parselmouth
  • 1,598
  • 8
  • 8