Summarize a CSV with a pivot table
Turn a long table into a useful summary.
Open a CSV or TSV, inspect its columns, choose groups and measures, then build a summary. Table data stays in this browser.
The first record supplies column labels. Duplicate or blank labels remain separate by column position. No number or date types are guessed.
How to summarize a CSV or TSV
Open a local file or paste its text. Choose its encoding and delimiter, then inspect columns. The text view lets you review decoding before using it. UTF-8, Windows-1252 and explicit UTF-16 little/big-endian files are supported. The first record is the header; duplicate and empty labels stay separate by position. Text is the starting type for every column, preserving identifiers such as 001.
Change a column to number or date only when that interpretation is intended. Select a numeric format with explicit decimal and grouping separators. Dates require YYYY-MM-DD, DD/MM/YYYY or MM/DD/YYYY; ambiguous slash dates are never guessed. Choose day, month, quarter or year buckets for grouping dates. Select up to three row groups and three column groups. The order of selected columns controls nesting; deselect and reselect to change that order.
Example: sales by month and product
Try the fictional sample, inspect columns, set Date to date with a month bucket and select it as a row group. Select Product as a column group. Set Sales to number, change the first measure to Sum, choose Sales and label it Sales. Build the summary. Its grand total is 24.85. Add Average of Sales to get 4.97 and Count distinct of Order ID to count six orders while preserving their leading zeros. Inspect any intersection or total to see the source records behind it.
Add a Region equals North filter and rebuild. The sales total becomes 15.9. Filters all need to match and use typed source values before date bucketing. Export a recipe to reuse column types, groups, measures, filters and sort choices with the same ordered header labels. Recipes contain labels and settings, not source records, so review labels before sharing them.
Counts, totals and missing values
Count rows includes every contributing record. Count nonblank and Count distinct skip missing values. Distinct compares reviewed typed values; text 001 and 1 differ, but explicitly converting both to numbers makes them equal. Sum, average, minimum and maximum require numbers. Missing numeric values are skipped. With no numeric values, the result is empty, shown as —. Zero is a real value and remains zero.
An empty CSV cell is missing. An optional setting also treats whitespace-only cells as missing; other text is never trimmed or case-folded. Missing grouping values form a separate group or exclude the whole record, according to your choice. Row totals, column totals and the grand total are recalculated from source records; they do not add averages or distinct counts from displayed cells. Sums use decimal arithmetic at 220 significant digits, covering the supported 100-digit inputs. Repeating averages round at that precision. No arbitrary decimal display rounding changes the stored results.
Exports and privacy
Will this change my CSV? No. It creates a separate summary. CSV output quotes all fields and can prefix possible spreadsheet formulas with an apostrophe. Disable that protection only for trusted raw data. Spreadsheet programs may still interpret CSV identifiers as numbers. XLSX stores summary values as text to preserve exact decimals and labels. Its Group definitions sheet keeps exact column-group values as JSON, with null for missing values. Convert trusted values to spreadsheet numbers yourself if needed.
The first 20 source records and first 100 contributing records are previews; contributing-record CSV includes every selected source record. Record numbers count the header as 1 and quoted multiline fields as one record. Input is bounded to 5 MiB, 20,000 data records and 80 columns. Results allow 200 row groups, 40 column groups, 1,000 intersections, eight measures and ten filters. Worker parsing/calculation stops after three seconds; XLSX export after 15 seconds and at 20 MiB. Cancel stops the current worker. Clear removes temporary data. Table data stays in your browser and is not sent to a conversion service.
Stars and feedback
Loading…
What should we improve next?
Help shape the next update to CSV Pivot & Summary. Tell us what would make it better for you.
Prefer email? feedback@tooltulip.com
Tool reference #223