Group CSV by Column

Group CSV by Column turns a detailed table into one row for each exact combination of selected fields. An order export can become a summary by customer, region, or product category. Each metric has its own meaning: counting records, counting nonempty values, counting different values, or calculating a numerical result. The tool retains participating source locations so that a summary can be investigated instead of becoming an unexplained set of totals.

Grouping changes the level of detail. A hundred order lines may become five customer rows, and a field that is not a group key or metric does not automatically survive in the summary. The original table remains in the workflow, and participating details are available as a separate report. Decide which question the summary should answer before selecting keys. Grouping by customer and month answers a different question from grouping only by customer, even when both use the same amount field.

Choose files or Paste a table, then confirm parsing and headers. The imported values remain text, preserving identifiers such as 00123 and numbers longer than common spreadsheet precision limits. Select Group columns in the order you want the key fields to appear. A field's internal identity remains stable through supported workflow changes, preventing a renamed field from being confused with a different column that happens to occupy its earlier position.

Grouping compares the original field values exactly. It does not automatically trim spaces, ignore capitalization, or treat 001 and 1 as equivalent. Multiple key fields use a representation that preserves their separate boundaries, so values containing commas or other punctuation cannot collide through simple string concatenation. If category inconsistencies should be combined, perform an explicit value-standardization step first. Keeping that step separate makes the reason for the later aggregate visible in the workflow history.

Empty groups defaults to Keep. A record with a missing or empty grouping component remains part of a separately represented group rather than disappearing. Missing cells and empty strings are internally distinct, even though CSV export represents a missing cell as an empty field. Examine the source and empty-definition settings when a distinction is important to your analysis. Pure whitespace is not empty by default, and literal marker words require explicit configuration.

Exclude and report removes rows with empty grouping components from the participating population. Those records appear in Excluded empty groups. Their amounts do not contribute to group metrics or totals, and invalid amounts on excluded rows do not block the remaining participating groups. This is a population choice, not a repair of the excluded records. Keep the exclusion report when a downstream reviewer needs to explain why the summarized row count differs from the original input count.

The initial metric is Record count and requires no value field. Add metric creates another independently configured measure. Nonempty count counts values that do not match the empty definition in the selected field. Distinct count counts different nonempty source values, preserving strict text distinctions. Sum, Mean, Minimum, and Maximum require a numeric field and a confirmed numeric interpretation. The same input field can supply several metrics, such as both total revenue and average revenue.

Result column name gives each metric an explicit output label. Use unique, meaningful names such as order_count, valid_amount_count, amount_sum, and amount_mean. A report with several unnamed counts is difficult to interpret because their denominators may differ. The output places group fields first, followed by metrics in configuration order. Move up changes metric order without changing a metric's formula. Result names are checked so one metric cannot silently overwrite another metric's output.

Each numeric metric uses the shared number rules: source decimal and grouping separators, negative notation, permitted currency tokens, and percentage interpretation. Confirm this numeric interpretation before running. A formatted amount such as 1.234,50 requires a comma decimal and dot grouping convention. A percentage can represent its numeric percentage value or its corresponding ratio. The chosen interface language does not alter those conventions, and unrelated text columns are not converted simply because a metric uses numbers elsewhere.

The numeric engine separates source text from its parsed decimal value. Supported decimal arithmetic gives 0.3 when adding 0.1 and 0.2. It also avoids rounding long integers through binary-number conversion. Numeric metric calculations use interpreted values rather than a display-only grouping separator. Fixed output precision in a normalization step is an actual chosen transformation; if you want rounded amounts to be aggregated, apply that step and select its result column explicitly.

Take three records in group A with amounts 10, 20, and an empty field. Configure Record count, Nonempty count of amount, Sum of amount, and Mean of amount. The resulting row contains three records, two nonempty values, a sum of thirty, and a mean of fifteen. The empty value contributes neither zero nor an observation to the numeric denominator. A separate group containing only empty amounts has a blank sum and mean, showing that no valid numerical observation was available.

Now take group A with one amount of ten and group B with three amounts of twenty. The two group means are ten and twenty, but the grand mean is 17.5 because it is calculated from all four participating observations. Averaging the two displayed means would incorrectly give fifteen. Distinct-count totals also return to the original participating values: a customer appearing in two groups must not be double-counted merely because two group-level distinct counts exist.

Invalid numeric values defaults to Block complete result export. A nonempty amount such as ten dollars fails unless it matches the explicitly supported numeric convention; arbitrary words are not removed to manufacture a number. The numeric issue report identifies the record, field, original text, reason, and chosen policy. Affected metric cells do not receive a false complete-success value, and the main result cannot be applied or exported as complete while blocking numeric issues remain.

Exclude and report counts is an explicit alternative. It omits invalid observations from the relevant numeric metric and records their number and source. Those rows can still contribute to Record count, and their nonempty text can still contribute to a nonempty or distinct count. This explains why several metrics on the same field may legitimately have different denominators. Do not describe the resulting sum as covering every source record without mentioning the exclusions. Empty values remain a separate condition rather than being included in the invalid count.

Output row order can follow first appearance or sort complete keys in ascending or descending order. Sorting does not normalize the key values. Key order is based on the defined complete-key representation, not an inferred numeric type or the current language's alphabetical convention. If a specific business ordering is needed after grouping, apply the existing sorting tool to the summary using explicitly selected field rules.

Every summary row carries the source locations of its participating records. Open its source link to review the underlying details. The Participating details report also provides the retained population, and Grand totals contains each metric recalculated over that population. These totals are useful for checking whether a prior filter or empty-key exclusion had the intended effect. The grid shows only a page of rows at a time, but its page size does not restrict the aggregate calculation or the exported summary.

Export new copy offers the grouped result, numeric issues, participating details, excluded groups, direct totals, and the settings summary. The exporter quotes delimiters and multiline values and checks its output by parsing it again. Spreadsheet formula protection can affect text keys beginning with a formula-like prefix, so review its reported impact. Preserving a leading-zero key in the downloaded file does not force another spreadsheet application to display that field as text on a double-click open.

Apply to workflow makes a completed full summary available for subsequent tools. It reduces the current table to the summary level, while Undo can return to earlier details. Save rules retains grouping, metric, interpretation, empty-value, exclusion, and sorting choices. A sample run is labeled, exported with SAMPLE in its filename, and cannot stand in for the full workflow. Input, output, audit, and time budgets remain enforced. If a run is cancelled or exceeds a limit, the imported source remains available for a smaller explicitly scoped operation.