In this python script the time stamp format in the csv file are in the "1/05/2025  12:30:00 AM" or DD/MM/YYYY HH:MM:SS format. This needs to be changed to suit the format used by the database the data is being imported into, which is YYYY-MM-DD HH:MM:SS (24 hour time format). Modify the script to allow for this and produce the whole completed script.

#!/usr/bin/env python3
# For 202505 and later the time stamps in the CSV file are in "1/05/2025  12:30:00 AM"

import os
import sys
from pathlib import Path
import requests
import zipfile
import csv
import psycopg2
from psycopg2 import sql, OperationalError
import configparser
from datetime import datetime
from tenacity import retry, stop_after_attempt, wait_fixed, before_sleep_log, retry_if_exception_type, RetryError
import logging
import argparse

# --- Global Constants ---
PROGRAM_VER = "data-download-archive-3.5.0"
URL_BASE = "https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/"

# --- Paths ---
CWD = Path(__file__).resolve().parent
DATA_DIR = CWD / "temp" 
LOG_DIR = CWD / "log"  
LOGFILE = LOG_DIR / "dl_rooftop_archive.log"  

HEADERS = {
    "User-Agent": "Mozilla/5.0",
    "Accept-Encoding": "gzip, deflate"
}

# --- Utilities ---
def ensure_dir_exists(path: Path):
    path.mkdir(parents=True, exist_ok=True)

ensure_dir_exists(DATA_DIR)
ensure_dir_exists(LOG_DIR)


def write_log(text: str, file: Path = LOGFILE):
    with open(file, 'a') as f:
        f.write(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - {text}\n")


def print_and_log(text: str, err: Exception = None):
    print(text)
    write_log(text)
    if err:
        write_log(f"Error: {err}")


def print_psycopg2_exception(err: Exception):
    _, _, tb = sys.exc_info()
    line_num = tb.tb_lineno
    msg = f"psycopg2 ERROR: {err} on line {line_num}\npgerror: {err.pgerror}\npgcode: {err.pgcode}"
    print_and_log(msg, err)


def config(filename: str = 'aemo_config.ini', section: str = 'postgresql') -> dict:
    parser = configparser.ConfigParser()
    parser.read(filename)

    if parser.has_section(section):
        return dict(parser.items(section))
    
    raise Exception(f"Section {section} not found in {filename}")


def connect_db() -> psycopg2.extensions.connection | None:
    try:
        params = config()
        return psycopg2.connect(**params)
    except OperationalError as err:
        print_psycopg2_exception(err)
        return None


@retry(
    stop=stop_after_attempt(3), 
    wait=wait_fixed(2), 
    before_sleep=before_sleep_log(logging.getLogger(__name__), logging.WARNING),
    retry=retry_if_exception_type(requests.RequestException)
)
def download_file(url: str, dest_path: Path, filename: str, encoded_filename: str) -> Path | None:
    try:
        response = requests.get(url + encoded_filename, timeout=20, headers=HEADERS)
        response.raise_for_status()
        file_path = dest_path / filename
        file_path.write_bytes(response.content)
        print_and_log(f"Downloaded: {url + encoded_filename}")
        return file_path
    except requests.RequestException as e:
        print_and_log(f"Error downloading {url + encoded_filename}: {e}", e)
        raise


def unzip_file(zip_path: Path, extract_to: Path) -> Path | None:
    try:
        with zipfile.ZipFile(zip_path, 'r') as z:
            z.extractall(extract_to)
            csv_file = zip_path.stem + ".CSV"
            csv_path = extract_to / csv_file
            print_and_log(f"Unzipped: {csv_file}")
            return csv_path
    except zipfile.BadZipFile as e:
        print_and_log(f"Error unzipping {zip_path}: {e}", e)
    return None


def process_csv(csv_path: Path):
    conn = connect_db()
    if not conn:
        return

    try:
        with conn.cursor() as cur:
            with open(csv_path, 'r') as file:
                csv_reader = csv.reader(file)
                next(csv_reader)  # Skip header
                
                # Collect rows for batch insert
                rows = []
                for row in csv_reader:
                    if len(row) < 10 or row[0] != "D":
                        continue

                    fields = [f.replace("/", "-").strip() or '0' for f in row[4:10]]
                    rows.append(fields)
                
                # Batch insert if there are any rows
                if rows:
                    cur.executemany(
                        sql.SQL("""
                            INSERT INTO nem_dispatch_rooftop_30min
                            (settlement_date, region, power, qi, type, lastchanged)
                            VALUES (%s, %s, %s, %s, %s, %s)
                            ON CONFLICT (settlement_date, region) DO NOTHING
                        """), rows
                    )
            conn.commit()
    except Exception as e:
        conn.rollback()
        print_and_log(f"Error processing CSV {csv_path}: {e}", e)
    finally:
        conn.close()

        
# --- Main Execution ---
def main(start_year_month: str, end_year_month: str):
    print_and_log(f"Program version: {PROGRAM_VER}")

    start_year, start_month = int(start_year_month[:4]), int(start_year_month[4:])
    end_year, end_month = int(end_year_month[:4]), int(end_year_month[4:])

    for year in range(start_year, end_year + 1):
        # Determine the month range for the given year
        if year == start_year and year == end_year:
            months = range(start_month, end_month + 1)
        elif year == start_year:
            months = range(start_month, 13)
        elif year == end_year:
            months = range(1, end_month + 1)
        else:
            months = range(1, 13)

        for month in months:
            month_str = f"{month:02d}"
            url = f"{URL_BASE}{year}/MMSDM_{year}_{month_str}/MMSDM_Historical_Data_SQLLoader/DATA/"
            
            raw_filename = f"PUBLIC_ARCHIVE#ROOFTOP_PV_ACTUAL#FILE01#{year}{month_str}010000.zip"
            encoded_filename = raw_filename.replace("#", "%23")
            zip_path = DATA_DIR / raw_filename

            # Skip if file already exists
            if zip_path.exists():
                print_and_log(f"{zip_path} already exists. Skipping download.")
            else:    
                try:
                    zip_path = download_file(url, DATA_DIR, raw_filename, encoded_filename)
                except RetryError as e:
                    print_and_log(f"Failed to download {url + encoded_filename} after 3 attempts: {e}", e)
                    continue
            
            if zip_path:
                csv_path = unzip_file(zip_path, DATA_DIR)
                if csv_path:
                    process_csv(csv_path)
                    zip_path.unlink(missing_ok=True)
                    csv_path.unlink(missing_ok=True)


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Download and process AEMO rooftop PV data.")
    parser.add_argument("start_year_month", type=str, help="Start year and month in YYYYMM format")
    parser.add_argument("end_year_month", type=str, help="End year and month in YYYYMM format")
    
    args = parser.parse_args()
    main(args.start_year_month, args.end_year_month)