Simple ETL Process using pythan

Thanks to  all for  vital respones . 

I have share my code. In that i had two issue kindly anyone look into this  provide some solution for this.

#1. where do i need to define the Input file path / Output file Path 

#2. i got a warning like , "   Unresolved import 'src.transform '   "

Thanks in advance ,

Karthik .K 

import csv

# ETL Extract
def extract_data(input_file):
    data = []
    with open(input_file, 'r') as file:
        reader = csv.reader(file)
        for row in reader:
            data.append(row)
    return data

# ETL Transform
def transform_data(data):
    transformed_data = []
    for row in data:
        transformed_row = [cell.upper() for cell in row]
        transformed_data.append(transformed_row)
    return transformed_data

# ETL Load
def load_data(output_file, data):
    with open(output_file, 'w', newline='') as file:
        writer = csv.writer(file)
        writer.writerows(data)

# Main ETL Process
def main():
    input_file = 'input_data.csv'
    output_file = 'output_data.csv'
    
    # Extract
    extracted_data = extract_data(input_file)
    
    # Transform
    transformed_data = transform_data(extracted_data)
    
    # Load
    load_data(output_file, transformed_data)
    
    print("ETL process completed.")

if __name__ == "__main__":
    main()

Here is the just a basic example of how to extract data from a CSV file and load into SQL using Python.

Please note that you'll need to install the necessary libraries if you haven't already:

pip install pandas sqlalchemy

Now, let's go through the ETL process:

import pandas as pd
import pyodbc

# Define the connection string
connection_string = "DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=my_username;PWD=my_password"

# Create a connection to the SQL database
conn = pyodbc.connect(connection_string)

# Load the CSV data into a Pandas DataFrame
df = pd.read_csv("data.csv")

# Transform the data as needed
df.drop_duplicates()
df.fillna(0)

# Load the data into the SQL database
df.to_sql("my_table", conn, if_exists="replace")

# Close the connection to the SQL database
conn.close()

Before running the code, make sure you have the pandas library installed. You can install it using pip if you haven't already:

pip install pandas

Here's a simple example code:

import pandas as pd
import sqlite3

# Extract
csv_file_path = 'data.csv'  # Replace with your CSV file path
data = pd.read_csv(csv_file_path)

# Transform
transformed_data = data.rename(columns={'old_column_name': 'new_column_name'})  # Example transformation

# Load
database_path = 'my_database.db'  # Replace with your desired database file path
conn = sqlite3.connect(database_path)
transformed_data.to_sql('table_name', conn, if_exists='replace', index=False)

# Close the connection
conn.close()

print("ETL process completed.")

Replace 'old_column_name' and 'new_column_name' with the appropriate column names you want to change in the transformation step. Also, replace 'data.csv' with the path to your CSV file and 'my_database.db' with the desired path for your SQLite database file.