zways/automate-excel
Overview
This skill automates common Excel tasks: reading, writing, merging, transforming, validating and formatting .xlsx/.xls and CSV files. It provides a set of Python scripts and utilities that combine openpyxl for cell-level operations and pandas for analysis and batch processing. Use it to speed up repetitive spreadsheet workflows, batch conversions, and report generation.
How this skill works
Scripts inspect input paths, sheet names, and headers, then read data with openpyxl or pandas depending on the operation. They perform filtering, aggregation, deduplication, VLOOKUP-style merges, template-based row filling, conditional formatting, and column-type enforcement, then write outputs as Excel or CSV while optionally preserving formats. Error handling and validation report missing columns, duplicate keys, and read/write failures.
When to use it
- Merging multiple workbooks or many sheets into a single table for analysis
- Converting between CSV and Excel or exporting a specific sheet to CSV
- Batch processing a folder of spreadsheets with consistent transformations
- Filtering rows, splitting files by row count or by column values
- Running VLOOKUP-style joins across tables or applying multiple lookup tables
- Validating required columns, duplicate keys, or empty rows before downstream use
Best practices
- Confirm input paths, sheet names, and whether headers are present before running
- Prefer openpyxl for format-sensitive edits and pandas for heavy transforms/aggregations
- When output files exist, choose explicit overwrite or unique naming to avoid data loss
- Process large files in chunks or use write_only/read_only modes to reduce memory
- Log file-level errors and continue processing remaining files in batch jobs
Example use cases
- Concatenate monthly reports from dozens of workbooks into a single summary sheet
- Convert multiple CSV files into a multi-sheet Excel workbook for distribution
- Split a large order file into per-region Excel files using a region column
- Run automated validation to ensure required invoice columns exist and keys are unique
- Fill an invoice template for each row in a customer CSV using {{column}} placeholders
FAQ
openpyxl is used for cell-level edits, preserving styles and formulas; pandas is used alongside it when bulk analysis or joins are required.
How do I handle very large Excel files?
Use read_only and write_only modes in openpyxl or process with pandas in chunks. For batch jobs, stream rows and avoid loading all files into memory at once.
