Bulk Insert text file into SQL Server using pymssql

Question

I'm trying to import a text file into SQL Server 2014 using pymssql (I'm a bit of a beginner with python but it seems this is the easiest way to do it if you know a bit of SQL). SQL server sits on the same machine as the file I'm importing.

This is my current code

SQLCon = pymssql.connect(host=ServerNm,database=DatabaseNm)
Cursor = SQLCon.cursor()

BulkInsert = '''
    BULK INSERT OD_List
    FROM {}
    WITH (
        FIRSTROW=2
      , FIELDTERMINATOR=','
      , ROWTERMINATOR='\n'
    )
'''.format("'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'")

Cursor.execute(BulkInsert)
SQLCon.commit()

I have found a few coding examples on the internet and tried them all to no avail... I keep ending up with the following error:

File "pymssql.pyx", line 467, in pymssql.Cursor.execute (pymssql.c:7561)      
pymssql.OperationalError: (4861, 'Cannot bulk load because the file 
"C:\\Users\thomsog1\\Desktop\\TM Tool\\Test\\SQL\\Inputs\\OD_List.txt" could 
not be opened. Operating system error code 123(The filename, directory name, 
or volume label syntax is incorrect.).DB-Lib error message 20018, severity 
16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

Any help would be really appreciated!

Possible duplicate of [BULK INSERT error code 3: The system cannot find the path specified](https://stackoverflow.com/questions/42537100/bulk-insert-error-code-3-the-system-cannot-find-the-path-specified) — Gord Thompson, Jun 24 '18 at 13:20
Also, the backslashes in your string may be causing problems. Try using a raw string like `r'C:\Users\ ...'` — Gord Thompson, Jun 24 '18 at 13:24
Thanks for replying. I did see that question, however SQL Server sits on the same machine as the file I'd be importing so I didn't think it was relevant. Apologies I didn't make that clear prior, I've edited the question accordingly. — Gavin, Jun 25 '18 at 12:42
I have tried both .format("r'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'") and .format(r'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'). They give an "invalid object name" error. — Gavin, Jun 25 '18 at 12:43
Try `.format(r"'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'")` — Gord Thompson, Jun 25 '18 at 13:03

score 2 · Accepted Answer · answered Jun 25 '18 at 23:27

The file path in the FROM clause of the BULK INSERT statement needs to be enclosed in single quotes for T-SQL, but it also includes backslashes so we need to use a Python raw string (r"..."), hence

.format(r"'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'")

Bulk Insert text file into SQL Server using pymssql

1 Answers1