convert_numbers_to_json.py

#!/usr/bin/env python3
"""
Script to convert customers.numbers file to JSON format using numbers-parser library
"""

import json
from pathlib import Path
from numbers_parser import Document

def convert_numbers_to_json():
    """Convert Numbers file to JSON"""
    # Paths
    script_dir = Path(__file__).parent
    numbers_file = script_dir / 'src' / 'data' / 'json' / 'customers.numbers'
    output_json = script_dir / 'src' / 'data' / 'json' / 'customers_new.json'

    print(f"Converting {numbers_file} to JSON...")
    print(f"Output will be: {output_json}")

    # Check if file exists
    if not numbers_file.exists():
        print(f"Error: {numbers_file} not found!")
        return

    try:
        # Open the Numbers document
        print("\nOpening Numbers file...")
        doc = Document(str(numbers_file))

        # Get the first sheet
        sheets = doc.sheets
        print(f"Found {len(sheets)} sheet(s)")

        if len(sheets) == 0:
            print("Error: No sheets found in the Numbers file")
            return

        # Get the first table from the first sheet
        sheet = sheets[0]
        print(f"Using sheet: {sheet.name}")

        tables = sheet.tables
        print(f"Found {len(tables)} table(s)")

        if len(tables) == 0:
            print("Error: No tables found in the sheet")
            return

        table = tables[0]
        print(f"Using table: {table.name}")
        print(f"Table has {table.num_rows} rows and {table.num_cols} columns")

        # Get headers from first row
        headers = []
        for col_idx in range(table.num_cols):
            cell = table.cell(0, col_idx)
            header = str(cell.value) if cell.value is not None else ""
            headers.append(header.strip())

        print(f"\nHeaders: {headers}")

        # Parse data rows (starting from row 1 to skip header)
        customers = []
        for row_idx in range(1, table.num_rows):
            # Check if row has any data
            row_values = []
            for col_idx in range(table.num_cols):
                cell = table.cell(row_idx, col_idx)
                row_values.append(cell.value)

            has_data = any(val is not None and str(val).strip() for val in row_values)
            if not has_data:
                continue

            # Build customer object
            customer = {}
            for col_idx in range(table.num_cols):
                if col_idx < len(headers):
                    header = headers[col_idx]
                    cell = table.cell(row_idx, col_idx)
                    value = cell.value

                    # Clean up the value
                    if value is not None:
                        # Convert to string and strip whitespace
                        value = str(value).strip()
                        # Convert empty strings to None
                        if value == '':
                            value = None

                    customer[header] = value

            # Only add if customer has an id (check both 'id' and '_id')
            if customer.get('id') or customer.get('_id'):
                customers.append(customer)

        print(f"\nExtracted {len(customers)} customers")

        # Preview first customer
        if customers:
            print("\nFirst customer:")
            print(json.dumps(customers[0], indent=2))
        else:
            print("\nNo customers extracted. Showing first few rows of data:")
            for row_idx in range(1, min(4, table.num_rows)):
                print(f"\nRow {row_idx}:")
                for col_idx in range(min(5, table.num_cols)):
                    cell = table.cell(row_idx, col_idx)
                    print(f"  {headers[col_idx]}: {cell.value}")

        # Save to JSON
        print(f"\nSaving to {output_json}...")
        with open(output_json, 'w', encoding='utf-8') as f:
            json.dump(customers, f, indent=2, ensure_ascii=False)

        print(f"✓ Successfully saved {len(customers)} customers to {output_json}")

        # Show statistics
        business_count = sum(1 for c in customers if c.get('type') == 'business')
        individual_count = sum(1 for c in customers if c.get('type') == 'individual')
        print(f"\nStatistics:")
        print(f"  Business customers: {business_count}")
        print(f"  Individual customers: {individual_count}")
        print(f"  Total: {len(customers)}")

    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

if __name__ == '__main__':
    convert_numbers_to_json()