Excel Data Wizard

Common Pain Points in Excel/CSV Data Processing

In daily office work, handling Excel or CSV files often involves repetitive manual tasks, such as merging data reports from multiple sources, cleaning messy data to remove duplicates or nulls, filtering specific records based on conditions, or generating visual charts. Relying on manual operations for these tasks is not only time-consuming but also prone to errors, especially with large files or complex manipulations.

How the Skill Works: Core Capabilities and Key Steps

Excel Data Wizard is a command-line tool that automates these operations through Python scripts. It offers the following core capabilities to cover the entire data processing workflow:
- Reading and Parsing: Supports .xlsx, .xls, and .csv files, with automatic detection of encodings (e.g., UTF-8) and delimiters (e.g., commas or tabs).
- Data Cleaning: Uses the excel_ops.py clean command for deduplication, handling null values, standardizing formats (e.g., date normalization), and detecting anomalies.
- Data Filtering: Through the excel_ops.py filter command, supports conditional filtering (e.g., --where "amount>5000") and regex matching, with multi-column joint conditions.
- Data Merging: Uses the excel_ops.py merge command for vertical concatenation (vconcat) of multiple files, horizontal concatenation (hconcat), or VLOOKUP-like association.
- Data Pivoting: Via the excel_ops.py pivot command, performs grouping aggregation (e.g., statistics by department), cross-tabulation generation, and multi-dimensional analysis.
- Formula Calculations: Includes new column calculations, conditional sums/counts, rankings, and proportions to simplify complex computations.
- Formatted Output: Exports to xlsx (with formatting), csv, json, or Markdown tables for further use.
- Chart Generation: Generates bar charts, line charts, pie charts, scatter plots, and outputs them as image files for visual data representation.

Key steps typically involve reading a file first, then choosing cleaning, filtering, or merging operations based on requirements, and finally outputting results. All operations are specified via command-line arguments, for example, excel_ops.py convert data.csv --to xlsx converts CSV to Excel.

Applicable Boundaries and Considerations

This tool is suitable for common office scenarios, but there are a few points to note: it depends on a Python environment and related libraries (e.g., pandas), so ensure dependencies are installed beforehand. For very large files (e.g., several GB), processing speed may be limited by system resources. Some advanced features (e.g., complex chart customization) might require additional configuration. Additionally, it is recommended to explicitly specify output file formats to avoid compatibility issues. Overall, it is well-suited for batch processing and automated workflows rather than real-time interactive analysis.