Import a CSV File to SQL Server using Python

In this guide, you’ll see a simple technique to import a CSV file to SQL Server using Python.

Here are the steps:

Steps to Import a CSV file to SQL Server using Python

Step 1: Prepare the CSV File

To begin, let’s prepare a CSV file called ‘products’ with the following data:

Step 2: Import the CSV File into a DataFrame

You may use the Pandas library to import a CSV file into a DataFrame.

For demonstration purposes, let’s assume that our CSV file will be stored under the following path:

Here is the code to import the CSV file for our example (note that you’ll need to change the path to reflect the location where the CSV file is stored on your computer):

import pandas as pd

data = pd.read_csv(r"C:\Users\Ron\Desktop\Test\products.csv")
df = pd.DataFrame(data)

print(df)

This is how the DataFrame would look like in Python:

   product_id  product_name  price
0           1        Laptop   1200
1           2       Printer    200
2           3        Tablet    350
3           4      Keyboard     80
4           5       Monitor    400

Step 3: Connect Python to SQL Server

To connect Python to SQL server, you’ll need the:

• Server Name. Let’s assume that the server name is: RON-SQLEXPRESS
• Database Name. The database name for our example would be: test_database

Here is the code to connect Python to SQL for our example:

import pyodbc

conn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server=RON-SQLEXPRESS;"
    "Database=test_database;"
    "Trusted_Connection=yes;"
)
cursor = conn.cursor()

You may wish to check the following guide that explains the full steps to connect Python to SQL Server using Pyodbc.

Step 4: Create a Table in SQL Server using Python

Next, add the syntax to create the table in SQL Server. This table will be used to store the imported data from the CSV file.

For our example, you can add the following syntax to create the ‘products‘ table:

cursor.execute(
    """
    CREATE TABLE products (
    product_id int primary key,
    product_name nvarchar(50),
    price int
    )
    """
)

Note that whenever you run the code to create a table. You should only use that piece of the code once. Otherwise, you’ll get the error below:

ProgrammingError: (’42S01′, “[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named ‘products’ in the database. (2714) (SQLExecDirectW)”)

Step 5: Insert the DataFrame Data into the Table

Here is the syntax to insert the DataFrame data (from step-2) into the products table:

for row in df.itertuples():
    cursor.execute(
        """
        INSERT INTO products (product_id, product_name, price)
        VALUES (?,?,?)
        """,
        row.product_id,
        row.product_name,
        row.price,
    )
conn.commit()

And here is the entire code to import the CSV file into SQL Server using Python:

import pandas as pd
import pyodbc

# Import CSV
data = pd.read_csv(r"C:\Users\Ron\Desktop\Test\products.csv")
df = pd.DataFrame(data)

# Connect to SQL Server
conn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server=RON-SQLEXPRESS;"
    "Database=test_database;"
    "Trusted_Connection=yes;"
)
cursor = conn.cursor()

# Create Table
cursor.execute(
    """
    CREATE TABLE products (
    product_id int primary key,
    product_name nvarchar(50),
    price int
    )
    """
)

# Insert DataFrame to Table
for row in df.itertuples():
    cursor.execute(
        """
        INSERT INTO products (product_id, product_name, price)
        VALUES (?,?,?)
        """,
        row.product_id,
        row.product_name,
        row.price,
    )
conn.commit()

Run the code in Python (after making the adjustment to the path where your CSV file is stored, as well as making the changes to your database connection info).

Step 6: Perform a Test

Let’s run a simple query to check that the values from the CSV file got imported into SQL Server:

SELECT * FROM products

Here is the result: