Unpivot CSV Data

Unpivot CSV Data turns selected column headings into values in a new attribute column. Their corresponding cells become values in a second new column. Identifier columns are repeated beside each observation. A monthly product report can therefore become product, month, and sales, with one row for each selected product-month cell. This arrangement is useful when a later filter, chart, or analysis expects the month to be a field rather than a separate heading.

Unpivoting is a structural operation. It does not add values together, calculate averages, or reconcile duplicate product identifiers. It also differs from transposition, which exchanges the axes of a whole matrix. An unpivot keeps chosen identifiers attached to the observations that came from the same source record. Text and numbers can coexist in the value column because the tool preserves their original text rather than forcing every observation into a numeric type.

Choose files or Paste a table, then review Confirm file parsing. Use Parse and preview to establish the file delimiter and check that the first logical record really contains the intended column names. Confirm import validates the full file. CSV and TSV are supported as UTF-8 text. Quotes, embedded commas, and correctly quoted line breaks are interpreted by the shared parser before the unpivot operation reads any cells.

A report with decorative title rows, merged spreadsheet headers, or multiple levels of headings needs an explicit preparation step. Skip known introductory logical records or supply unique headers when that accurately describes the data. The tool does not infer a hierarchy from two header rows or guess that a repeated month belongs to a different year. Use names such as 2024-Jan and 2025-Jan when those distinctions matter. A meaningful header becomes data in the output, so ambiguous input names create ambiguous attributes.

Select Keep identifier columns in the order you want them in the result. An identifier might be a product number, store, or a combination of fields. Choosing a value column as an identifier would keep it fixed and prevent it from being expanded. Leaving a real identifier out of the retained set can remove context, so inspect the planned output before running. Selecting the same field as both an identifier and a selected value field is blocked.

Selected value columns gives you a precise list of columns to expand. The ordered list also determines the order of their observations within each source record. This is appropriate when a report contains both monthly measures and descriptive fields that should not become observations. Select only the relevant month columns and review their order. A column left out of both lists is omitted from the result copy, while remaining available in the original input and history.

All other columns uses the retained identifier list as the boundary and expands every remaining field. This is convenient when a later file adds another month. It is also potentially broader than expected when a supplier adds a note or status field. The saved schema must match the current source, and a changed schema requires Confirm current schema after reviewing the columns. That confirmation explicitly accepts the current nonidentifier fields as the new expansion scope.

Attribute column name and Value column name control the two new headers. Use descriptive names such as month and sales_text when they match the content. Their names must be nonempty, distinct, and free of conflicts with existing names. The tool blocks conflicts instead of overwriting a retained identifier. At least one expandable field is required. A table with only identifiers cannot produce a meaningful long result until an observation field is selected.

Keep empty values is enabled initially. An empty monthly cell still represents a position in the original report and produces an observation with an empty value. Disabling the setting excludes those observations. The summary reports Empty cells and Excluded cells separately, making the effect visible. The empty definition can distinguish missing cells, empty strings, whitespace-only content, and exact additional markers. By default, zero, false, NULL, and N/A are preserved as values.

Decide whether a missing observation is meaningful before excluding it. A blank January value could mean that January was recorded as unknown, while a completely absent January column could mean that the reporting period was not part of the source. Dropping blanks makes both situations less distinguishable in the output. If a downstream process requires every product-month position, keep blanks and address their meaning in a later explicit validation or analysis step outside this tool.

The expected record count is the sum of selected source cells that survive the empty-value policy. With blanks retained, two source rows and two expanded columns produce four observations. Identifier fields add output columns but do not themselves increase the number of observations. This arithmetic is a useful first check when reviewing a configuration. The estimate and source mapping are also subject to row and cell limits; a small wide input can still produce a large long table.

Start with id, Jan, and Feb. The first product has id 001, Jan equal to 10, and an empty Feb value. The second product has id 002, Jan equal to 20, and Feb equal to 30. Retain id, expand Jan followed by Feb, and use month and amount for the new headers. The default result contains 001 Jan 10, 001 Feb empty, 002 Jan 20, and 002 Feb 30, in that order.

Disable Keep empty values and repeat the run. The result now contains three observations and reports one excluded cell. Identifier 001 is still attached to January; it is not expanded as an attribute. The output order remains source-record order first and selected-column order second. Repeating the same configuration on the same ordered input produces the same ordering. The text of each amount is preserved, so a value such as 0010 or pending remains exactly that text.

After applying the result to the workflow, use Filter CSV to keep a particular month or remove observations according to a reviewed text condition. Export that filtered result as a separate step when needed. An ordinary search inside the result grid is only a display filter and does not change the exported dataset. Keeping these actions explicit helps explain whether a missing observation was excluded during unpivoting or removed by a later filter.

Source mapping records the output identity, source record identity, source file and logical-record position, and original column name. The pair of source record and source column identifies the originating cell even when business identifiers are duplicated. An identifier alone is not necessarily a unique key. Retain the mapping when a reviewer needs to distinguish two rows that happen to have the same product number and month label.

The Settings and reproducibility report describes the selected mode, identifiers, value columns, and empty policy. Save rules preserves a reusable configuration, but loading it into another source still requires a compatible field structure. Stable column identities protect references within an existing workflow when display names or order change. Schema confirmation for All other columns prevents a new field from being included merely because an old rule was loaded without review.

Run and review is required for a complete result; Preview sample and estimate displays only a limited sample. Export new copy offers the long table, source mapping, settings, or a ZIP bundle. Serialization and read-back verification preserve quoted separators, Unicode, and multiline values. Formula-prefix protection is explicit and reports the cells it changes. It can alter legitimate signed text, so review the count and select the appropriate export mode for the receiving application.

If a size guard is reached, reduce the number of source records or observation columns instead of accepting a partial long table. If a destination name conflicts, choose a new name. If the source structure changed, inspect it before confirming. Cancel task terminates the active worker and leaves the source available. Rule changes mark an earlier result stale. Applied steps support undo and redo, while interface-language changes retain data without changing delimiters or type interpretation. Refreshing the page clears the session and should follow any required downloads.