python - BULK INSERT error code 3: The system cannot find the path specified

Question

I am trying to bulk insert a local file into a remote MS_SQL database using pyodbc. I am able to connect to the DB and I am able to INSERT INTO tables, as I have done it before. Where I have been having issues is to BULK INSERT.

I am using BULK INSERT as a way to speed up my INSERT process.

The code looks like this:

statement = """ BULK INSERT BulkTable FROM 'C:\Users\userName\Desktop\Folder\Book1.csv' WITH (
       FIRSTROW=2,
       FIELDTERMINATOR=',',
       ROWTERMINATOR = '\n'
);
"""
cursor.execute(statement)
cnxn.commit()

This code yields this error:

Traceback (most recent call last):
   File "tester.py", line 41, in <module> cursor.execute(statement)
   pyodbc.ProgrammingError: 
    ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]  Cannot bulk load because the file    "C:\Users\userName\Desktop
\Folder\Book1.csv" could not be opened.
    Operating system error code 3(The system cannot find the path specified.). (4861) (SQLExecDirectW)')`

I really don't understand why it can't open the file as the path is definitely correct.

Let me know if you need any more information.

Answer 1

"I am trying to bulk insert a local file into a remote MS_SQL database"

Your approach is not working because the file specification 'C:\Users\userName\Desktop\Folder\Book1.csv' is only a valid path on the workstation that is running your Python code, but the BULK INSERT documentation explains that

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.

(emphasis mine). That is, the BULK INSERT statement is running on the server, so a file specification on some other machine (like your workstation) is actually a "remote file" as far as the server is concerned. In other words, SQL Server goes looking for a file named C:UsersuserNameDesktopFolderBook1 on the server itself and when that fails it raises the "cannot find the path" error.

In order to use BULK INSERT you would need to either

1. put the file on a network share that the SQL Server can "see", and then supply the UNC path to that file, or

2. upload the file to a local folder on the SQL Server and then supply the local (server) path to the file.

If neither of those alternatives is feasible then your other option from Python is to use the subprocess module to invoke SQL Server's bcp utility to upload the data from your local file into the SQL Server database.