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 — Gord Thompson
– Gord Thompson, Commented Jun 24, 2018 at 13:20
Also, the backslashes in your string may be causing problems. Try using a raw string like r'C:\Users\ ...' — Gord Thompson
– Gord Thompson, Commented Jun 24, 2018 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
– Gavin, Commented Jun 25, 2018 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
– Gavin, Commented Jun 25, 2018 at 12:43
Try .format(r"'C:\Users\thomsog1\Desktop\TM Tool\Test\SQL\Inputs\OD_List.txt'") — Gord Thompson
– Gord Thompson, Commented Jun 25, 2018 at 13:03

Gord Thompson · Accepted Answer · 2018-06-25 23:27:43Z

2

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'")

answered Jun 25, 2018 at 23:27

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bulk Insert text file into SQL Server using pymssql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related