Overview

When working with data, you often need to merge multiple spreadsheet files from different sources or time periods. Claude can automatically merge these files, handle header differences and data alignment issues, and generate a unified summary spreadsheet.

Use Cases

Merge monthly sales data sheets
Consolidate data submissions from multiple departments
Integrate survey results from different sources
Merge split large datasets

Steps

Step 1: Check File Consistency

First analyze whether the files to be merged have consistent structures.

Please check all Excel files in ~/data/sales folder:
- List all file names and sizes
- Check if column names are consistent across files
- Count rows in each file
- Identify data type differences
- Report whether files can be merged directly

Step 2: Simple Merge

Merge files with identical structures.

Please merge all .xlsx files in ~/data/sales folder:
- All files have the same column structure
- Keep header from the first file, skip header rows in other files
- Output to ~/data/sales_merged.xlsx
- Add a "Source File" column to indicate which file each row came from

Step 3: Handle Column Differences

Merge files with slightly different column names.

Files have slightly different column names, please merge intelligently:
- Identify columns with same meaning but different names (e.g., "Name" and "Full Name")
- Fill null values for missing columns
- Include extra columns in output
- Generate column mapping report showing how each column was handled

Step 4: Conditional Merge

Only merge data meeting specific conditions.

Merge all CSV files in ~/data/reports, but:
- Only include rows where "Status" column is "Completed"
- Filter out empty and duplicate rows
- Sort by "Date" column
- Output as ~/data/completed_reports.csv

Step 5: Add Summary Information

Add statistics to the merged file.

After merging data, please add a summary in a new worksheet:
- Row count contributed by each source file
- Totals by category
- Date range
- Sum and average of key numeric fields
Create an Excel file with "Raw Data" and "Summary" worksheets

Warning: Merging many files may take time and use memory. It's recommended to test on a small scale first to confirm the merge logic is correct before processing all files.

Tip: If you need to merge files regularly, you can have Claude create a script that runs automatically with just a double-click.

FAQ

Q: Merged Excel file won't open? A: File might be too large and exceed Excel's limit (about 1 million rows). Consider merging in batches or outputting as CSV format.

Q: How to handle inconsistent date formats? A: Tell Claude to recognize common date formats (like 2024-01-01, 01/01/2024, etc.) and convert to a standard format before merging.

Q: Can files in different formats be merged? A: Yes. Claude can process .xlsx, .xls, .csv and other formats simultaneously, merging them into a specified output format.