01Make imported customer labels consistent
Replace nonbreaking spaces and tidy a selected label column before matching or grouping records.
- 1
Open the guide's three-row sample and select label as the only column to clean; keep row_id unchanged.
- 2
Enable nonbreaking-space replacement, edge trimming, and repeated internal-space reduction, then inspect the preview.
- 3
Check that S1, S2, and S3 all contain North Harbor with one ordinary space and 12 characters; save a new output file.
Collapsing internal spaces changes field values. Apply it to reviewed labels, not automatically to identifiers or fixed-width text.
02Trim label edges without flattening internal spacing
Remove unwanted boundary spaces while keeping meaningful gaps between words.
- 1
Load the hidden-spaces sample from the guide and select label, leaving the identifier column untouched.
- 2
Enable nonbreaking-space replacement and edge trimming, but leave repeated internal-space reduction off.
- 3
Check that S1 retains two spaces between North and Harbor, while S2 and S3 have one ordinary internal space; export a separate copy.
Replacing a nonbreaking space with an ordinary space is itself a change. Keep the original when that character has a deliberate meaning.
03Remove repeated member records without merging different cities
Compare all relevant fields so a repeated member ID does not erase a distinct record.
- 1
Load the guide's five-row member sample and keep the original file as a reference.
- 2
Select member_id, name, and city together as the duplicate key; keep exact matching and preserve the first occurrence.
- 3
Check for four retained rows and one removed row. Keep both E01/Ada/Seoul and E01/Ada/Busan, and review the removed-row output.
Selecting only member_id would collapse the two E01 cities. The guide's Google Sheets procedure and the workspace's exact-match option can also differ on other mixed-case data.
04Keep the latest customer revision after sorting
Use an explicit date and revision order before keeping the first row for each customer.
- 1
Open customer-history.csv from the guide. Move the blank-ID row and the missing-date row to an exception sheet, leaving five eligible rows.
- 2
In Excel, sort the entire eligible table by customer_id ascending, updated_date newest first, and numeric revision largest first; export that sorted table as a new CSV.
- 3
Deduplicate the sorted CSV on customer_id, keeping the first row. Verify C101/revision 302/Bea, C102/revision 304/Dan, and C103/revision 305/Eli remain.
Keep-first deduplication does not find the latest date by itself. Sorting only one column would detach values from their original records.
05Find product IDs added or missing between exports
Compare identifier membership across two snapshots without relying on row order.
- 1
Download old.csv and new.csv from the guide. Copy only each id column's data values into the two comparison lists, one ID per line, without the header.
- 2
Use exact matching with trimming and case folding off; keep the left list labeled Previous and the right list labeled Current.
- 3
Verify that 00789 appears only in Current, 00123 only in Previous, and 00456 in both; inspect the corresponding source rows before acting.
A shared ID does not mean the whole record is unchanged. This sample changes 00456's name, and set comparison does not detect that edit or compare duplicate counts.
06Compare two lists with an explicit case and space rule
Separate missing IDs from differences caused by letter case, leading spaces, or repeated occurrences.
- 1
Copy the nonblank ID values from list-a.csv and list-b.csv into separate comparison lists, excluding the headers and preserving the leading space before A104 in List A.
- 2
Enable case-insensitive matching and leave trimming off. Check that CaseA matches casea, while the two versions of A104 remain different.
- 3
Verify that only A101 and the leading-space A104 are unique to A, and A104 and A105 are unique to B. Use the guide's Excel count formulas to check A102 separately: two occurrences in A and one in B.
The comparison tool reports unique membership, not occurrence balance. Do not delete repeated source rows before performing the separate count audit.
07Convert CSV to JSON without changing IDs or blanks
Create an array of objects while keeping the CSV field values as strings.
- 1
Load the guide's id, quantity, note sample and confirm there are three unique, nonblank headers and two data rows.
- 2
Choose JSON output with string-preserving values; leave identifiers and quantities as text.
- 3
Inspect both objects: the first id must be the string 00123 and quantity the string 4; the second quantity and note must be empty strings. Save the JSON under a new filename.
An empty string, JSON null, and a missing property are different values. The guide's optional numeric-quantity Python mode is a separate conversion, not this recipe's output.
08Convert a quoted TSV export to CSV
Change the field separator while preserving commas, literal tabs, quotes, and multiline notes.
- 1
Load example.tsv from the guide and select Tab as the input delimiter; confirm id, note, and status are the three headers.
- 2
Choose CSV output and keep all values as strings so 00123, 00456, and 00789 retain their leading zeros.
- 3
Reinspect the output as CSV: expect three data records, an empty second status, a literal tab inside the second note, and a line break inside the third note.
Replacing every tab with a comma would edit the second note. Use parsing and export, and check that the TSV follows the quoted-field convention used by this sample.
09Prepare a small order lookup file with columns in the right order
Extract just the fields needed by the next step without changing their values or matching another table.
- 1
Load january.csv from the append-versus-merge guide and confirm the headers order_id and sku with two data records.
- 2
Select sku followed by order_id as the output columns; keep both fields and their source row order.
- 3
Check the output header is sku,order_id and the rows are 00123/A01 and 00456/A02. Save the result separately, keeping the original for any later append or merge.
Extracting or reordering columns does not join product names or combine months. A later merge needs a reviewed key, and removing a required field can make that merge impossible.
10Split a CSV into smaller files without breaking multiline notes
Split by complete data records instead of physical lines in the text file.
- 1
Load the ticket sample from the quoted-newline guide and confirm three headers and two data records, even though the text occupies four physical lines.
- 2
Set the chunk size to one data record per output file and split the parsed CSV.
- 3
Check that there are two outputs: one complete T101 record with both note lines and one T102 record. Verify each file's headers before importing it elsewhere.
A line-counting split can cut a quoted field in half. Header lines are not data records, and the embedded newline must remain part of T101's note.
11Check a supplier export that opens in one Excel column
Identify the intended separator before attempting cleanup or conversion.
- 1
Open the guide's sample and read the header sku;description;price before choosing a delimiter.
- 2
Select Semicolon and inspect the parsed table: it should have three columns and two data records, with Mug, blue and Plate; small each in one description field.
- 3
Follow the guide's Excel import steps using the same delimiter and set sku to Text before loading; verify IDs 00123 and 00456 and a price total of 20.50.
The most frequent punctuation character is not necessarily the delimiter. Quoted identifiers can still be converted to numbers by Excel unless their column type is Text.
12Check long product IDs before importing them into Excel
Keep a text reference so a display change cannot hide lost identifier digits.
- 1
Load long-id-input.csv from the guide and inspect product_id as text, including the two distinct values ending in 3456 and 3457.
- 2
In Excel, import product_id as Text before it is converted to a number; use the guide's import controls and keep stock numeric.
- 3
Compare with long-id-import-expected.csv: all four IDs must match the source exactly, with lengths 15, 16, 16, and 16 in source order.
Formatting an already changed numeric ID as Text cannot recover its original digits. Use an untouched source; a plausible display or correct row count is not enough.