Transforming and Combining Data

Converting between formats

At some point, someone will send you data in the wrong format.

Your finance team exports billing data as Excel files. Your CRM exports customer lists as CSV files. Your analytics platform exports event data as JSON. And you need all of it in one place, in the same format.

Claude Code converts between formats in a single request. Put the file in your project folder and tell it what you want:

Convert billing_q4.xlsx to a CSV file

Claude Code reads the Excel file, pulls the data out, and saves a new CSV in the same folder. Your original file stays untouched.

This works across all the common data formats:

Convert this JSON file to an Excel spreadsheet

Turn survey_results.csv into an Excel file with the columns in this order: name, email, rating, comments

Convert all the CSV files in this folder to Excel files

That last one is worth highlighting. If you have a dozen files to convert, Claude Code handles the whole batch instead of making you do them one by one.

Tip: When converting to Excel, you can ask for formatting too: "Convert this CSV to Excel and make the header row bold with a blue background." Claude Code applies the formatting as part of the conversion.

Merging data from multiple files

Combining data from different sources is one of those tasks that sounds straightforward until you actually sit down to do it.

Say your company tracks sales across three regions, each with its own monthly report: sales_east.csv, sales_west.csv, and sales_central.csv. All three files have the same columns, just different rows. You want one file with everything.

Combine sales_east.csv, sales_west.csv, and sales_central.csv into a single file called sales_all.csv

Claude Code reads all three files, stacks the rows together, and saves the result.

When the files have the same columns in the same order, this works perfectly. Real data isn't always that tidy, though. If the columns are in a different order across files, or one file has an extra column the others don't, tell Claude Code about the differences:

Combine these three sales files into one. They have the same columns but in different order. The east file also has an extra "territory" column — include it and leave it blank for the other regions.

Being explicit about mismatches gets you better results than hoping Claude Code will figure it out.

For a different kind of merge — connecting two files side by side rather than stacking rows — be specific about how to match them:

Merge customer_list.csv and billing_history.csv using the customer_id column to match rows. Keep all customers even if they don't have billing records.

Think of it like looking up information across two separate spreadsheets. One has customer names and contact details. The other has purchase amounts and dates. You want a single spreadsheet with everything, lined up by customer.

Heads up: When merging files from different sources, some rows won't have a match. A customer in your CRM might not appear in your billing system yet. Tell Claude Code what to do with unmatched rows — keep them with blank fields, or drop them. If you don't say, Claude Code picks for you, and it might not pick what you want.

Adding calculated columns

Sometimes your data has the raw numbers but not the thing you actually want to know.

Your customer_orders.csv has columns for revenue and cost, but no profit margin. Instead of opening a spreadsheet and writing a formula row by row, ask Claude Code:

Add a column called profit_margin that calculates (revenue - cost) / revenue as a percentage. Save the result as customer_orders_with_margins.csv

Claude Code adds the column, runs the math for every row, and saves a new file.

Here are other calculated columns that come up often:

Add a column that shows the number of days between order_date and ship_date

Add a column that categorizes orders as "small" (under $100), "medium" ($100-$500), or "large" (over $500)

Add a column showing each customer's total lifetime spend across all their orders

That last example is worth pausing on. It doesn't do math within a single row — it groups all orders by customer and calculates a running total. In a spreadsheet, you'd wrestle with SUMIF formulas or a pivot table to get this. With Claude Code, you describe the result you want and it works out the approach.

Reshaping your data

Sometimes everything you need is in the file, but it's arranged the wrong way.

Here's a situation you'll probably recognize. You have a spreadsheet where every row is a single sale: a date, a product name, and an amount. Hundreds of rows.

Date,Product,Amount
2026-01-03,Widget,150
2026-01-03,Gadget,200
2026-01-10,Widget,175
2026-01-10,Gadget,225

But what you actually want is a summary where each row is a date and each product gets its own column:

Date,Widget,Gadget
2026-01-03,150,200
2026-01-10,175,225

Spreadsheet people call this a "pivot." You're rotating the data — turning values from one column (product names) into their own separate columns.

The good news is you don't need that vocabulary with Claude Code. Describe the shape you're after:

Rearrange this data so each row is a date and each product gets its own column, with sales amounts as the values

It works the other direction too. If you have a wide table with months as columns and you need a tall list with one row per month:

Reshape this spreadsheet so that each month becomes its own row instead of its own column. I want three columns: region, month, and revenue.

Tip: When you're not sure how to describe the shape you want, show Claude Code a small example of the output. Paste or describe two or three rows of your ideal result, and it will figure out how to get your data there.

A practical example: CRM meets billing

Here's a scenario that pulls together everything from this page.

You're a product manager preparing a customer health review. You have two files from two different systems:

• crm_export.csv — customer names, account managers, sign-up dates, and support tier (Basic, Pro, Enterprise)
• billing_jan.json — January billing records with customer IDs, invoice amounts, and payment dates (exported from your billing system as JSON)

You need one spreadsheet with customer details and billing data side by side, plus a column showing how long each customer has been active.

Here's the whole thing, three prompts:

Convert billing_jan.json to a CSV file called billing_jan.csv

Merge crm_export.csv and billing_jan.csv using the customer_id column. Keep all CRM customers even if they have no January billing.

Add a column called months_active that calculates how many months have passed between the signup_date and today. Save the final result as customer_health_jan.csv

That's it. Format conversion, a merge, and a calculated column. The result is a single spreadsheet your team can review — no formulas, no VLOOKUP, no copy-pasting between files.

Next, you'll move from raw numbers to something visual — creating charts that make your data easier to read and share.