Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

import pandas as pd from docx import Document import os from datetime import datetime…

  1. User

    import pandas as pd from docx import Document import os from datetime import datetime

    def auto_populate_chambers_form(excel_file, template_file, output_file): """ Auto-populate Chambers submission form (Word document) with data from Excel file

    Parameters:
    excel_file (str): Path to the Excel file containing matter data
    template_file (str): Path to the Word document template (Chambers.docx)
    output_file (str): Path where the populated document will be saved
    """
    print(f"Reading data from Excel file: {excel_file}")
    
    # Read Excel data and strip whitespace from column names
    df = pd.read_excel(excel_file)
    df.columns = df.columns.str.strip()  # Strip whitespace from column names
    
    # Check columns after stripping
    print("Columns in DataFrame:", df.columns.tolist())  # Debugging line
    
    # Check if the required column is present
    if 'Confidential / Publishable' not in df.columns:
        print("Column 'Confidential / Publishable' not found in DataFrame.")
        return  # or handle this issue as suitable
    
    # Count how many publishable and confidential matters we have
    publishable_matters = df[df['Confidential / Publishable'].str.lower() == 'publishable']
    confidential_matters = df[df['Confidential / Publishable'].str.lower() == 'confidential']
    
    print(f"Found {len(publishable_matters)} publishable matters and {len(confidential_matters)} confidential matters")
    
    # Continue with the rest of your code...
    
    # Populate publishable matters (Section D)
    for i, matter in publishable_matters.iterrows():
        if i >= 10:  # Max 10 publishable matters allowed
            print("Warning: More than 10 publishable matters found in Excel. Only the first 10 will be used.")
            break
            
        matter_num = i + 1
        section_name = f"Publishable Matter {matter_num}"
        
        # Find the section in the document
        found_section = False
        for para in doc.paragraphs:
            if section_name in para.text:
                found_section = True
                break
        
        if not found_section:
            print(f"Warning: Could not find section '{section_name}' in the template document.")
            continue
        
        # Populate fields for this matter
        populate_matter_fields(
            doc, 
            section_name, 
            "D", 
            matter['Client Name'],
            matter['Matter/Case Overview'],
            matter['Value'],
            matter.get('Cross-border', 'N/A'),
            matter['Lead partners'],
            matter['Team Members'],
            matter.get('Other Firms/counsels Involved', ''),
            matter.get('Current Status', ''),
            matter.get('Additional Info', '')
        )
    
    # Populate confidential matters (Section E)
    for i, matter in confidential_matters.iterrows():
        if i >= 10:  # Max 10 confidential matters allowed
            print("Warning: More than 10 confidential matters found in Excel. Only the first 10 will be used.")
            break
            
        matter_num = i + 1
        section_name = f"Confidential Matter {matter_num}"
        
        # Find the section in the document
        found_section = False
        for para in doc.paragraphs:
            if section_name in para.text:
                found_section = True
                break
        
        if not found_section:
            print(f"Warning: Could not find section '{section_name}' in the template document.")
            continue
        
        # Populate fields for this matter
        populate_matter_fields(
            doc, 
            section_name, 
            "E", 
            matter['Client Name'],
            matter['Matter/Case Overview'],
            matter['Value'],
            matter.get('Cross-border', 'N/A'),
            matter['Lead partners'],
            matter['Team Members'],
            matter.get('Other Firms/counsels Involved', ''),
            matter.get('Current Status', ''),
            matter.get('Additional Info', '')
        )
    
    # Populate client lists
    populate_client_lists(doc, publishable_matters, confidential_matters)
    
    # Save the populated document
    doc.save(output_file)
    print(f"Document successfully populated and saved as: {output_file}")
    

    def populate_matter_fields(doc, section_name, section_prefix, client_name, matter_overview, value, cross_border, lead_partner, team_members, other_firms, status, additional_info): """ Populate the fields for a specific matter in the document """ # Combine matter overview with role, significance, etc. from Excel # (Adjust as needed based on your Excel structure)

    # Find and replace fields
    for i, para in enumerate(doc.paragraphs):
        # Client name field
        if f"{section_prefix}1 Name of client" in para.text:
            # Find the paragraph after this one and set its text
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = client_name
        
        # Matter summary field
        elif f"{section_prefix}2 Summary of matter" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = matter_overview
        
        # Matter value field
        elif f"{section_prefix}3 Matter value" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = str(value)
        
        # Cross-border field
        elif f"{section_prefix}4 Is this a cross-border matter" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = cross_border
        
        # Lead partner field
        elif f"{section_prefix}5 Lead partner" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = lead_partner
        
        # Team members field
        elif f"{section_prefix}6 Other team members" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = team_members
        
        # Other firms field
        elif f"{section_prefix}7 Other firms advising" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = other_firms
        
        # Status field
        elif f"{section_prefix}8 Date of completion" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = status
        
        # Additional info field
        elif f"{section_prefix}9 Other information" in para.text:
            if i+1 < len(doc.paragraphs):
                doc.paragraphs[i+1].text = additional_info
    

    def populate_client_lists(doc, publishable_matters, confidential_matters): """ Populate the publishable and confidential client lists """ # Find the client list tables tables = doc.tables publishable_clients_table = None confidential_clients_table = None

    # Locate the tables by checking their content
    for table in tables:
        for row in table.rows:
            for cell in row.cells:
                if "D -- [PUBLISHABLE] CLIENTS" in cell.text:
                    publishable_clients_table = table
                elif "E0 -- [CONFIDENTIAL] CLIENTS" in cell.text:
                    confidential_clients_table = table
    
    # Populate publishable clients table
    if publishable_clients_table:
        for i, matter in publishable_matters.iterrows():
            if i >= len(publishable_clients_table.rows) - 1:  # -1 for header row
                break
                
            # Row index (skipping header row)
            row_idx = i + 1
            
            # Set client name
            if len(publishable_clients_table.rows[row_idx].cells) >= 2:
                publishable_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                
                # Set 'New Client' field if available in Excel
                if 'New Client' in matter and len(publishable_clients_table.rows[row_idx].cells) >= 3:
                    publishable_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')
    
    # Populate confidential clients table
    if confidential_clients_table:
        for i, matter in confidential_matters.iterrows():
            if i >= len(confidential_clients_table.rows) - 1:  # -1 for header row
                break
                
            # Row index (skipping header row)
            row_idx = i + 1
            
            # Set client name
            if len(confidential_clients_table.rows[row_idx].cells) >= 2:
                confidential_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                
                # Set 'New Client' field if available in Excel
                if 'New Client' in matter and len(confidential_clients_table.rows[row_idx].cells) >= 3:
                    confidential_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')
    

    if name == "main": # File paths - adjust these to your actual file locations excel_file = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" # Your Excel file with matter data template_file = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx"# Original Chambers form template

    # Create output file with timestamp
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_file = f"Chambers_Populated_{timestamp}.docx"
    
    # Run the auto-population
    auto_populate_chambers_form(excel_file, template_file, output_file)        this code and this error PS C:\Users\Administrator\Desktop\Bd team> python app.py
    

    Reading data from Excel file: C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx Columns in DataFrame: ['S No.', 'Client Name', 'Matter/Case Overview', 'Value', 'Description (in 500 words)', 'Role of S&A', 'The significance, impact and outcome of the case', 'Current Status (ongoing / completed )', 'Lead partners', 'Team Members', 'Other Firms/counsels Involved', 'Confidential / Publishable'] Found 0 publishable matters and 0 confidential matters Traceback (most recent call last): File "C:\Users\Administrator\Desktop\Bd team\app.py", line 239, in auto_populate_chambers_form(excel_file, template_file, output_file)
    File "C:\Users\Administrator\Desktop\Bd team\app.py", line 110, in auto_populate_chambers_form populate_client_lists(doc, publishable_matters, confidential_matters)
    ^^^ NameError: name 'doc' is not defined PS C:\Users\Administrator\Desktop\Bd team> the confidentila matter as yes in last column while oublishable have no in last column

  2. Assistant

    The error occurred because the variable doc has not been defined in your auto_populate_chambers_form function before you attempt to use it in the populate_client_lists function call.

    Furthermore, the output shows that you found 0 publishable and confidential matters, which means that your filters for identifying these matters may not be working as expected. Let's fix both of these issues in your code.

    Here’s the updated version of your code:

    1. Add Document Loading: Load the document in the auto_populate_chambers_form function.
    2. Correct Filtering: Ensure proper filtering based on the 'Confidential / Publishable' column.

    Here is the updated code:

    import pandas as pd
    from docx import Document
    import os
    from datetime import datetime
    
    def auto_populate_chambers_form(excel_file, template_file, output_file):
        """
        Auto-populate Chambers submission form (Word document) with data from Excel file
        
        Parameters:
        excel_file (str): Path to the Excel file containing matter data
        template_file (str): Path to the Word document template (Chambers.docx)
        output_file (str): Path where the populated document will be saved
        """
        print(f"Reading data from Excel file: {excel_file}")
        
        # Read Excel data and strip whitespace from column names
        df = pd.read_excel(excel_file)
        df.columns = df.columns.str.strip()  # Strip whitespace from column names
        
        # Check columns after stripping
        print("Columns in DataFrame:", df.columns.tolist())  # Debugging line
        
        # Check if the required column is present
        if 'Confidential / Publishable' not in df.columns:
            print("Column 'Confidential / Publishable' not found in DataFrame.")
            return  # or handle this issue as suitable
    
        # Count how many publishable and confidential matters we have
        publishable_matters = df[df['Confidential / Publishable'].str.lower() == 'no']  # Changed to 'no'
        confidential_matters = df[df['Confidential / Publishable'].str.lower() == 'yes']  # Changed to 'yes'
    
        print(f"Found {len(publishable_matters)} publishable matters and {len(confidential_matters)} confidential matters")
    
        # Load the Word document template
        doc = Document(template_file)
        
        # Continue with the rest of your code...
        
        # Populate publishable matters (Section D)
        for i, matter in publishable_matters.iterrows():
            if i >= 10:  # Max 10 publishable matters allowed
                print("Warning: More than 10 publishable matters found in Excel. Only the first 10 will be used.")
                break
                
            matter_num = i + 1
            section_name = f"Publishable Matter {matter_num}"
            
            # Find the section in the document
            found_section = False
            for para in doc.paragraphs:
                if section_name in para.text:
                    found_section = True
                    break
            
            if not found_section:
                print(f"Warning: Could not find section '{section_name}' in the template document.")
                continue
            
            # Populate fields for this matter
            populate_matter_fields(
                doc, 
                section_name, 
                "D", 
                matter['Client Name'],
                matter['Matter/Case Overview'],
                matter['Value'],
                matter.get('Cross-border', 'N/A'),
                matter['Lead partners'],
                matter['Team Members'],
                matter.get('Other Firms/counsels Involved', ''),
                matter.get('Current Status', ''),
                matter.get('Additional Info', '')
            )
        
        # Populate confidential matters (Section E)
        for i, matter in confidential_matters.iterrows():
            if i >= 10:  # Max 10 confidential matters allowed
                print("Warning: More than 10 confidential matters found in Excel. Only the first 10 will be used.")
                break
                
            matter_num = i + 1
            section_name = f"Confidential Matter {matter_num}"
            
            # Find the section in the document
            found_section = False
            for para in doc.paragraphs:
                if section_name in para.text:
                    found_section = True
                    break
            
            if not found_section:
                print(f"Warning: Could not find section '{section_name}' in the template document.")
                continue
            
            # Populate fields for this matter
            populate_matter_fields(
                doc, 
                section_name, 
                "E", 
                matter['Client Name'],
                matter['Matter/Case Overview'],
                matter['Value'],
                matter.get('Cross-border', 'N/A'),
                matter['Lead partners'],
                matter['Team Members'],
                matter.get('Other Firms/counsels Involved', ''),
                matter.get('Current Status', ''),
                matter.get('Additional Info', '')
            )
        
        # Populate client lists
        populate_client_lists(doc, publishable_matters, confidential_matters)
        
        # Save the populated document
        doc.save(output_file)
        print(f"Document successfully populated and saved as: {output_file}")
    
    
    def populate_matter_fields(doc, section_name, section_prefix, 
                              client_name, matter_overview, value, cross_border,
                              lead_partner, team_members, other_firms,
                              status, additional_info):
        """
        Populate the fields for a specific matter in the document
        """
        # Find and replace fields
        for i, para in enumerate(doc.paragraphs):
            # Client name field
            if f"{section_prefix}1 Name of client" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = client_name
            
            # Matter summary field
            elif f"{section_prefix}2 Summary of matter" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = matter_overview
            
            # Matter value field
            elif f"{section_prefix}3 Matter value" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = str(value)
            
            # Cross-border field
            elif f"{section_prefix}4 Is this a cross-border matter" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = cross_border
            
            # Lead partner field
            elif f"{section_prefix}5 Lead partner" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = lead_partner
            
            # Team members field
            elif f"{section_prefix}6 Other team members" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = team_members
            
            # Other firms field
            elif f"{section_prefix}7 Other firms advising" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = other_firms
            
            # Status field
            elif f"{section_prefix}8 Date of completion" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = status
            
            # Additional info field
            elif f"{section_prefix}9 Other information" in para.text:
                if i+1 < len(doc.paragraphs):
                    doc.paragraphs[i+1].text = additional_info
    
    
    def populate_client_lists(doc, publishable_matters, confidential_matters):
        """
        Populate the publishable and confidential client lists
        """
        # Find the client list tables
        tables = doc.tables
        publishable_clients_table = None
        confidential_clients_table = None
        
        # Locate the tables by checking their content
        for table in tables:
            for row in table.rows:
                for cell in row.cells:
                    if "D -- [PUBLISHABLE] CLIENTS" in cell.text:
                        publishable_clients_table = table
                    elif "E0 -- [CONFIDENTIAL] CLIENTS" in cell.text:
                        confidential_clients_table = table
        
        # Populate publishable clients table
        if publishable_clients_table:
            for i, matter in publishable_matters.iterrows():
                if i >= len(publishable_clients_table.rows) - 1:  # -1 for header row
                    break
                    
                # Row index (skipping header row)
                row_idx = i + 1
                
                # Set client name
                if len(publishable_clients_table.rows[row_idx].cells) >= 2:
                    publishable_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                    
                    # Set 'New Client' field if available in Excel
                    if 'New Client' in matter and len(publishable_clients_table.rows[row_idx].cells) >= 3:
                        publishable_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')
        
        # Populate confidential clients table
        if confidential_clients_table:
            for i, matter in confidential_matters.iterrows():
                if i >= len(confidential_clients_table.rows) - 1:  # -1 for header row
                    break
                    
                # Row index (skipping header row)
                row_idx = i + 1
                
                # Set client name
                if len(confidential_clients_table.rows[row_idx].cells) >= 2:
                    confidential_clients_table.rows[row_idx].cells[1].text = matter['Client Name']
                    
                    # Set 'New Client' field if available in Excel
                    if 'New Client' in matter and len(confidential_clients_table.rows[row_idx].cells) >= 3:
                        confidential_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')
    
    
    if __name__ == "__main__":
        # File paths - adjust these to your actual file locations
        excel_file = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx"  # Your Excel file with matter data
        template_file = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx"  # Original Chambers form template
        
        # Create output file with timestamp
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        output_file = f"Chambers_Populated_{timestamp}.docx"
        
        # Run the auto-population
        auto_populate_chambers_form(excel_file, template_file, output_file)
    

    Key Changes:

    1. Loaded the Word Document: The Document is initialized using the template file within the auto_populate_chambers_form function.
    2. Filtering: The filtering of publishable and confidential matters is correctly implemented based on the 'Yes' and 'No' values in the 'Confidential / Publishable' column.

    These changes should resolve the issues you encountered and improve the functionality. Be sure to confirm the actual column names from your Excel sheet are matched correctly in your code.