code4.py

import pdfplumber
import pandas as pd
import re
from typing import List, Dict, Tuple, Any

class PDFSectionExtractor:
    def __init__(self, pdf_path: str):
        """Initialize with path to PDF file."""
        self.pdf_path = pdf_path
        self.tables = []
        self.table_names = {}  # Store table names and their content
        
    def is_level_three_section(self, section_number: str) -> bool:
        """Check if the section number is a level three section (e.g., 2.1.1)."""
        return len(section_number.split('.')) == 3

    def find_table_names(self, text: str) -> List[Dict[str, str]]:
        """Extract table names from text."""
        table_pattern = r'\*\*Table\s+(\d+):\s+([^*]+)\*\*'
        return [(match.group(1), match.group(2).strip()) 
                for match in re.finditer(table_pattern, text)]

    def clean_content(self, content: str) -> str:
        """Remove table references and names from content."""
        # Remove table references
        content = re.sub(r'\*\*Table\s+\d+:\s+[^*]+\*\*', '', content)
        # Remove any empty lines created
        content = '\n'.join(line for line in content.split('\n') if line.strip())
        return content
        
    def merge_split_tables(self, tables: List[List]) -> List[List]:
        """Merge tables that are split across pages."""
        merged_tables = []
        current_table = None
        
        for table in tables:
            if not table:
                continue
                
            if current_table is None:
                current_table = table
            else:
                # Check if this table is a continuation
                # Compare the number of columns
                if len(table[0]) == len(current_table[0]):
                    current_table.extend(table)
                else:
                    merged_tables.append(current_table)
                    current_table = table
        
        if current_table:
            merged_tables.append(current_table)
            
        return merged_tables

    def extract_tables(self) -> List[Dict[str, Any]]:
        """Extract tables with their names and merge split tables."""
        tables_data = []
        current_section = None
        current_table_data = None
        current_table_name = None
        
        with pdfplumber.open(self.pdf_path) as pdf:
            for page in pdf.pages:
                text = page.extract_text(x_tolerance=1,y_tolerance=0) or ''
                
                # Find table names in the text
                table_names = self.find_table_names(text)
                tables = page.extract_tables()
                
                # Process each table found
                if tables:
                    tables = self.merge_split_tables(tables)
                    
                    for i, table in enumerate(tables):
                        table_name = None
                        if i < len(table_names):
                            table_num, name = table_names[i]
                            table_name = f"Table {table_num}: {name}"
                        
                        if table:  # Check if table has content
                            df = pd.DataFrame(table)
                            # Clean the DataFrame
                            df = df.dropna(how='all').dropna(axis=1, how='all')
                            # Replace None with empty string
                            df = df.fillna('')
                            
                            tables_data.append({
                                'name': table_name,
                                'data': df
                            })
        
        return tables_data

    def extract_sections(self) -> List[Dict[str, str]]:
        """Extract sections from PDF with content, excluding tables."""
        sections = []
        current_section = None
        current_content = []
        section_pattern = r'^(\d+\.(?:\d+)?(?:\.\d+)?)\s+(.+)$'
        
        with pdfplumber.open(self.pdf_path) as pdf:
            for page in pdf.pages:
                text = page.extract_text(x_tolerance=1)
                if not text:
                    continue
                    
                lines = text.split('\n')
                
                for line in lines:
                    match = re.match(section_pattern, line.strip())
                    
                    if match:
                        if current_section:
                            content_text = '\n'.join(current_content)
                            content_text = self.clean_content(content_text)
                            
                            if self.is_level_three_section(current_section[0]):
                                full_content = current_section[1] + '\n' + content_text
                                sections.append({
                                    'section_number': current_section[0],
                                    'section_name': '',
                                    'content': full_content
                                })
                            else:
                                sections.append({
                                    'section_number': current_section[0],
                                    'section_name': current_section[1],
                                    'content': content_text
                                })
                        
                        current_section = (match.group(1), match.group(2))
                        current_content = []
                    elif current_section:
                        current_content.append(line.strip())
        
        # Handle the last section
        if current_section:
            content_text = '\n'.join(current_content)
            content_text = self.clean_content(content_text)
            
            if self.is_level_three_section(current_section[0]):
                full_content = current_section[1] + '\n' + content_text
                sections.append({
                    'section_number': current_section[0],
                    'section_name': '',
                    'content': full_content
                })
            else:
                sections.append({
                    'section_number': current_section[0],
                    'section_name': current_section[1],
                    'content': content_text
                })
                
        return sections

def convert_pdf_to_excel(pdf_path: str, excel_path: str):
    """Convert PDF with sections and tables to Excel file."""
    try:
        extractor = PDFSectionExtractor(pdf_path)
        sections = extractor.extract_sections()
        tables = extractor.extract_tables()
        
        with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
            # Write sections to main sheet
            df_sections = pd.DataFrame(sections)
            df_sections.to_excel(writer, index=False, sheet_name='Sections')
            
            # Auto-adjust sections sheet
            worksheet = writer.sheets['Sections']
            for idx, col in enumerate(['A', 'B', 'C']):
                worksheet.column_dimensions[col].width = 15 if idx < 2 else 50
            
            # Write tables to separate sheets
            for i, table_info in enumerate(tables, 1):
                if table_info['name']:
                    sheet_name = table_info['name'][:31]  # Excel sheet name length limit
                else:
                    sheet_name = f'Table_{i}'
                
                # Write table data
                table_info['data'].to_excel(writer, sheet_name=sheet_name, index=False)
                
                # Auto-adjust table sheet
                worksheet = writer.sheets[sheet_name]
                for column in worksheet.columns:
                    max_length = 0
                    column = [cell for cell in column]
                    for cell in column:
                        try:
                            if len(str(cell.value)) > max_length:
                                max_length = len(cell.value)
                        except:
                            pass
                    adjusted_width = (max_length + 2)
                    worksheet.column_dimensions[column[0].column_letter].width = adjusted_width
        
        return True
    
    except Exception as e:
        print(f"Error converting PDF to Excel: {str(e)}")
        return False

if __name__ == "__main__":
    pdf_path = "/Users/aakanksha.n/Desktop/pdf_to_excel/[00 12 10] 26251-100-3DR-S04-00001_002.docx.pdf"
    excel_path = "/Users/aakanksha.n/Desktop/pdf_to_excel/format4.xlsx"
    
    success = convert_pdf_to_excel(pdf_path, excel_path)
    if success:
        print("Successfully converted PDF to Excel!")
    else:
        print("Failed to convert PDF to Excel.")