Merge CSV by Date and Effective Period

An event table tells you when an order, reading or other activity happened. A history table tells you when a price, configuration or status became effective. This tool connects those two kinds of evidence. It preserves every event and adds the historical fields only when the configured search determines one unambiguous record. It does not invent a price between changes or fetch history from an external service. The source files remain unchanged, and the downloaded result is a new table.

The default is a backward search: choose the latest historical start that is not later than the event. This is useful when calculating orders against prices that were already in force. A normal exact join answers a different question, because an order date need not equal a price change date. Appending files also cannot establish which price applied. Use this page for time-dependent lookup, and use the ordinary join tool when equality on an identifier is all you need.

Load the event table and history table separately. Each file has its own encoding and parsing confirmation. CSV and TSV values stay text, including product codes with leading zeros. Pasted tables follow the same delimiter and header review. XLSX input requires a selected sheet and data area, with explicit choices for stored values, displayed text and available cached formula results. No spreadsheet formula is recalculated. The first preview is a sample; confirming import validates the complete selected data.

Add one or more exact key pairs, such as product and warehouse, and map the event time and effective start fields. The keys may have different column names in the two files. Matching does not silently trim spaces or fold case. Empty identifiers are errors. If there really is only one entity and no key columns, explicitly confirm that all history belongs to one entity. Leaving this choice undecided blocks processing, so unrelated products cannot accidentally share a price history.

You can bring an applied current table from another workbench tool into either role. This supports a workflow in which date normalization happens first and historical lookup happens next. Keep both source date columns until you have verified the result. Once the enriched table is reviewed, set it as the current table to continue with validation or calculation. Session data survives supported site navigation, but refreshing or closing the page clears it.

Backward lookup chooses the most recent eligible past record. Forward lookup chooses the next eligible record at or after the event. Nearest lookup compares absolute distances on both sides. Allow equal timestamps is enabled initially for these searches. Turn it off only when the business rule requires a strictly earlier or later start. Nearest is a timing rule, not evidence that the selected record is commercially correct; it can choose a future configuration when you permit that mode.

The maximum distance is optional, and an empty setting visibly means no limit. Pure dates use calendar days. Instants use actual elapsed hours. These meanings are deliberately separate: a day in a named time zone can span more or less than twenty-four hours. Both tables must use the same declared time meaning. Source formats are explicit, and unzoned timestamps require a source zone. The interface language and browser location never supply a hidden default.

If past and future starts are equally close, the default result is ambiguous. You may explicitly prefer the past or future for equal distances; that policy is retained in the rules report. Two history rows at the same eligible start remain ambiguous even if one appears first in the uploaded file. Reordering a history file therefore cannot change a unique match. An eligible candidate beyond the chosen maximum distance is reported as outside tolerance, separately from having no candidate in the requested direction.

Interval mode tests membership in a start and end range. The default includes the start and excludes the end. For ranges from September 1 to September 5 and from September 5 to September 10, an event on September 5 belongs only to the second range. An overlap that covers an event creates ambiguity; no first-row preference hides it. Events in a gap between intervals remain unmatched rather than borrowing a value from outside the range.

An empty end is invalid until you explicitly choose that it means indefinitely effective. If a pure-date source uses the end as the last valid calendar day, select that interpretation so the following date becomes the exclusive boundary. This option is limited to calendar dates; it does not add a fixed day to a zoned timestamp. Ends at or before their starts are invalid. A history error with a known key blocks matching for that key until repaired, because ignoring a broken interval could produce a falsely confident result.

Consider product P with a price of 10 effective September 1 and a price of 12 effective September 5. An event on September 3 receives 10 in backward mode. An event on September 5 receives 12 when equality is allowed. An event on August 31 has no eligible past record and retains its own original fields with an unmatched status. Under nearest mode, September 3 is two calendar days from both starts and requires review by default.

The same structure works for device configurations: identify the device, map each reading time, then locate the last configuration change before that reading. A historical record may correctly serve many events. This is different from transaction reconciliation, where confirming a payment consumes its records. The lookup result never expands an event into several accepted matches. Multiple candidates stay in a separate report, allowing you to correct the history or choose an explicit policy without duplicating order amounts.

The summary separates matched, unmatched, ambiguous, outside-tolerance and error records. The full result preserves input order and contains every event. Historical column names receive suffixes without overwriting event fields. Match status, distance, effective boundaries and the selected source accompany the enriched rows. Open the source view to inspect file, sheet where available, and logical record position. Logical records are not physical text lines when a quoted cell contains a line break.

Result tabs include unresolved events, candidate details, unused history, input errors and the applied rules. Unused history is informational: it may cover a time period with no events and is not inherently wrong. Export one explicitly selected tab as CSV or TSV. Search in the preview does not silently restrict the export. Formula-prefix protection is appropriate for spreadsheet review but may change leading characters; raw text requires a separate acknowledgment. Generated files are parsed again and checked against the intended matrix.

Changing an input or rule makes the previous output stale and disables download until a fresh run finishes. Undo and redo allow you to recover settings, while saved rules omit source tables. Field names and constants can still be sensitive, so inspect a rule file before sharing it. Work runs in a cancellable local worker with time, input and candidate limits. A limit stops the operation instead of presenting an incomplete scan as a complete answer. File contents are not uploaded, although loading the website still requests static assets.