Removing CSV duplicates? Check blank order IDs before deleting rows

Two rows with the same order ID can describe different line items. Two rows with blank order IDs can also be separate records. If a deduplication removes more money than expected, check the excluded rows as well as the row count.

Why blank keys matter

Deduplicating on one column compares that column, even when other columns differ. Empty strings can become the same comparison key. A blank order ID does not mean “no order” or “the same order.”

Microsoft recommends checking the selected comparison columns and copying the original data before removing duplicates. This guide checks CSV strings and CSV Helper's comparison library, rather than reproducing Excel's cell types and display-format comparisons. Microsoft documentation

Reproduce the problem with six synthetic records

Download the synthetic order file. It contains a header and six data records, not customer data.

order_id,line_id,amount
O1001,L01,12000
O1001,L02,3000
O1001,L01,12000
,L03,900
,L04,4000
O1002,L05,5000

Record numbers below exclude the header. Records 4 and 5 have empty order IDs but different line IDs and amounts. O1001 also has two distinct line items, L01 and L02. For this example only, assume that the identical L01 record was accidentally sent twice. Amounts are integer Korean won (KRW); there is no currency conversion.

Preserve the original and inspect the missing keys

Make a copy before changing anything. Use the CSV checker to inspect the parsed table and column structure. Look at which fields are empty; do not assume that a structurally valid CSV has complete business identifiers.

Here, two order IDs are empty and the source amount total is 36,900 KRW. Do not delete those rows or fill both with the same invented ID. An apparently larger source total is not automatically correct either: real data may include cancellations, partial orders or repeated imports.

Compare the columns used for deduplication

In the duplicate-row tool, enable the header setting and start with whitespace and case normalization disabled. The current library compares the whole row when no key columns are selected. It keeps the first matching row; it does not decide which row is latest or correct.

The entire-row and composite-key results happen to match in this fixture, but the comparison rules differ. With order_id alone, L02 and L04 disappear as well as the repeated L01. Reducing six records to three is not proof of a successful cleanup.

The excluded amount for the order_id-only comparison is 19,000 KRW: the repeated 12,000 plus separate line amounts of 3,000 and 4,000. Inspect the excluded-row list and the first record each exclusion matched.

Choose a business key and preserve unresolved rows

Keep the blank-ID records until their identifiers can be checked against the source. Do not assume the tool automatically protects blank keys. If necessary, keep unresolved rows in a separate preserved copy while reviewing the rest.

The pair order_id + line_id gives the intended result in this synthetic example. It is not guaranteed to be unique in every business: corrected versions, reused line numbers or split shipments may require additional rules. Even identical rows can represent legitimate separate events. Confirm the business meaning before deleting them.

Whitespace and case options change comparisons, not the values retained in the output. An empty string, a space, 0 and the literal text NULL are different source representations; do not generalize this example to all of them.

Reconcile counts and amounts

For the entire-row comparison, 6 = 5 + 1 records and 36,900 = 24,900 + 12,000 KRW. This explains the exclusion; it does not establish that the deletion is correct for a real transaction.

For an optional Python check, place the downloaded file in your working directory:

import csv

with open('guide-11-orders-empty-keys.csv', encoding='utf-8-sig', newline='') as f:
    rows = list(csv.DictReader(f, strict=True))

empty = [(i, row['line_id'], row['amount'])
         for i, row in enumerate(rows, 1) if row['order_id'] == '']
print(empty)
print(sum(int(row['amount']) for row in rows))

Expected output:

[(4, 'L03', '900'), (5, 'L04', '4000')]
36900

This reads fields as strings and adds only this fixture's integer amount column. It is not an implementation of Excel's general duplicate-removal behavior. The totals are calculated separately; this guide does not claim that the browser tool automatically totals amounts.

Limits and review notes

CSV Helper cannot establish whether an order is real, cancelled or the latest version, or recover missing order IDs. Check the output against the source before using it.

Reviewed: 2026-10-19. The fixture, Python output, counts, excluded positions, integer totals and source preservation were checked locally, including comparison with the current CSV library. Excel GUI, browser-interface and download behavior were not tested.

AI assistance: AI helped draft the guide and synthetic examples. Code was executed locally. No customer files were used, and no human Excel review is claimed.