使用pyodbc向SQL Server批量插入数据：找不到文件报错

Got it, let's break down this bulk load error you're hitting with pyodbc and SQL Server. The operating system error code 3 is just SQL Server's way of saying "I can't find that CSV file you told me about"—here are the most common fixes to get this working:

• Fix your file path formatting
  SQL Server is picky about path syntax. First, make sure you're escaping backslashes properly. In Python, use a raw string (prefix with r) to avoid accidental escape sequences, or swap backslashes for forward slashes. For example:

  # Raw string (avoids escape issues)
  file_path = r"C:\Users\kdalal\callerx_project\caller_x\new_file_name.csv"
  # Or use forward slashes
  file_path = "C:/Users/kdalal/callerx_project/caller_x/new_file_name.csv"
  

  When inserting this into your BULK INSERT SQL statement, ensure the path is wrapped in single quotes as shown in the example below.

• Make sure the SQL Server service can access the file
  SQL Server runs under a dedicated service account (like NT SERVICE\MSSQLSERVER for default instances). This account needs read permissions on the CSV file and its parent folders. To fix this:

  1. Right-click your CSV file → Properties → Security tab
  2. Click "Edit" → "Add" → Type the service account name (e.g., NT SERVICE\MSSQLSERVER) and click "Check Names" to confirm
  3. Grant the account "Read" permission and save changes
     If your SQL Server is on a remote machine, you'll need to place the CSV in a network share that the service account can access, and use a UNC path like \\your-server\shared-folder\new_file_name.csv.

• Double-check the file actually exists at that path
  It sounds obvious, but typos in the folder name, filename (including capitalization—Windows ignores it, but SQL Server can be finicky), or accidentally moving the file will trigger this error. Manually navigate to the path in File Explorer to confirm the file is exactly where you think it is.

• Avoid relative paths
  SQL Server's BULK INSERT doesn't reliably handle relative paths. Always use a full absolute path to eliminate ambiguity about where the server should look for the file.

Example Working Code #

Here's a cleaned-up snippet that incorporates these fixes:

import pyodbc

# Establish connection (update with your server/db credentials)
conn = pyodbc.connect(
    "DRIVER={SQL Server};SERVER=your-server-name;DATABASE=your-db-name;UID=your-username;PWD=your-password"
)
cursor = conn.cursor()

# Use raw string for file path
csv_path = r"C:\Users\kdalal\callerx_project\caller_x\new_file_name.csv"

# Build BULK INSERT statement (adjust options like FIRSTROW if your CSV has headers)
bulk_insert_sql = f"""
BULK INSERT YourTargetTableName
FROM '{csv_path}'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\\n',
    FIRSTROW = 2,  -- Skip header row if present
    TABLOCK
)
"""

try:
    cursor.execute(bulk_insert_sql)
    conn.commit()
    print("CSV data inserted successfully!")
except pyodbc.ProgrammingError as e:
    print(f"Error occurred: {e}")
finally:
    # Clean up connections
    cursor.close()
    conn.close()

If you're still stuck, try moving the CSV to a simpler path (like C:\temp\new_file_name.csv) to rule out issues with long paths or special characters in folder names.

内容的提问来源于stack exchange，提问作者Krishnang K Dalal