ToolFlowKit
Home / Resources / Template
Reusable material / template

Store product spreadsheet cleanup kit

Practice a repeatable supplier-CSV cleanup with original data, an expected result, two expressions, and a review checklist.

What is included

A 12-row fictional supplier CSV, an expected formatting result, GREL expressions, exception notes, and a reusable review checklist.

The CSVs contain fictional supplier products, leading-zero SKUs, a missing category, and conflicting duplicate candidates. The expected CSV applies two formatting rules and retains those exceptions for review.

Practice supplier export ↓CSV · 12 fictional rows before cleanupExpected formatting result ↓CSV · same rows, IDs, prices, and quantitiesWorkflow and review checklist ↓Markdown · steps, expressions, AI planning prompt, checks

Use the template

  1. Save the Markdown file and keep a copy for each task.
  2. Replace the bracketed fields with information for that task.
  3. Retain source links, limits, and open questions with the result.

Use a text editor or a Markdown editor. If you use the material with an AI service, its account requirements and charges apply to your own setup.

Download .md file ↓

Read or copy the file

Reusable structure
# Product spreadsheet cleanup for store owners

ToolFlowKit original practice kit | v0.1 | Prepared 2026-10-20

## The recurring job

Another supplier export arrives with inconsistent categories and spaces around product names. Keep the formatting work repeatable, while leaving product identity and conflicting values for review.

Use the guide at https://toolflowkit.com/workflows/clean-spreadsheet with the two accompanying CSVs. The site is being prepared locally; use your local site address before public release.

## Files

- store-products-before.csv: 12 fictional supplier records before cleanup.
- store-products-expected.csv: expected result of the two formatting rules below.
- This Markdown file: instructions, exception list, review checks, and an optional AI planning prompt.

The products and amounts are original teaching data. Expected results follow the stated rules. Neither CSV is a ready-to-import store catalog.

## 1. Keep an original and import a practice copy

Install and launch OpenRefine using its official instructions. Its basic local cleanup works on your computer through a browser. No AI model call is needed for these two formatting steps.

Create a project from store-products-before.csv. Check the header, comma separator, quoting, and encoding in the preview. Keep all fields as text for this exercise: turn off "Attempt to parse cell text into numbers" so SKU 000101 retains its leading zeros.

Expect 12 rows and these columns:

row_id, supplier, sku, product_name, category, unit_price_usd, stock_qty

One name includes a comma and another includes quotation marks. They must remain in a single cell each. Use a CSV-aware importer, not a manual comma split.

## 2. Approve the scope

Change only:

- product_name: trim leading and trailing whitespace.
- category: trim leading and trailing whitespace, then lowercase letters.

Preserve all rows, their order, row_id, supplier, sku, prices, and quantities. Lowercase categories are a practice convention; select the proper labels for your real catalog before adopting the rule.

## 3. Preview the transformations

Clear active filters. In each selected column's menu, choose Edit cells > Transform... with GREL selected.

For product_name:

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

For category:

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

Inspect the preview before applying each expression. Preserve punctuation and blank categories. This routine does not supply missing facts or resolve duplicate products.

## 4. Keep the exceptions

- R05 and R06: North / 000105 appears twice, with unit prices 6.50 and 7.00. Keep both and confirm the applicable record with the supplier.
- R01 and R07: SKU 000101 appears under different suppliers; R07 is an XL product. Do not merge by SKU alone.
- R10: the category is blank. Ask for an approved label before using an import format that requires one.

## 5. Export and reconcile

Clear active filters/facets for a full export. Export CSV and compare by row_id with store-products-expected.csv.

- [ ] 12 rows retained, with the same row IDs and order.
- [ ] Suppliers and SKUs unchanged, including leading zeros.
- [ ] Every price and quantity matches its original row.
- [ ] Quantities sum to 110, before and after.
- [ ] Categories: 6 desk accessories, 2 lighting, 3 storage, 1 blank.
- [ ] R05 and R06 remain separate, with conflicting prices visible.
- [ ] Arithmetic sum of price times quantity is 1254.00, before and after. This includes both conflicting records and is not validated inventory value.
- [ ] Embedded commas and quotes still occupy the correct cells.

Matching totals are a cross-check, not proof that individual cells are correct. Resolve exceptions separately before any live catalog import. Check the platform's current required headers and formats.

## 6. Save the reviewed routine

In your project, use Undo/Redo > Extract... and select the two reviewed formatting operations. Save the JSON as product-formatting-operations.json in your own workspace.

Next week, inspect the new file's headers and meanings first. In a matching new project, use Undo/Redo > Apply... to paste your saved JSON. Repeat the export checks and keep fresh exception notes. A rule reviewed on one file does not settle another file's missing facts or conflicting records.

## Optional AI planning prompt

Use a small non-confidential sample, not customer or order data. Keep credentials outside the prompt. Any AI account or service charges are your own.

```text
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.
```

## Your next run

- Source file/date: [Record]
- Supplier: [Record]
- Approved fields/rules: [Record]
- Input and output row counts: [Record]
- Identifier and value checks: [Record]
- Exceptions and owner: [Record]
- Operation file and tool version: [Record your actual local setup]
- Human review: [Record]

## Official documentation

Checked 2026-10-20. Follow the current instructions for your installed version.

- Installation: https://openrefine.org/docs/manual/installing
- Local operation and history reuse: https://openrefine.org/docs/manual/running
- Project import: https://openrefine.org/docs/manual/starting
- Data types: https://openrefine.org/docs/manual/exploring
- Column transforms and preview: https://openrefine.org/docs/manual/cellediting
- GREL functions: https://openrefine.org/docs/manual/grelfunctions
- Exporting: https://openrefine.org/docs/manual/exporting

License for ToolFlowKit's original kit and practice files: CC0 1.0 Universal. https://creativecommons.org/publicdomain/zero/1.0/

Use it in a workflow

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