BULK INSERT into SQL Server table using pyodbc: cannot find file

Question

I know this kind of question has been asked before but still couldn't find the answer I'm looking for. I'm doing bulk insert of the csv file into the SQL Server table but I am getting error shown below:

My Code:

df_output.to_csv('new_file_name.csv', sep=',', encoding='utf-8')
conn = pyodbc.connect(r'DRIVER={SQL Server}; PORT=1433; SERVER=Dev02; DATABASE=db;UID='';PWD='';')
curr = conn.cursor()
print("Inserting!")
curr.execute("""BULK INSERT STG_CONTACTABILITY_SCORE
               FROM 'C:\\Users\\kdalal\\callerx_project\\caller_x\\new_file_name.csv'
               WITH
               (
                 CODEPAGE = 'ACP',
                 FIRSTROW = 2,
                 FIELDTERMINATOR = ',',
                 ROWTERMINATOR = '\n'
                 );""")
conn.commit()

The Error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot bulk load because the file "C:\Users\kdalal\callerx_project\caller_x\new_file_name.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.). (4861) (SQLExecDirectW)')

'new_file_name.csv' is in the specified path. I tried changing the path to just 'new_file_name.csv' since it is in the folder from where I am running the script still it throws a

file does not exists

Can you please tell me what am I doing wrong here. Thanks a lot in advance.

Is the code file .py in the same path? `C:\\Users\\kdalal\\callerx_project\\caller_x\\` — virtualdvid, Feb 13 '18 at 20:34
try `df_output.to_csv('C:\\Users\\kdalal\\callerx_project\\caller_x\\new_file_name.csv', sep=',', encoding='utf-8')` — virtualdvid, Feb 13 '18 at 20:39
Is the SQL Server instance on a different machine from the one that is running your Python code? — Gord Thompson, Feb 13 '18 at 20:58
@GordThompson: yes, I am trying to bulk insert csv file from my windows machine to SQL Server — Krishnang K Dalal, Feb 13 '18 at 21:16

score 4 · Accepted Answer · answered Feb 14 '18 at 00:03

The BULK INSERT statement is executed on the SQL Server machine, so the file path must be accessible from that machine. You are getting "The system cannot find the path specified" because the path

C:\\Users\\kdalal\\callerx_project\\caller_x\\new_file_name.csv

is a path on your machine, not the SQL Server machine.

Since you are dumping the contents of a dataframe to the CSV file you could simply use df.to_sql to push the contents directly to the SQL Server without an intermediate CSV file. To improve performance you can tell SQLAlchemy to use pyodbc's fast_executemany option as described in the related question

Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC

Got it. I tried this and it worked. The performance was decent. Thank you. — Krishnang K Dalal, Feb 14 '18 at 14:48

BULK INSERT into SQL Server table using pyodbc: cannot find file

1 Answers1

Linked