Simple ETL Process using pythan

Question

Hi All  we want to impletement the ETL Process in pythan  can anyone share simple code with working example. Thanks karthik. K

Amit Mohanty · Answer

Let's assume you have a CSV file named input_data.csv with the following content: idnameage 1Rakesh25 2Asit30 3Subhashree22 We want to transform it by adding a birth_year column based on the current year (2023). import pandas as pd from datetime import datetime # Extract: Load the data from the input CSV file input_file = input_data.csv data = pd.read_csv(input_file) # Transform: Calculate birth year based on current year current_year = datetime.now().year data['birth_year'] = current_year - data['age'] # Load: Save the transformed data to a new CSV file output_file = transformed_data.csv data.to_csv(output_file index=False) print(ETL process completed!) After running the above code we'll get a new CSV file named transformed_data.csv with the following content: idnameagebirth_year 1Rakesh251998 2Asit301993 3Subhashree222001 Before running the above code make sure you have the pandas library installed.

Mohammad Hussain · Answer

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()

Deepak Rawat · Answer

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.

Cr Bhargavi · Answer

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()

Karthik K · Answer

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