xls2csv.py

#!/usr/bin/env python3
"""
Convert all worksheets in an XLS file to CSV files.

Usage:
    python xls2csv.py <input.xls> [output_dir]

Each worksheet will be saved as a separate CSV file named:
    <input_file>_<worksheet_name>.csv
"""

import sys
import csv
import os
import xlrd


def xls_to_csv(input_file, output_dir=None):
    """Convert all worksheets in an XLS file to CSV files."""
    if not os.path.exists(input_file):
        print(f"Error: File '{input_file}' not found.", file=sys.stderr)
        sys.exit(1)

    if output_dir:
        os.makedirs(output_dir, exist_ok=True)
    else:
        output_dir = os.path.dirname(input_file) or "."

    try:
        workbook = xlrd.open_workbook(input_file)
    except Exception as e:
        print(f"Error opening workbook: {e}", file=sys.stderr)
        sys.exit(1)

    base_name = os.path.splitext(os.path.basename(input_file))[0]

    for sheet_idx in range(workbook.nsheets):
        sheet = workbook.sheet_by_index(sheet_idx)
        sheet_name = sheet.name

        # Create safe filename
        safe_name = "".join(
            c if c.isalnum() or c in ("-", "_") else "_" for c in sheet_name
        )
        output_file = os.path.join(output_dir, f"{base_name}_{safe_name}.csv")

        with open(output_file, "w", newline="", encoding="utf-8") as f:
            writer = csv.writer(f)

            for row_idx in range(sheet.nrows):
                row_values = []
                for col_idx in range(sheet.ncols):
                    cell = sheet.cell(row_idx, col_idx)
                    value = cell.value

                    # Convert Excel dates to ISO format
                    if cell.ctype == xlrd.XL_CELL_DATE and isinstance(
                        value, (int, float)
                    ):
                        try:
                            date_tuple = xlrd.xldate_as_tuple(
                                float(value), workbook.datemode
                            )
                            value = f"{date_tuple[0]:04d}-{date_tuple[1]:02d}-{date_tuple[2]:02d}"
                            if date_tuple[3] or date_tuple[4] or date_tuple[5]:
                                value += f" {date_tuple[3]:02d}:{date_tuple[4]:02d}:{date_tuple[5]:02d}"
                        except:
                            pass

                    row_values.append(value)

                writer.writerow(row_values)

        print(f"Converted: {sheet_name} -> {output_file}")

    print(f"\nConverted {workbook.nsheets} worksheet(s) from '{input_file}'")


def main():
    if len(sys.argv) < 2:
        print(f"Usage: {sys.argv[0]} <input.xls> [output_dir]", file=sys.stderr)
        sys.exit(1)

    input_file = sys.argv[1]
    output_dir = sys.argv[2] if len(sys.argv) > 2 else None

    xls_to_csv(input_file, output_dir)


if __name__ == "__main__":
    main()