Product Spreadsheet Cleanup for Store Owners: Clean, Check, Repeat

Start with a copy of the supplier file

Use this when the same product-export columns need formatting each week. Keep the original export, note its supplier and date, and try the rules on a copy before touching a live store import.

OpenRefine runs on your computer and is used through a browser, as its running guide explains. Follow the official installation instructions for your operating system. This local cleanup does not require a paid AI account.

Original practice data. The files below contain fictional supplier products and amounts. The expected file shows the result of the two stated formatting rules; it is a comparison target, not a ready-to-import store catalog.

1. In OpenRefine, create a project from store-products-before.csv.
2. In the import preview, use the first line as the header and check the comma separator, quotation marks, and encoding.
3. Keep identifier fields as text. For this exercise, turn off Attempt to parse cell text into numbers so a SKU such as 000101 retains its zeros. OpenRefine’s data-type guide describes this setting.
4. Check for 12 rows and the seven expected columns before starting. The supplied file includes a comma inside one name and quotation marks inside another; each name belongs in one cell.

Decide what the routine is allowed to change

For this exercise, trim leading and trailing whitespace from product_name. For category, trim the ends and convert letters to lowercase. Lowercase is the chosen practice convention; use your own catalog’s approved labels in a real task.

• Preserve: row order, row_id, supplier, sku, unit_price_usd, and stock_qty.
• Review: duplicate supplier/SKU combinations, missing categories, and conflicting prices.
• Confirm separately: whether different labels mean the same category and whether similar names describe the same product or variant.

Write these decisions down before saving an operation. A tidy name or matching SKU alone does not settle product identity across different suppliers.

Clean two columns and inspect the preview

From a column’s menu, choose Edit cells → Transform… and use GREL. The cell-editing guide explains the transform window and preview. Clear any active filters first so the intended rows are in scope.

1. Trim product names

On product_name, preview this expression. Confirm that the name’s content, including punctuation, is preserved.

if(value == null, value, value.trim())

2. Standardize category casing

On category, use the next expression. The GREL reference documents trimming and lowercase conversion. Keep blank categories unresolved.

if(value == null, value, value.trim().toLowercase())

Compare the practice rows

Quotation marks in this display make boundary spaces visible. In the CSV, quotation marks also protect embedded commas and quotes; do not split the file by commas manually.

What should stay on the exception list?

For a larger file, agree on a key such as supplier plus SKU with the data owner, then review repeated keys and their variants. This exercise preserves all rows so formatting checks can be completed without silently resolving those decisions.

Check the whole export, not just the visible rows

Remove active filters and facets before exporting the full practice file. OpenRefine’s export guide notes that many export choices use the current view. Compare your CSV with store-products-expected.csv by row ID.

• All row IDs, suppliers, and SKUs still match, including leading zeros.
• Every price and quantity matches its original row; unchanged totals alone cannot prove this.
• The category counts are 6 desk accessories, 2 lighting, 3 storage, and 1 blank.
• The arithmetic sum of price × quantity remains 1,254.00 in the fictional USD fields. It includes both conflicting rows and is not validated inventory value.
• The CSV’s embedded comma and quotation marks still occupy the intended cells.

A matching row count does not clear the exception list. Resolve the conflicting records and missing category separately before preparing a platform-specific import.

Save the checked operations for the next export

After reviewing the sample, open Undo/Redo → Extract…, select the two formatting operations, and save the resulting JSON. On a new project with matching columns, use Undo/Redo → Apply… to reuse them. The official reuse instructions describe this sequence.

Keep the rules’ purpose and expected input columns beside that file. Before reapplying them, check that headers, meanings, and category conventions have not changed. Recheck identifiers, totals, and exceptions on every new export.

Save your reviewed operations as product-formatting-operations.json in your own workspace. Keep exception decisions in a separate note so a decision about one week’s price conflict is not accidentally applied to next week’s data.

Where can AI help with this routine?

Use AI to propose a plan from column definitions and a small non-confidential sample, or to explain a formula before you preview it. Keep product identity, missing values, and conflicting prices subject to review. Any model or subscription you choose is paid through your own account.

TASK: Help plan a repeatable product-spreadsheet cleanup. Propose rules for review; do not rewrite or delete records.
AUDIENCE: A store owner preparing a supplier export.
COLUMNS: [Names, meanings, and data types]
IDENTITY RULE: [How a supplier and SKU identify a product or offer]
APPROVED CATEGORY LABELS: [Your store's labels, if available]
SAMPLE VALUES: [A small non-confidential sample, not customer/order data]

Return a table: observed issue | proposed rule | fields affected | before/after example | what a person must confirm.
Separate formatting rules from decisions about product identity, category meaning, missing facts, and conflicting prices. Preserve leading zeros, row IDs, variants, prices, quantities, and blank values unless I approve a separate rule.
Never assume two similar names are the same product. Flag duplicate candidates for review. Do not invent missing categories or choose which price is correct. Identify any assumptions that need checking against the supplier's source.
End with a checklist for row counts, IDs, blank fields, and unchanged numeric totals. Any suggested formula must be previewed on a copy in the chosen tool before use.

For a reusable instruction pack, see how to package repeated tasks. If the next job is writing the cleaned products’ descriptions, use the store-owner product-writing workflow with verified specifications.

Common questions

Will this remove duplicate products?

The exercise keeps duplicate candidates for review. Confirm the product key, variants, and correct record with the supplier before approving a separate deduplication rule.

Can I apply these rules to a Shopify or marketplace import?

Use them to prepare a working file, then check your platform’s current required columns and formats. The practice CSV has teaching fields and unresolved exceptions, so it is not a platform import template.

Do I need to pay for AI to clean these columns?

No model call is needed for the two fixed formatting rules. AI is optional for planning or explanation, and any chosen service’s charges apply to your own account.

What if next week’s supplier changes the headers?

Review the new file before applying the history. Map each rule to the correct column and confirm its meaning; keep the new source unchanged until you have checked the result.