Validating CSV Batch Files

Given [[Input File]] and [[Report Date]]:

1. Parse filename → extract date and sequence number.
2. Parse file → split into first row (header), last row (trailer), and data rows (everything in between).
3. Run all format checks (Workflow Step 2) — auto-fix silently, log each fix.
4. Run all data checks (Workflow Step 3) — for each discrepancy, pause and ask user to choose among proposed options or enter free text.
5. Apply user's chosen value to the working copy (never touch original input).
6. Repeat validation from Step 2 until zero issues remain.
7. Rename file to match [[Report Date]] with .CSV extension, present final download link.

Maintain two things in memory throughout the session:

• original: the untouched original filename + file contents (never overwritten).
• working copy: the current amended version, updated only after user confirms a value.

Progress:
- [ ] Step 1: Parse filename and extract report date + sequence number
- [ ] Step 2: Run format checks (auto-correct, no user input needed)
- [ ] Step 3: Run data checks (ask user for each discrepancy)
- [ ] Step 4: Apply confirmed corrections to working copy
- [ ] Step 5: Re-run Steps 2–3 on working copy (repeat until clean)
- [ ] Step 6: Rename/finalize file, present download link

Step 1: Filename Parsing

• Extract embedded date and sequence number from filename per spec pattern.
• Compare filename date to [[Report Date]].
• Confirm extension is exactly .CSV (uppercase), not .csv or other case variants.

Step 2: Format Checks (auto-correct, no confirmation needed)

These are pure formatting/syntax issues — fix silently and log what was changed:

• File extension → force to .CSV.
• Filename date mismatch vs [[Report Date]] → do not auto-rename yet; hold until all data issues resolved (renaming is final step).
• Service ID casing → uppercase every character.
• Total charge amount format → exactly 2 decimal places.
• File creation/closure time → must be valid HHmmSS (00-23 / 00-59 / 00-59); if malformed but unambiguous (e.g., missing leading zero), auto-correct; if ambiguous, escalate to Step 3 as a data issue.

Step 3: Data Checks (always ask user before changing)

For each issue found, present:

Issue: <description>
Field: <row/column reference>
Current value: <value>
Proposed value: <system-computed correct value>
Options:
  [1] Accept proposed value: <value>
  [2] Enter custom value: ____

Checks to run, in order:

First row (header):

• File creation date == [[Report Date]] == filename date.
• File creation time is valid HHmmSS.
• Sequence number in header == sequence number in filename.

Data rows:

• Exactly 22 fields per row (flag rows with more/fewer; ask user how to split/pad/trim).
• Record dates fall in the month prior to [[Report Date]]'s month.
• Record type O: start date == 1st of month; end date == blank.
• Record type R: start date == 1st of month; end date == last day of that month.

Last row (trailer):

• Sequence number == filename sequence number.
• File closure date == [[Report Date]] == filename date.
• File closure time is valid HHmmSS and >= file creation time from first row.
• Record count == actual count of data rows.
• Total charge amount == sum of all data row charges, formatted to 2 decimals.

Step 4–5: Apply and Reiterate

• After every user decision, update the working copy only (never the original).
• Re-run the full check pipeline (Steps 2–3) against the updated working copy.
• Continue until a full pass produces zero format and zero data issues.

Step 6: Finalize

• Rename working copy file to the spec-compliant filename (matching [[Report Date]], correct sequence number, .CSV extension).
• Present a download link and a short summary of all changes made (auto-fixes + user-confirmed corrections).

Example 1: Service ID casing (auto-fix, no user prompt) Input: data row contains SVCID: "abc123xz" Output: Silently corrected to "ABC123XZ"; logged as a format fix in the final summary.

Example 2: Record count mismatch (ask user) Input: Trailer row states RECORD_COUNT=150, but actual data rows = 148. Output:

Issue: Record count mismatch
Field: Last row, record count
Current value: 150
Proposed value: 148
Options:
  [1] Accept proposed value: 148
  [2] Enter custom value: ____

Example 3: Record type O with non-blank end date Input: Row has TYPE=O, START=2024-03-01, END=2024-03-31 Output:

Issue: Record type O requires blank end date
Field: Data row (line 45), end date
Current value: 2024-03-31
Proposed value: (blank)
Options:
  [1] Accept proposed value: (blank)
  [2] Enter custom value: ____