Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
691 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

"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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...