# Flagged CSV
Convert XLSX files to CSV while preserving visual formatting information as inline flags.
Traditional XLSX → CSV conversion will cause the loss of format such as background cell colors or cell merge:
The new Flagged CSV will attach important flags to the original cell value, keeping the colors/cell-merge with {} flags
## Why Flagged CSV is Critical for AI
Traditional AI models cannot process XLSX files directly. When users convert XLSX to CSV for AI processing, critical visual information is lost - colors that indicate categories, merged cells that show date ranges, and formatting that conveys meaning. This forces users to manually add context that was already present visually.
### Real-World Example: AI's Limitation with Standard CSV
Consider this financial data with colored categories and merged cells for date ranges:
**Original Excel file** has:
- Different colors for each expense category
- Merged cells showing the time span for each expense
- My color1 and My color4 share the same green color (#84E291)
**After standard CSV conversion**, an AI assistant fails to answer basic questions:
> **User**: "Which two items have the same color?"
>
> **AI**: "The provided CSV file does not contain any information about the colors of the cells..."
> **User**: "What is the time range for each color's spend?"
>
> **AI**: "I cannot determine time ranges from the CSV data..."
### The Flagged CSV Solution
With Flagged CSV format, the same AI can now answer correctly:
```csv
Name,Color,Value,JUL{#0E2841},AUG{#0E2841},SEP{#0E2841},OCT{#0E2841},NOV{#0E2841},DEC{#0E2841}
My color1,{#84E291},30,$500{#84E291}{MG:881261},{MG:881261},,,,
My color2,{#E49EDD},32,$600{#E49EDD}{MG:316508},{MG:316508},{MG:316508},,,
My color3,{#F6C6AC},34,$700{#F6C6AC}{MG:353471},{MG:353471},{MG:353471},{MG:353471},,
My color4,{#84E291},36,$800{#84E291}{MG:860393},{MG:860393},{MG:860393},{MG:860393},{MG:860393},{MG:860393}
```
Now the AI can answer:
> **Q: Which two items have the same color?**
>
> **A: My color1 and My color4 have the same color (#84E291).**
> **Q: What is the time range and spend for each item?**
>
> **A:**
> - **My color1**: Time range JUL-AUG with a spend of $500
> - **My color2**: Time range JUL-SEP with a spend of $600
> - **My color3**: Time range JUL-OCT with a spend of $700
> - **My color4**: Time range JUL-DEC with a spend of $800
This enables AI to understand:
- **Color patterns** - Which items belong to the same category
- **Time ranges** - Merged cells indicate duration
- **Relationships** - Visual cues that humans naturally understand
## Overview
Flagged CSV is a Python library and command-line tool that converts Excel (XLSX) files to CSV format while preserving important visual information that would normally be lost in conversion:
- **Cell background colors** - Preserved as `{#RRGGBB}` or `{bc:#RRGGBB}` flags
- **Cell foreground colors** - Preserved as `{fc:#RRGGBB}` flags
- **Merged cells** - Marked with `{MG:XXXXXX}` flags where XXXXXX is a unique identifier
- **Cell formatting** - Currency symbols, number formats, dates preserved as displayed in Excel
- **Cell locations** - Original Excel coordinates preserved as `{l:CellRef}` flags
## Installation
### Via uv (Recommended)
```bash
# Clone the repository
git clone https://github.com/yourusername/flagged-csv.git
cd flagged-csv
# Sync dependencies with uv
uv sync
# Install in development mode
uv pip install -e .
```
### Via pip
```bash
pip install flagged-csv
```
## Quick Start
### Command Line Usage
```bash
# Basic conversion
flagged-csv input.xlsx -t Sheet1 > output.csv
# Include all colors (foreground and background) with merge information
flagged-csv input.xlsx -t Sheet1 --include-colors --signal-merge -o output.csv
# Include background colors only, preserve formatting, ignore white backgrounds
flagged-csv input.xlsx -t Sheet1 --preserve-formats --include-bg-colors --ignore-colors "#FFFFFF"
# Include foreground colors only (font colors)
flagged-csv input.xlsx -t Sheet1 --include-fg-colors -o output.csv
# Include cell locations and keep empty rows for structure preservation
flagged-csv input.xlsx -t Sheet1 --add-location --keep-empty-lines -o output.csv
# Process with size limits
flagged-csv input.xlsx -t Sheet1 --max-rows 1000 --max-columns 200 -o output.csv
```
### Python Library Usage
```bash
# Run Python scripts with uv
uv run python your_script.py
```
```python
# your_script.py
from flagged_csv import XlsxConverter
# Create converter instance
converter = XlsxConverter()
# Convert with all formatting options
csv_content = converter.convert_to_csv(
'data.xlsx',
tab_name='Sheet1',
include_colors=True,
signal_merge=True,
preserve_formats=True,
ignore_colors='#FFFFFF'
)
# Save to file
with open('output.csv', 'w') as f:
f.write(csv_content)
```
## Flag Format Specification
### Color Flags
#### Background Color
- Format: `{#RRGGBB}` (backward-compatible) or `{bc:#RRGGBB}` (explicit)
- Example: `Sales{#FF0000}` or `Sales{bc:#FF0000}` - "Sales" with red background
#### Foreground Color
- Format: `{fc:#RRGGBB}`
- Example: `Text{fc:#0000FF}` - "Text" with blue font color
#### Combined Colors
- Multiple flags can be combined: `100{#FFFF00}{fc:#FF0000}{MG:123456}{l:B5}`
- This represents: Yellow background, red text, part of merge group 123456, from cell B5
### Merge Flags
- Format: `{MG:XXXXXX}` where XXXXXX is a 6-digit identifier
- All cells in a merged range share the same ID
- The first cell contains the actual value
- Subsequent cells contain only the merge flag
### Location Flags
- Format: `{l:CellRef}` where CellRef is the Excel cell coordinate
- Example: `Value{l:A5}` - "Value" from cell A5
- Useful for preserving cell position information
### Example Output
Given an Excel file with:
- Cell A1: "Total Sales" with blue background (#0000FF) and white text (#FFFFFF)
- Cells B1-D1: Merged cell containing "$1,000" with green background (#00FF00)
- Cell A2: "Profit" with red text (#FF0000)
The CSV output with `--include-colors` would be:
```csv
Total Sales{#0000FF}{fc:#FFFFFF},$1000{#00FF00}{MG:384756},{MG:384756},{MG:384756}
Profit{fc:#FF0000},,
```
## Configuration Options
### CLI Options
- `-t, --tab-name`: Sheet name to convert (required)
- `-o, --output`: Output file path (default: stdout)
- `--format`: Output format: csv, html, or markdown (default: csv)
- `--include-colors`: Include both foreground and background colors
- `--include-bg-colors`: Include background colors only as {#RRGGBB} flags
- `--include-fg-colors`: Include foreground colors only as {fc:#RRGGBB} flags
- `--signal-merge`: Include merged cell information as {MG:XXXXXX} flags
- `--preserve-formats`: Preserve number/date formatting as displayed in Excel
- `--ignore-colors`: Comma-separated hex colors to ignore (e.g., "#FFFFFF,#000000")
- `--add-location`: Add cell coordinates {l:A5} to non-empty cells
- `--keep-empty-lines`: Preserve empty rows to maintain original row positions
- `--max-rows`: Maximum number of rows to process (default: 300)
- `--max-columns`: Maximum number of columns to process (default: 100)
- `--no-header`: Exclude DataFrame column headers (A, B, C...) from output
- `--keep-na`: Keep NA values instead of converting to empty strings
### Python API Options
```python
from flagged_csv import XlsxConverter, XlsxConverterConfig
# Create converter with custom configuration
config = XlsxConverterConfig(
keep_default_na=False, # Convert NA to empty strings
index=False, # Don't include row index
header=False, # Don't include DataFrame column headers (default)
keep_empty_lines=False, # Remove empty rows (default)
add_location=False # Don't add cell coordinates (default)
)
converter = XlsxConverter(config)
# Convert with additional options
csv_content = converter.convert_to_csv(
'data.xlsx',
tab_name='Sheet1',
include_colors=True, # Include both fg and bg colors
# OR use specific color options:
# include_bg_colors=True, # Background colors only
# include_fg_colors=True, # Foreground colors only
signal_merge=True,
preserve_formats=True,
add_location=True, # Add {l:A5} cell coordinates
keep_empty_lines=True, # Keep empty rows
max_rows=500, # Process up to 500 rows
max_columns=50 # Process up to 50 columns
)
```
## Advanced Usage
### Processing Multiple Sheets
Save this as `process_sheets.py`:
```python
from flagged_csv import XlsxConverter
import pandas as pd
converter = XlsxConverter()
# Process all sheets in a workbook
xl_file = pd.ExcelFile('multi_sheet.xlsx')
for sheet_name in xl_file.sheet_names:
csv_content = converter.convert_to_csv(
'multi_sheet.xlsx',
tab_name=sheet_name,
include_colors=True,
signal_merge=True
)
with open(f'{sheet_name}.csv', 'w') as f:
f.write(csv_content)
print(f'Converted {sheet_name} -> {sheet_name}.csv')
```
Run with:
```bash
uv run python process_sheets.py
```
### Parsing Flagged CSV
Save this as `parse_flagged.py`:
```python
import re
import pandas as pd
def parse_flagged_csv(file_path):
"""Parse a flagged CSV file and extract values and formatting."""
df = pd.read_csv(file_path, header=None)
# Regular expressions for parsing flags
color_pattern = r'{#([0-9A-Fa-f]{6})}'
merge_pattern = r'{MG:(\d{6})}'
location_pattern = r'{l:([A-Z]+\d+)}'
# Extract clean values and formatting info
for row_idx in range(len(df)):
for col_idx in range(len(df.columns)):
cell = str(df.iloc[row_idx, col_idx])
# Extract color
color_match = re.search(color_pattern, cell)
if color_match:
color = color_match.group(1)
print(f"Cell ({row_idx},{col_idx}) has color #{color}")
# Extract merge ID
merge_match = re.search(merge_pattern, cell)
if merge_match:
merge_id = merge_match.group(1)
print(f"Cell ({row_idx},{col_idx}) is part of merge group {merge_id}")
# Extract location
location_match = re.search(location_pattern, cell)
if location_match:
location = location_match.group(1)
print(f"Cell ({row_idx},{col_idx}) originally from {location}")
# Get clean value (remove all flags)
clean_value = re.sub(r'{[^}]+}', '', cell)
df.iloc[row_idx, col_idx] = clean_value
return df
# Example usage
if __name__ == "__main__":
df = parse_flagged_csv('output.csv')
print("\nCleaned data:")
print(df)
```
Run with:
```bash
uv run python parse_flagged.py
```
### Working with Merged Cells
```python
def reconstruct_merged_cells(df):
"""Reconstruct merged cell ranges from flagged CSV."""
merge_groups = {}
for row_idx in range(len(df)):
for col_idx in range(len(df.columns)):
cell = str(df.iloc[row_idx, col_idx])
# Find merge ID
match = re.search(r'{MG:(\d{6})}', cell)
if match:
merge_id = match.group(1)
if merge_id not in merge_groups:
merge_groups[merge_id] = []
merge_groups[merge_id].append((row_idx, col_idx))
# merge_groups now contains all cells belonging to each merge
for merge_id, cells in merge_groups.items():
print(f"Merge {merge_id}: {cells}")
```
## Output Formats
### CSV (Default)
Standard CSV format with flags appended to cell values.
### HTML
```python
html_output = converter.convert_to_csv(
'data.xlsx',
tab_name='Sheet1',
output_format='html',
include_colors=True
)
```
### Markdown
```python
markdown_output = converter.convert_to_csv(
'data.xlsx',
tab_name='Sheet1',
output_format='markdown',
include_colors=True
)
```
## Error Handling
The library handles various error cases gracefully:
```python
try:
csv_content = converter.convert_to_csv('data.xlsx', tab_name='InvalidSheet')
except ValueError as e:
print(f"Sheet not found: {e}")
except FileNotFoundError as e:
print(f"File not found: {e}")
```
## Performance Considerations
- The library uses multiple fallback engines (calamine, openpyxl, xlrd) for maximum compatibility
- Large files are processed efficiently with streaming where possible
- Color extraction uses caching to avoid repeated theme color lookups
- Default limits of 300 rows and 100 columns can be adjusted using `--max-rows` and `--max-columns`
- Empty rows are automatically removed by default (use `--keep-empty-lines` to preserve them)
- Trailing empty content is always trimmed to reduce file size
## Testing
Run the test suite using uv:
```bash
# Run all tests
uv run pytest tests/test_converter.py
# Run tests with verbose output
uv run pytest tests/test_converter.py -v
# Run a specific test
uv run pytest tests/test_converter.py::TestXlsxConverter::test_color_extraction -v
```
## Development
```bash
# Set up development environment
uv sync
# Run the example script
uv run python example.py
# Run the CLI tool in development
uv run flagged-csv --help
```
## Requirements
- Python 3.11+
- pandas >= 2.0.0, < 2.2.0
- numpy < 2.0 (for compatibility)
- pydantic >= 2.0.0 (for configuration models)
- openpyxl >= 3.1.0
- python-calamine >= 0.2.0 (for robust Excel reading)
- xlrd >= 2.0.0 (for older Excel format support)
- click >= 8.0.0 (for CLI)
## Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Run tests to ensure everything works (`uv run pytest tests/`)
4. Commit your changes (`git commit -m 'Add amazing feature'`)
5. Push to the branch (`git push origin feature/amazing-feature`)
6. Open a Pull Request
## License
This project is licensed under the MIT License - see the LICENSE file for details.
## AI Integration Guide
When using Flagged CSV with AI systems (ChatGPT, Claude, etc.), you have two options:
### Option 1: Use the provided prompt file
Simply include the contents of `flagged-csv.prompt.md` in your conversation with the AI. This file contains the complete specification for the Flagged CSV format.
### Option 2: Use this condensed prompt
```
The CSV data uses Flagged CSV format where:
- {#RRGGBB} indicates cell background color (e.g., {#FF0000} is red)
- {MG:XXXXXX} indicates merged cells (same ID = same merged group)
- {l:CellRef} indicates the original Excel cell location (e.g., {l:A5} is cell A5)
- The value in the merged cell should be applied to the entire range as a whole.
- Multiple flags can appear together: value{#color}{MG:id}{l:cell}
```
This simple context enables AI to answer questions about:
- Color-based categorization
- Time ranges from merged cells
- Visual patterns and relationships
- Conditional formatting meanings
## Acknowledgments
This library is inspired by the need to preserve Excel's visual information during data processing pipelines, particularly for financial and business reporting applications where cell colors and merged cells convey important meaning. It bridges the gap between human-readable Excel files and AI-processable data formats.
The code for this project was primarily written using [Claude Code](https://claude.ai/code), demonstrating how AI can help create tools that make data more accessible to AI systems.
Raw data
{
"_id": null,
"home_page": null,
"name": "flagged-csv",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.11",
"maintainer_email": null,
"keywords": "xlsx, csv, excel, converter, formatting, colors, merge-cells, ai, data-processing",
"author": null,
"author_email": "ross <ross.jill.websolution@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/96/53/3718ffe2d8a57d408860023abc34ef255975655e122e514d757472aefcfd/flagged_csv-0.1.3.tar.gz",
"platform": null,
"description": "# Flagged CSV\n\nConvert XLSX files to CSV while preserving visual formatting information as inline flags.\n\nTraditional XLSX \u2192 CSV conversion will cause the loss of format such as background cell colors or cell merge:\n\n\nThe new Flagged CSV will attach important flags to the original cell value, keeping the colors/cell-merge with {} flags\n\n\n## Why Flagged CSV is Critical for AI\n\nTraditional AI models cannot process XLSX files directly. When users convert XLSX to CSV for AI processing, critical visual information is lost - colors that indicate categories, merged cells that show date ranges, and formatting that conveys meaning. This forces users to manually add context that was already present visually.\n\n### Real-World Example: AI's Limitation with Standard CSV\n\nConsider this financial data with colored categories and merged cells for date ranges:\n\n**Original Excel file** has:\n- Different colors for each expense category\n- Merged cells showing the time span for each expense\n- My color1 and My color4 share the same green color (#84E291)\n\n**After standard CSV conversion**, an AI assistant fails to answer basic questions:\n\n> **User**: \"Which two items have the same color?\"\n> \n> **AI**: \"The provided CSV file does not contain any information about the colors of the cells...\"\n\n> **User**: \"What is the time range for each color's spend?\"\n> \n> **AI**: \"I cannot determine time ranges from the CSV data...\"\n\n### The Flagged CSV Solution\n\nWith Flagged CSV format, the same AI can now answer correctly:\n\n```csv\nName,Color,Value,JUL{#0E2841},AUG{#0E2841},SEP{#0E2841},OCT{#0E2841},NOV{#0E2841},DEC{#0E2841}\nMy color1,{#84E291},30,$500{#84E291}{MG:881261},{MG:881261},,,,\nMy color2,{#E49EDD},32,$600{#E49EDD}{MG:316508},{MG:316508},{MG:316508},,,\nMy color3,{#F6C6AC},34,$700{#F6C6AC}{MG:353471},{MG:353471},{MG:353471},{MG:353471},,\nMy color4,{#84E291},36,$800{#84E291}{MG:860393},{MG:860393},{MG:860393},{MG:860393},{MG:860393},{MG:860393}\n```\n\nNow the AI can answer:\n\n> **Q: Which two items have the same color?**\n> \n> **A: My color1 and My color4 have the same color (#84E291).**\n\n> **Q: What is the time range and spend for each item?**\n> \n> **A:** \n> - **My color1**: Time range JUL-AUG with a spend of $500\n> - **My color2**: Time range JUL-SEP with a spend of $600 \n> - **My color3**: Time range JUL-OCT with a spend of $700\n> - **My color4**: Time range JUL-DEC with a spend of $800\n\nThis enables AI to understand:\n- **Color patterns** - Which items belong to the same category\n- **Time ranges** - Merged cells indicate duration\n- **Relationships** - Visual cues that humans naturally understand\n\n## Overview\n\nFlagged CSV is a Python library and command-line tool that converts Excel (XLSX) files to CSV format while preserving important visual information that would normally be lost in conversion:\n\n- **Cell background colors** - Preserved as `{#RRGGBB}` or `{bc:#RRGGBB}` flags\n- **Cell foreground colors** - Preserved as `{fc:#RRGGBB}` flags\n- **Merged cells** - Marked with `{MG:XXXXXX}` flags where XXXXXX is a unique identifier\n- **Cell formatting** - Currency symbols, number formats, dates preserved as displayed in Excel\n- **Cell locations** - Original Excel coordinates preserved as `{l:CellRef}` flags\n\n## Installation\n\n### Via uv (Recommended)\n\n```bash\n# Clone the repository\ngit clone https://github.com/yourusername/flagged-csv.git\ncd flagged-csv\n\n# Sync dependencies with uv\nuv sync\n\n# Install in development mode\nuv pip install -e .\n```\n\n### Via pip\n\n```bash\npip install flagged-csv\n```\n\n## Quick Start\n\n### Command Line Usage\n\n```bash\n# Basic conversion\nflagged-csv input.xlsx -t Sheet1 > output.csv\n\n# Include all colors (foreground and background) with merge information\nflagged-csv input.xlsx -t Sheet1 --include-colors --signal-merge -o output.csv\n\n# Include background colors only, preserve formatting, ignore white backgrounds\nflagged-csv input.xlsx -t Sheet1 --preserve-formats --include-bg-colors --ignore-colors \"#FFFFFF\"\n\n# Include foreground colors only (font colors)\nflagged-csv input.xlsx -t Sheet1 --include-fg-colors -o output.csv\n\n# Include cell locations and keep empty rows for structure preservation\nflagged-csv input.xlsx -t Sheet1 --add-location --keep-empty-lines -o output.csv\n\n# Process with size limits\nflagged-csv input.xlsx -t Sheet1 --max-rows 1000 --max-columns 200 -o output.csv\n```\n\n### Python Library Usage\n\n```bash\n# Run Python scripts with uv\nuv run python your_script.py\n```\n\n```python\n# your_script.py\nfrom flagged_csv import XlsxConverter\n\n# Create converter instance\nconverter = XlsxConverter()\n\n# Convert with all formatting options\ncsv_content = converter.convert_to_csv(\n 'data.xlsx',\n tab_name='Sheet1',\n include_colors=True,\n signal_merge=True,\n preserve_formats=True,\n ignore_colors='#FFFFFF'\n)\n\n# Save to file\nwith open('output.csv', 'w') as f:\n f.write(csv_content)\n```\n\n## Flag Format Specification\n\n### Color Flags\n\n#### Background Color\n- Format: `{#RRGGBB}` (backward-compatible) or `{bc:#RRGGBB}` (explicit)\n- Example: `Sales{#FF0000}` or `Sales{bc:#FF0000}` - \"Sales\" with red background\n\n#### Foreground Color\n- Format: `{fc:#RRGGBB}`\n- Example: `Text{fc:#0000FF}` - \"Text\" with blue font color\n\n#### Combined Colors\n- Multiple flags can be combined: `100{#FFFF00}{fc:#FF0000}{MG:123456}{l:B5}`\n- This represents: Yellow background, red text, part of merge group 123456, from cell B5\n\n### Merge Flags \n- Format: `{MG:XXXXXX}` where XXXXXX is a 6-digit identifier\n- All cells in a merged range share the same ID\n- The first cell contains the actual value\n- Subsequent cells contain only the merge flag\n\n### Location Flags\n- Format: `{l:CellRef}` where CellRef is the Excel cell coordinate\n- Example: `Value{l:A5}` - \"Value\" from cell A5\n- Useful for preserving cell position information\n\n### Example Output\n\nGiven an Excel file with:\n- Cell A1: \"Total Sales\" with blue background (#0000FF) and white text (#FFFFFF)\n- Cells B1-D1: Merged cell containing \"$1,000\" with green background (#00FF00)\n- Cell A2: \"Profit\" with red text (#FF0000)\n\nThe CSV output with `--include-colors` would be:\n```csv\nTotal Sales{#0000FF}{fc:#FFFFFF},$1000{#00FF00}{MG:384756},{MG:384756},{MG:384756}\nProfit{fc:#FF0000},,\n```\n\n## Configuration Options\n\n### CLI Options\n\n- `-t, --tab-name`: Sheet name to convert (required)\n- `-o, --output`: Output file path (default: stdout)\n- `--format`: Output format: csv, html, or markdown (default: csv)\n- `--include-colors`: Include both foreground and background colors\n- `--include-bg-colors`: Include background colors only as {#RRGGBB} flags\n- `--include-fg-colors`: Include foreground colors only as {fc:#RRGGBB} flags\n- `--signal-merge`: Include merged cell information as {MG:XXXXXX} flags\n- `--preserve-formats`: Preserve number/date formatting as displayed in Excel\n- `--ignore-colors`: Comma-separated hex colors to ignore (e.g., \"#FFFFFF,#000000\")\n- `--add-location`: Add cell coordinates {l:A5} to non-empty cells\n- `--keep-empty-lines`: Preserve empty rows to maintain original row positions\n- `--max-rows`: Maximum number of rows to process (default: 300)\n- `--max-columns`: Maximum number of columns to process (default: 100)\n- `--no-header`: Exclude DataFrame column headers (A, B, C...) from output\n- `--keep-na`: Keep NA values instead of converting to empty strings\n\n### Python API Options\n\n```python\nfrom flagged_csv import XlsxConverter, XlsxConverterConfig\n\n# Create converter with custom configuration\nconfig = XlsxConverterConfig(\n keep_default_na=False, # Convert NA to empty strings\n index=False, # Don't include row index\n header=False, # Don't include DataFrame column headers (default)\n keep_empty_lines=False, # Remove empty rows (default)\n add_location=False # Don't add cell coordinates (default)\n)\n\nconverter = XlsxConverter(config)\n\n# Convert with additional options\ncsv_content = converter.convert_to_csv(\n 'data.xlsx',\n tab_name='Sheet1',\n include_colors=True, # Include both fg and bg colors\n # OR use specific color options:\n # include_bg_colors=True, # Background colors only\n # include_fg_colors=True, # Foreground colors only\n signal_merge=True,\n preserve_formats=True,\n add_location=True, # Add {l:A5} cell coordinates\n keep_empty_lines=True, # Keep empty rows\n max_rows=500, # Process up to 500 rows\n max_columns=50 # Process up to 50 columns\n)\n```\n\n## Advanced Usage\n\n### Processing Multiple Sheets\n\nSave this as `process_sheets.py`:\n\n```python\nfrom flagged_csv import XlsxConverter\nimport pandas as pd\n\nconverter = XlsxConverter()\n\n# Process all sheets in a workbook\nxl_file = pd.ExcelFile('multi_sheet.xlsx')\nfor sheet_name in xl_file.sheet_names:\n csv_content = converter.convert_to_csv(\n 'multi_sheet.xlsx',\n tab_name=sheet_name,\n include_colors=True,\n signal_merge=True\n )\n \n with open(f'{sheet_name}.csv', 'w') as f:\n f.write(csv_content)\n print(f'Converted {sheet_name} -> {sheet_name}.csv')\n```\n\nRun with:\n```bash\nuv run python process_sheets.py\n```\n\n### Parsing Flagged CSV\n\nSave this as `parse_flagged.py`:\n\n```python\nimport re\nimport pandas as pd\n\ndef parse_flagged_csv(file_path):\n \"\"\"Parse a flagged CSV file and extract values and formatting.\"\"\"\n df = pd.read_csv(file_path, header=None)\n \n # Regular expressions for parsing flags\n color_pattern = r'{#([0-9A-Fa-f]{6})}'\n merge_pattern = r'{MG:(\\d{6})}'\n location_pattern = r'{l:([A-Z]+\\d+)}'\n \n # Extract clean values and formatting info\n for row_idx in range(len(df)):\n for col_idx in range(len(df.columns)):\n cell = str(df.iloc[row_idx, col_idx])\n \n # Extract color\n color_match = re.search(color_pattern, cell)\n if color_match:\n color = color_match.group(1)\n print(f\"Cell ({row_idx},{col_idx}) has color #{color}\")\n \n # Extract merge ID\n merge_match = re.search(merge_pattern, cell)\n if merge_match:\n merge_id = merge_match.group(1)\n print(f\"Cell ({row_idx},{col_idx}) is part of merge group {merge_id}\")\n \n # Extract location\n location_match = re.search(location_pattern, cell)\n if location_match:\n location = location_match.group(1)\n print(f\"Cell ({row_idx},{col_idx}) originally from {location}\")\n \n # Get clean value (remove all flags)\n clean_value = re.sub(r'{[^}]+}', '', cell)\n df.iloc[row_idx, col_idx] = clean_value\n \n return df\n\n# Example usage\nif __name__ == \"__main__\":\n df = parse_flagged_csv('output.csv')\n print(\"\\nCleaned data:\")\n print(df)\n```\n\nRun with:\n```bash\nuv run python parse_flagged.py\n```\n\n### Working with Merged Cells\n\n```python\ndef reconstruct_merged_cells(df):\n \"\"\"Reconstruct merged cell ranges from flagged CSV.\"\"\"\n merge_groups = {}\n \n for row_idx in range(len(df)):\n for col_idx in range(len(df.columns)):\n cell = str(df.iloc[row_idx, col_idx])\n \n # Find merge ID\n match = re.search(r'{MG:(\\d{6})}', cell)\n if match:\n merge_id = match.group(1)\n if merge_id not in merge_groups:\n merge_groups[merge_id] = []\n merge_groups[merge_id].append((row_idx, col_idx))\n \n # merge_groups now contains all cells belonging to each merge\n for merge_id, cells in merge_groups.items():\n print(f\"Merge {merge_id}: {cells}\")\n```\n\n## Output Formats\n\n### CSV (Default)\nStandard CSV format with flags appended to cell values.\n\n### HTML\n```python\nhtml_output = converter.convert_to_csv(\n 'data.xlsx',\n tab_name='Sheet1',\n output_format='html',\n include_colors=True\n)\n```\n\n### Markdown\n```python\nmarkdown_output = converter.convert_to_csv(\n 'data.xlsx', \n tab_name='Sheet1',\n output_format='markdown',\n include_colors=True\n)\n```\n\n## Error Handling\n\nThe library handles various error cases gracefully:\n\n```python\ntry:\n csv_content = converter.convert_to_csv('data.xlsx', tab_name='InvalidSheet')\nexcept ValueError as e:\n print(f\"Sheet not found: {e}\")\nexcept FileNotFoundError as e:\n print(f\"File not found: {e}\")\n```\n\n## Performance Considerations\n\n- The library uses multiple fallback engines (calamine, openpyxl, xlrd) for maximum compatibility\n- Large files are processed efficiently with streaming where possible\n- Color extraction uses caching to avoid repeated theme color lookups\n- Default limits of 300 rows and 100 columns can be adjusted using `--max-rows` and `--max-columns`\n- Empty rows are automatically removed by default (use `--keep-empty-lines` to preserve them)\n- Trailing empty content is always trimmed to reduce file size\n\n## Testing\n\nRun the test suite using uv:\n\n```bash\n# Run all tests\nuv run pytest tests/test_converter.py\n\n# Run tests with verbose output\nuv run pytest tests/test_converter.py -v\n\n# Run a specific test\nuv run pytest tests/test_converter.py::TestXlsxConverter::test_color_extraction -v\n```\n\n## Development\n\n```bash\n# Set up development environment\nuv sync\n\n# Run the example script\nuv run python example.py\n\n# Run the CLI tool in development\nuv run flagged-csv --help\n```\n\n## Requirements\n\n- Python 3.11+\n- pandas >= 2.0.0, < 2.2.0\n- numpy < 2.0 (for compatibility)\n- pydantic >= 2.0.0 (for configuration models)\n- openpyxl >= 3.1.0\n- python-calamine >= 0.2.0 (for robust Excel reading)\n- xlrd >= 2.0.0 (for older Excel format support)\n- click >= 8.0.0 (for CLI)\n\n## Contributing\n\nContributions are welcome! Please feel free to submit a Pull Request.\n\n1. Fork the repository\n2. Create your feature branch (`git checkout -b feature/amazing-feature`)\n3. Run tests to ensure everything works (`uv run pytest tests/`)\n4. Commit your changes (`git commit -m 'Add amazing feature'`)\n5. Push to the branch (`git push origin feature/amazing-feature`)\n6. Open a Pull Request\n\n## License\n\nThis project is licensed under the MIT License - see the LICENSE file for details.\n\n## AI Integration Guide\n\nWhen using Flagged CSV with AI systems (ChatGPT, Claude, etc.), you have two options:\n\n### Option 1: Use the provided prompt file\nSimply include the contents of `flagged-csv.prompt.md` in your conversation with the AI. This file contains the complete specification for the Flagged CSV format.\n\n### Option 2: Use this condensed prompt\n```\nThe CSV data uses Flagged CSV format where:\n- {#RRGGBB} indicates cell background color (e.g., {#FF0000} is red)\n- {MG:XXXXXX} indicates merged cells (same ID = same merged group)\n- {l:CellRef} indicates the original Excel cell location (e.g., {l:A5} is cell A5)\n- The value in the merged cell should be applied to the entire range as a whole.\n- Multiple flags can appear together: value{#color}{MG:id}{l:cell}\n```\n\nThis simple context enables AI to answer questions about:\n- Color-based categorization\n- Time ranges from merged cells\n- Visual patterns and relationships\n- Conditional formatting meanings\n\n## Acknowledgments\n\nThis library is inspired by the need to preserve Excel's visual information during data processing pipelines, particularly for financial and business reporting applications where cell colors and merged cells convey important meaning. It bridges the gap between human-readable Excel files and AI-processable data formats.\n\nThe code for this project was primarily written using [Claude Code](https://claude.ai/code), demonstrating how AI can help create tools that make data more accessible to AI systems.\n",
"bugtrack_url": null,
"license": null,
"summary": "Convert XLSX files to CSV with visual formatting preserved as inline flags",
"version": "0.1.3",
"project_urls": {
"Documentation": "https://github.com/ross-jill-ws/flagged-csv#readme",
"Homepage": "https://github.com/ross-jill-ws/flagged-csv",
"Issues": "https://github.com/ross-jill-ws/flagged-csv/issues",
"Repository": "https://github.com/ross-jill-ws/flagged-csv.git"
},
"split_keywords": [
"xlsx",
" csv",
" excel",
" converter",
" formatting",
" colors",
" merge-cells",
" ai",
" data-processing"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "c8ca34e6108cf5ea94820547db423d200c9f329a7f3b998694e95b71aef68016",
"md5": "8c9ab0cfa92340b7bd886e02a76bc1d1",
"sha256": "16df7e008cf3386363b55e8ff97181ec0e95776bcfffc37b9ba75d563071e7c8"
},
"downloads": -1,
"filename": "flagged_csv-0.1.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "8c9ab0cfa92340b7bd886e02a76bc1d1",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.11",
"size": 18876,
"upload_time": "2025-08-17T00:03:24",
"upload_time_iso_8601": "2025-08-17T00:03:24.083258Z",
"url": "https://files.pythonhosted.org/packages/c8/ca/34e6108cf5ea94820547db423d200c9f329a7f3b998694e95b71aef68016/flagged_csv-0.1.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "96533718ffe2d8a57d408860023abc34ef255975655e122e514d757472aefcfd",
"md5": "9b7d29935eb3555be3009c2195f3199f",
"sha256": "db4eb604b44d7296aa34f99a6ec4db8b96f44b035afed8474cad7bbe6e03aecc"
},
"downloads": -1,
"filename": "flagged_csv-0.1.3.tar.gz",
"has_sig": false,
"md5_digest": "9b7d29935eb3555be3009c2195f3199f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.11",
"size": 25257,
"upload_time": "2025-08-17T00:03:25",
"upload_time_iso_8601": "2025-08-17T00:03:25.682616Z",
"url": "https://files.pythonhosted.org/packages/96/53/3718ffe2d8a57d408860023abc34ef255975655e122e514d757472aefcfd/flagged_csv-0.1.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-17 00:03:25",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ross-jill-ws",
"github_project": "flagged-csv#readme",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "flagged-csv"
}
JSON
