Search code examples
pythonpymssql

Bulk Insert text file into SQL Server using pymssql

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!

Solution

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