Source Code: Import Multiple CSV Files (Data Files) to SQL Server With Python

Buy Me a Coffee? Your support is much appreciated!

Source Code

import os
import pypyodbc as odbc # pip install pypyodbc

def bulk_insert(date_file, target_table):
    sql = f"""
        BULK INSERT {target_table}
        FROM '{date_file}'
        WITH
        (   
            FORMAT='CSV',
            FIRSTROW = 2, 
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = '\\n'
        )    
    """.strip()
    return sql

# Step 1. Establish SQL Server Connection
SERVICE_NAME = '<server name>'
DATABASE_NAME = '<database name>'
target_table = '<table name>'

conn = odbc.connect(f"""
    Driver={{SQL Server}};
    Server={SERVICE_NAME};
    Database={DATABASE_NAME};
    # uid=<user id>;
    # pwd=<password>;
""".strip())
print(conn)

# Step 2. Iterate through data files and upload
data_file_folder = os.path.join(os.getcwd(), 'Data Files')
data_files = os.listdir(data_file_folder)

cursor = conn.cursor()
try:
    # here we can use with statement to automatically close connection once the operation is complete
    with cursor:
        for data_file in data_files:
            if data_file.endswith('.csv'):
                cursor.execute(bulk_insert(os.path.join(data_file_folder, data_file), target_table))
                print(os.path.join(data_file_folder, data_file), target_table + ' inserted')
        cursor.commit()
except Exception as e:
    print(e)
    conn.rollback()
    print('Transaction rollback')