Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

Complete the "processcsv" function in this python script to insert the data from the CSV…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with chatgpt-4o-latest-20250326’s answer.
  1. User

    Complete the "process_csv" function in this python script to insert the data from the CSV files to the table "nem_interconnect_5m" with the following structure:

    Column Type Comment run_datetime timestamp NULL intervention integer NULL interconnectorid character varying(20) interval_datetime timestamp meteredmwflow numeric(12,5) NULL mwflow numeric(12,5) NULL mwlosses numeric(12,5) NULL marginalvalue numeric(12,5) NULL violationdegree numeric(12,5) NULL mnsp integer NULL exportlimit numeric(12,5) NULL importlimit numeric(12,5) NULL marginalloss numeric(12,5) NULL exportgenconid character varying(40) NULL importgenconid character varying(40) NULL fcasexportlimit numeric(12,5) NULL fcasimportlimit numeric(12,5) NULL lastchanged timestamp NULL

    Indexes PRIMARY interconnectorid, interval_datetime

    Python Script:

    #!/usr/bin/env python3

    File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip

    URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/

    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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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
    
                    # Extract data and map to database columns, adjusting for the CSV structure.
                    #   INTERVAL_DATETIME, REGIONID, POWER, LASTCHANGED, TYPE, QI
                    try:
                        run_datetime_str = row[4].strip() # e.g., "2023/01/01 00:00:00"
                        run_datetime = datetime.strptime(run_datetime_str, "%Y/%m/%d %H:%M:%S")
                        region = row[5].strip()
                        power = float(row[6].strip()) if row[6].strip() else 0.0 # Ensure a float value
                        lastchanged_str = row[7].strip()
                        lastchanged = datetime.strptime(lastchanged_str, "%Y/%m/%d %H:%M:%S") if lastchanged_str else None
                        type_ = row[8].strip()
                        qi = float(row[9].strip()) if row[9].strip() else 0.0  # Ensure a float value
    
                        rows.append((run_datetime,intervention,interconnectorid,interval_datetime,meteredmwflow,mwflow,mwlosses,marginalvalue,violationdegree,mnsp,exportlimit,importlimit,marginalloss,exportgenconid,importgenconid,fcasexportlimit,fcasimportlimit,lastchanged))
    
                    except (ValueError, IndexError) as e:
                        print_and_log(f"Skipping row due to data error: {row}. Error: {e}")
                        continue # Skip to the next row if there's an error
    
                
                # Batch insert if there are any rows
                if rows:
                    cur.executemany(
                        sql.SQL("""
                            INSERT INTO nem_interconnect_5m
                            (run_datetime,intervention,interconnectorid,interval_datetime,meteredmwflow,mwflow,mwlosses,marginalvalue,violationdegree,mnsp,exportlimit,importlimit,marginalloss,exportgenconid,importgenconid,fcasexportlimit,fcasimportlimit,lastchanged)
                            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                            ON CONFLICT (interconnectorid, interval_datetime) 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/"
            # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
            raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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)
    
  2. Gemini Assistant

    To complete the process_csv function, we need to correctly parse the CSV file content, map the data to the nem_interconnect_5m table columns, convert data types, and handle potential NULL values.

    Based on the typical AEMO MMSDM PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN file structure, the CSV data records (starting with 'D') generally follow this order:

    1. RECORD_TYPE (D)
    2. VERSION
    3. INTERVAL_DATETIME
    4. INTERCONNECTORID
    5. RUN_DATETIME
    6. INTERVENTION
    7. MWFLOW
    8. MWLOSSES
    9. MARGINALVALUE
    10. VIOLATIONDEGREE
    11. MNSP
    12. EXPORTLIMIT
    13. IMPORTLIMIT
    14. MARGINALLOSS
    15. EXPORTGENCONID
    16. IMPORTGENCONID
    17. FCASIMPORTLIMIT
    18. FCASEXPORTLIMIT
    19. LASTCHANGED
    20. METEREDMWFLOW (This column is often optional or at the very end and might not be present in older files. It's marked as NULL in the database, so we handle its absence.)

    The AEMO CSV files typically have a "C" record (control record) as the first line, followed by a "D" record that serves as the header for the data. Thus, we need to skip the first two lines of the CSV reader to get to the actual data.

    Here's the completed process_csv function:

    #!/usr/bin/env python3
    # File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
    # URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/
    
    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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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)
                # Find the actual CSV file name within the zip, as it's not always just zip_path.stem + ".CSV"
                # It's usually the same as the zip stem, but can have different case or extensions inside.
                # A more robust approach would be to list contents of the zip and find the .CSV.
                # However, assuming AEMO standard name for now:
                csv_file = zip_path.stem + ".CSV"
                csv_path = extract_to / csv_file
                if not csv_path.exists(): # Fallback for common alternative casing or extension
                    csv_file = zip_path.stem + ".csv"
                    csv_path = extract_to / csv_file
                if not csv_path.exists():
                    # If still not found, try to list and find the first CSV
                    csv_files_in_zip = [f for f in z.namelist() if f.lower().endswith('.csv')]
                    if csv_files_in_zip:
                        csv_path = extract_to / csv_files_in_zip[0]
                    else:
                        print_and_log(f"No CSV file found in {zip_path}")
                        return None
    
                print_and_log(f"Unzipped: {csv_path.name}")
                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)
                    
                    # AEMO CSV files typically have a C-record line first, then a D-record header line.
                    # Skip both to get to the actual data rows.
                    try:
                        next(csv_reader) # Skip C-record (control record)
                        next(csv_reader) # Skip D-record header
                    except StopIteration:
                        print_and_log(f"CSV file {csv_path} is too short or malformed (missing header/control records).")
                        return
                    
                    rows_to_insert = []
                    for row in csv_reader:
                        # Data rows start with 'D'. Minimum number of columns for essential data (up to LASTCHANGED) is 19.
                        if not row or row[0].strip() != "D" or len(row) < 19:
                            print_and_log(f"Skipping malformed or non-data row in {csv_path}: {row}")
                            continue
    
                        try:
                            # Extract and convert data based on the identified CSV structure
                            # CSV Column Index Mapping (from assumed AEMO PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN structure):
                            # D,VERSION,INTERVAL_DATETIME(2),INTERCONNECTORID(3),RUN_DATETIME(4),INTERVENTION(5),
                            # MWFLOW(6),MWLOSSES(7),MARGINALVALUE(8),VIOLATIONDEGREE(9),MNSP(10),
                            # EXPORTLIMIT(11),IMPORTLIMIT(12),MARGINALLOSS(13),EXPORTGENCONID(14),IMPORTGENCONID(15),
                            # FCASIMPORTLIMIT(16),FCASEXPORTLIMIT(17),LASTCHANGED(18),METEREDMWFLOW(19) (optional)
    
                            # 1. run_datetime (timestamp NULL) - CSV index 4
                            run_datetime_str = row[4].strip()
                            run_datetime = datetime.strptime(run_datetime_str, "%Y/%m/%d %H:%M:%S") if run_datetime_str else None
    
                            # 2. intervention (integer NULL) - CSV index 5
                            intervention = int(row[5].strip()) if row[5].strip() else None
    
                            # 3. interconnectorid (character varying(20)) - CSV index 3. Must not be NULL (part of PK)
                            interconnectorid = row[3].strip()
                            if not interconnectorid:
                                 raise ValueError("interconnectorid cannot be empty or NULL.")
    
                            # 4. interval_datetime (timestamp) - CSV index 2. Must not be NULL (part of PK)
                            interval_datetime_str = row[2].strip()
                            interval_datetime = datetime.strptime(interval_datetime_str, "%Y/%m/%d %H:%M:%S")
                            if not interval_datetime:
                                raise ValueError("interval_datetime cannot be empty or NULL.")
    
                            # 5. meteredmwflow (numeric(12,5) NULL) - CSV index 19 (optional)
                            # Check if row has enough columns for meteredmwflow
                            meteredmwflow = float(row[19].strip()) if len(row) > 19 and row[19].strip() else None
    
                            # 6. mwflow (numeric(12,5) NULL) - CSV index 6
                            mwflow = float(row[6].strip()) if row[6].strip() else None
    
                            # 7. mwlosses (numeric(12,5) NULL) - CSV index 7
                            mwlosses = float(row[7].strip()) if row[7].strip() else None
    
                            # 8. marginalvalue (numeric(12,5) NULL) - CSV index 8
                            marginalvalue = float(row[8].strip()) if row[8].strip() else None
    
                            # 9. violationdegree (numeric(12,5) NULL) - CSV index 9
                            violationdegree = float(row[9].strip()) if row[9].strip() else None
    
                            # 10. mnsp (integer NULL) - CSV index 10
                            mnsp = int(row[10].strip()) if row[10].strip() else None
    
                            # 11. exportlimit (numeric(12,5) NULL) - CSV index 11
                            exportlimit = float(row[11].strip()) if row[11].strip() else None
    
                            # 12. importlimit (numeric(12,5) NULL) - CSV index 12
                            importlimit = float(row[12].strip()) if row[12].strip() else None
    
                            # 13. marginalloss (numeric(12,5) NULL) - CSV index 13
                            marginalloss = float(row[13].strip()) if row[13].strip() else None
    
                            # 14. exportgenconid (character varying(40) NULL) - CSV index 14
                            exportgenconid = row[14].strip() if row[14].strip() else None
    
                            # 15. importgenconid (character varying(40) NULL) - CSV index 15
                            importgenconid = row[15].strip() if row[15].strip() else None
    
                            # 16. fcasexportlimit (numeric(12,5) NULL) - CSV index 17
                            fcasexportlimit = float(row[17].strip()) if row[17].strip() else None
    
                            # 17. fcasimportlimit (numeric(12,5) NULL) - CSV index 16
                            fcasimportlimit = float(row[16].strip()) if row[16].strip() else None
    
                            # 18. lastchanged (timestamp NULL) - CSV index 18
                            lastchanged_str = row[18].strip()
                            lastchanged = datetime.strptime(lastchanged_str, "%Y/%m/%d %H:%M:%S") if lastchanged_str else None
    
                            # Append data in the order of the database table columns
                            rows_to_insert.append((
                                run_datetime, intervention, interconnectorid, interval_datetime,
                                meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree,
                                mnsp, exportlimit, importlimit, marginalloss, exportgenconid,
                                importgenconid, fcasexportlimit, fcasimportlimit, lastchanged
                            ))
    
                        except (ValueError, IndexError) as e:
                            print_and_log(f"Skipping row due to data parsing error in {csv_path}: {row}. Error: {e}")
                            continue # Skip to the next row if there's an error
    
                    
                    # Batch insert if there are any rows
                    if rows_to_insert:
                        # Construct the INSERT statement with all column names in the correct order
                        insert_query = sql.SQL("""
                            INSERT INTO nem_interconnect_5m (
                                run_datetime, intervention, interconnectorid, interval_datetime,
                                meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree,
                                mnsp, exportlimit, importlimit, marginalloss, exportgenconid,
                                importgenconid, fcasexportlimit, fcasimportlimit, lastchanged
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                            )
                            ON CONFLICT (interconnectorid, interval_datetime) DO NOTHING
                        """)
                        cur.executemany(insert_query, rows_to_insert)
                        print_and_log(f"Inserted/skipped {len(rows_to_insert)} rows from {csv_path}")
                    else:
                        print_and_log(f"No valid data rows found in {csv_path} to insert.")
    
                conn.commit()
        except Exception as e:
            conn.rollback()
            print_and_log(f"Error processing CSV {csv_path}: {e}", e)
        finally:
            if conn: # Ensure connection is closed even if it's None initially
                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/"
                # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
                raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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)
    
  3. User

    The rows don't line up correctly. Here are the first part of the csv to assist: I,P5MIN,INTERCONNECTORSOLN,4,RUN_DATETIME,INTERVENTION,INTERCONNECTORID,INTERVAL_DATETIME,METEREDMWFLOW,MWFLOW,MWLOSSES,MARGINALVALUE,VIOLATIONDEGREE,MNSP,EXPORTLIMIT,IMPORTLIMIT,MARGINALLOSS,EXPORTGENCONID,IMPORTGENCONID,FCASEXPORTLIMIT,FCASIMPORTLIMIT,LASTCHANGED D,P5MIN,INTERCONNECTORSOLN,4,"2024/04/01 00:05:00",0,N-Q-MNSP1,"2024/04/01 00:05:00",-8.8,-9,-0.36063,0,0,0,35.6,-88.8,1.04072,N>N_LSDU_9U6_1,QNTE_ROC,35.6,-88.8,"2024/04/01 00:00:25"

  4. Gemini Assistant

    You are absolutely correct! My apologies for the previous oversight. AEMO CSV file structures can vary significantly between datasets, and the header you provided clarifies the exact column order for PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN files.

    The key points from your provided header are:

    • The "I" record shows the actual column names and their order.
    • The "D" record shows the data corresponding to those columns.
    • RUN_DATETIME is at index 4, INTERVENTION at 5, INTERCONNECTORID at 6, INTERVAL_DATETIME at 7, and METEREDMWFLOW at 8, etc.
    • FCASIMPORTLIMIT and FCASEXPORTLIMIT are indeed swapped in their original position compared to the table definition, but are consistently listed in the header (FCASEXPORTLIMIT then FCASIMPORTLIMIT). We need to map them to the correct table columns.

    Let's update the process_csv function with the correct column mapping and error handling based on the provided CSV structure.

    #!/usr/bin/env python3
    # File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
    # URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/
    
    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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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)
                # Find the actual CSV file name within the zip, as it's not always just zip_path.stem + ".CSV"
                # It's usually the same as the zip stem, but can have different case or extensions inside.
                csv_files_in_zip = [f for f in z.namelist() if f.lower().endswith('.csv')]
                
                if csv_files_in_zip:
                    # Assuming the first CSV file found is the one we want
                    csv_file_name_in_zip = csv_files_in_zip[0]
                    csv_path = extract_to / csv_file_name_in_zip
                else:
                    print_and_log(f"No CSV file found in {zip_path}")
                    return None
    
                print_and_log(f"Unzipped: {csv_path.name}")
                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)
                    
                    # AEMO CSV files typically have an "I" record (info) and then a "D" record (header).
                    # Skip both to get to the actual data rows.
                    try:
                        next(csv_reader) # Skip "I" record (info/file metadata)
                        next(csv_reader) # Skip "D" record (data header)
                    except StopIteration:
                        print_and_log(f"CSV file {csv_path} is too short or malformed (missing header/control records).")
                        return
                    
                    rows_to_insert = []
                    for row in csv_reader:
                        # Data rows start with 'D'.
                        # Based on the provided header, LASTCHANGED is at index 21.
                        # So, a valid data row should have at least 22 columns (indices 0 to 21).
                        if not row or row[0].strip() != "D" or len(row) < 22:
                            print_and_log(f"Skipping malformed or non-data row in {csv_path}: {row}")
                            continue
    
                        try:
                            # Extract and convert data based on the provided CSV structure and map to DB columns.
                            # CSV Column Mapping (0-indexed) from header:
                            # 0: RECORD_TYPE (D)
                            # 1: P5MIN
                            # 2: INTERCONNECTORSOLN
                            # 3: VERSION
                            # 4: RUN_DATETIME
                            # 5: INTERVENTION
                            # 6: INTERCONNECTORID
                            # 7: INTERVAL_DATETIME
                            # 8: METEREDMWFLOW
                            # 9: MWFLOW
                            # 10: MWLOSSES
                            # 11: MARGINALVALUE
                            # 12: VIOLATIONDEGREE
                            # 13: MNSP
                            # 14: EXPORTLIMIT
                            # 15: IMPORTLIMIT
                            # 16: MARGINALLOSS
                            # 17: EXPORTGENCONID
                            # 18: IMPORTGENCONID
                            # 19: FCASEXPORTLIMIT
                            # 20: FCASIMPORTLIMIT
                            # 21: LASTCHANGED
    
                            # DB Column Order:
                            # run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow,
                            # mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit,
                            # importlimit, marginalloss, exportgenconid, importgenconid,
                            # fcasexportlimit, fcasimportlimit, lastchanged
    
                            # Helper function to convert to numeric, handling empty strings as None
                            def to_float_or_none(value_str):
                                return float(value_str.strip()) if value_str.strip() else None
    
                            # Helper function to convert to int, handling empty strings as None
                            def to_int_or_none(value_str):
                                return int(value_str.strip()) if value_str.strip() else None
    
                            # Helper function to convert to timestamp, handling empty strings as None
                            def to_datetime_or_none(value_str):
                                return datetime.strptime(value_str.strip(), "%Y/%m/%d %H:%M:%S") if value_str.strip() else None
    
                            # 1. run_datetime (timestamp NULL) - CSV index 4
                            run_datetime = to_datetime_or_none(row[4])
    
                            # 2. intervention (integer NULL) - CSV index 5
                            intervention = to_int_or_none(row[5])
    
                            # 3. interconnectorid (character varying(20)) - CSV index 6. PK, must not be NULL
                            interconnectorid = row[6].strip()
                            if not interconnectorid: # Ensure PK is not empty
                                 raise ValueError(f"interconnectorid cannot be empty for row: {row}")
    
                            # 4. interval_datetime (timestamp) - CSV index 7. PK, must not be NULL
                            interval_datetime = to_datetime_or_none(row[7])
                            if not interval_datetime: # Ensure PK is not empty
                                raise ValueError(f"interval_datetime cannot be empty for row: {row}")
    
                            # 5. meteredmwflow (numeric(12,5) NULL) - CSV index 8
                            meteredmwflow = to_float_or_none(row[8])
    
                            # 6. mwflow (numeric(12,5) NULL) - CSV index 9
                            mwflow = to_float_or_none(row[9])
    
                            # 7. mwlosses (numeric(12,5) NULL) - CSV index 10
                            mwlosses = to_float_or_none(row[10])
    
                            # 8. marginalvalue (numeric(12,5) NULL) - CSV index 11
                            marginalvalue = to_float_or_none(row[11])
    
                            # 9. violationdegree (numeric(12,5) NULL) - CSV index 12
                            violationdegree = to_float_or_none(row[12])
    
                            # 10. mnsp (integer NULL) - CSV index 13
                            mnsp = to_int_or_none(row[13])
    
                            # 11. exportlimit (numeric(12,5) NULL) - CSV index 14
                            exportlimit = to_float_or_none(row[14])
    
                            # 12. importlimit (numeric(12,5) NULL) - CSV index 15
                            importlimit = to_float_or_none(row[15])
    
                            # 13. marginalloss (numeric(12,5) NULL) - CSV index 16
                            marginalloss = to_float_or_none(row[16])
    
                            # 14. exportgenconid (character varying(40) NULL) - CSV index 17
                            exportgenconid = row[17].strip() if row[17].strip() else None
    
                            # 15. importgenconid (character varying(40) NULL) - CSV index 18
                            importgenconid = row[18].strip() if row[18].strip() else None
    
                            # 16. fcasexportlimit (numeric(12,5) NULL) - CSV index 19
                            fcasexportlimit = to_float_or_none(row[19])
    
                            # 17. fcasimportlimit (numeric(12,5) NULL) - CSV index 20
                            fcasimportlimit = to_float_or_none(row[20])
    
                            # 18. lastchanged (timestamp NULL) - CSV index 21
                            lastchanged = to_datetime_or_none(row[21])
    
                            # Append data tuple in the exact order of the database table columns
                            rows_to_insert.append((
                                run_datetime, intervention, interconnectorid, interval_datetime,
                                meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree,
                                mnsp, exportlimit, importlimit, marginalloss, exportgenconid,
                                importgenconid, fcasexportlimit, fcasimportlimit, lastchanged
                            ))
    
                        except (ValueError, IndexError) as e:
                            # Log the specific row causing the error for debugging
                            print_and_log(f"Skipping row due to data parsing error in {csv_path}: {row}. Error: {e}")
                            continue # Skip to the next row if there's an error
    
                    
                    # Batch insert if there are any rows
                    if rows_to_insert:
                        # Construct the INSERT statement with all column names in the correct order
                        insert_query = sql.SQL("""
                            INSERT INTO nem_interconnect_5m (
                                run_datetime, intervention, interconnectorid, interval_datetime,
                                meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree,
                                mnsp, exportlimit, importlimit, marginalloss, exportgenconid,
                                importgenconid, fcasexportlimit, fcasimportlimit, lastchanged
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                            )
                            ON CONFLICT (interconnectorid, interval_datetime) DO NOTHING
                        """)
                        cur.executemany(insert_query, rows_to_insert)
                        print_and_log(f"Inserted/skipped {len(rows_to_insert)} rows from {csv_path}")
                    else:
                        print_and_log(f"No valid data rows found in {csv_path} to insert.")
    
                conn.commit()
        except Exception as e:
            conn.rollback()
            print_and_log(f"Error processing CSV {csv_path}: {e}", e)
        finally:
            if conn:
                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/"
                # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
                raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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 interconnectors 5-minute data.")
        parser.add_argument("start_year_month", type=str, help="Start year and month in YYYYMM format (e.g., 202301)")
        parser.add_argument("end_year_month", type=str, help="End year and month in YYYYMM format (e.g., 202312)")
        
        args = parser.parse_args()
        main(args.start_year_month, args.end_year_month)
    

    Key changes made:

    1. Correct CSV Index Mapping: All column extractions within the try block in process_csv have been adjusted to match the 0-based indices provided by your CSV header (e.g., RUN_DATETIME from row[4], INTERCONNECTORID from row[6], etc.).
    2. Robust Type Conversion: Added helper functions to_float_or_none, to_int_or_none, and to_datetime_or_none to handle empty strings ('') in the CSV as None for database NULL values, and to handle the specific date/time format.
    3. Primary Key Validation: Added explicit checks for interconnectorid and interval_datetime to ensure they are not empty, as they are part of the primary key and cannot be NULL. If they are empty, a ValueError is raised to skip the row.
    4. Minimum Row Length Check: The if len(row) < 22 or row[0].strip() != "D": condition now correctly checks for the expected number of columns (at least 22, as LASTCHANGED is at index 21) and that the row is a data record.
    5. CSV Header Skipping: Confirmed that next(csv_reader) called twice is correct to skip both the "I" (Info) and "D" (Data Header) records.
    6. unzip_file Improvement: Made unzip_file slightly more robust by listing the contents of the zip file and picking the first CSV found, in case the filename casing differs from zip_path.stem + ".CSV".
    7. Argument Parser Description: Clarified the help messages for argparse.

    This updated script should now correctly parse and insert the data from your AEMO Interconnector Solution CSV files.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.