Verify a spreadsheet split by department: blank groups, filenames and a reassembly audit

01Agree on the deliverable before splitting

The spreadsheet batch tool reads XLSX, CSV and TSV, combines values and splits the result by a field. Its split download is a ZIP containing group CSV files and manifest.csv. It does not produce separate workbooks preserving original formatting, formulas or charts. Establish a different handoff format if those are required.

For inconsistent source headers, follow the column-name merging guide first. This exercise deliberately uses one small file without deduplication, so a missing row can be traced without confusing several transformations.

02Complete exercise: four rows, three groups and a faulty copy

The pack contains departments.csv, a deliberately wrong bad-reassembled.csv, expected.json and README. All people and IDs are fictional. Import only departments.csv initially; the other files support acceptance checks, not a combined two-CSV input.

North/West and North:West are different original departments, and 0003 has a blank Department. Keep these raw values rather than replacing both punctuation characters with underscores. The first line is the sole header; each ID must occur exactly once.

ID,Department,Name
0001,North/West,Ada
0002,North:West,Bo
0003,,Cy
0004,North/West,Dee

03First pass: preview, split and inspect every data file

Open Spreadsheet batch tools, select departments.csv and check one source with four data rows. Keep ID, Department and Name. Do not enable deduplication. Provenance columns can remain for tracing sources; they do not add business records.

Generate a merged preview and check all four IDs. Select Department as the split field and download the ZIP. Regenerate after changing inputs, mappings or settings. Extraction should reveal manifest.csv and three data CSVs, not three original-layout XLSX files; this route does not preserve workbook styling or formulas.

1. 1
   Import only the four-row departments.csv exercise.
2. 2
   Keep three columns, leave deduplication off and generate a preview with four IDs.
3. 3
   Split on Department, download the ZIP and inspect manifest.csv first.
4. 4
   Open each listed File and verify its IDs and raw Department values.
5. 5
   Start another tool run with only the three data CSVs, merge and download the full result for reconciliation.

04Second pass: reassemble from the manifest without trusting the total alone

Groups use raw values while filenames clean characters such as slashes and colons. When both names become North_West, the second receives“ (2)” to prevent overwrite. This does not merge or standardize departments. In the manifest, Group is the original value, File is the actual filename and Rows counts data rows.

First check 2+1+1=4, excluding all three headers and the manifest’s own rows. Then verify that North_West.csv contains only 0001 and 0004 with North/West, the suffixed file contains 0002 with North:West, and empty.csv retains 0003 with a blank department. Any mismatch requires source review.

For reassembly, select only those three data CSVs, excluding manifest.csv. Keep business columns, leave deduplication off, generate and download the merged result. Row order can change: compare by ID, requiring four unique IDs occurring once and matching names and original departments. Additional provenance columns do not have to equal the original business fields.

Now audit bad-reassembled.csv independently. It still has four rows, but the final 0004 has become 0001: there are only three unique IDs,0001 repeats and 0004 is missing. Reject it. This is a reference/manual acceptance check, not a claim that an unconfigured tool automatically blocks duplicates. Deleting the repeated row leaves only three rows and does not repair the loss. Rebuild from the original four rows and manifest, then check every ID again.

ID,Department,Name
0001,North/West,Ada
0002,North:West,Bo
0003,,Cy
0001,North/West,Dee

05Leading zeros at handoff: check characters before opening behavior

IDs should remain text such as 0001 in both split CSVs and the reassembled output. Inspect raw CSV characters in a text editor, then import the ID column as Text in the recipient’s spreadsheet application. Double-clicking a CSV can trigger numeric conversion.Microsoft’s leading-zero guidance explains these import differences.

If the CSV still contains 0001 but the grid displays 1, correct the import type. If the source CSV already contains only 1, recover from an authoritative original; display formatting cannot infer the lost zero count. Include the original CSV, split ZIP, manifest and a dated review note stating which files were reassembled.

For inconsistent headers, start with merging by column names; for structural conversion, see CSV/JSON checks. Editorial acceptance exercised both tool languages and reread actual split and merged downloads. It does not replace acceptance in the recipient’s Excel environment or review of real personnel data.

06Handle oversized or surprising groups

Current limits include 30 files, 10 MB per file, 50 MB total, 50,000 data rows and 200 split groups. Unexpected group counts can reflect spaces, different capitalization or outdated labels. Resolve their meaning before normalization; similar filenames cannot make that decision.

Processing runs in the browser, where available memory also matters. Validate the rules on a small sample, then reconcile the full batch. If processing fails, keep the original and regenerate after resolving the error. A partial result is not an accepted delivery.