Parsing and Reconciling a Fixed-Length File Interface

Goal

You parse a fixed-length message file according to its layout, verify integrity with the trailer, and complete one full file-interface cycle: encoding conversion, completion flag, retention, and reconciliation.

Why it matters

File integration looks outdated, but it is still the best for bulk processing and reconcilability. The problem is that its way of failing is quiet. If you process a file cut off during transfer as is, only half is reflected with no error, and you discover that at month-end closing as "the numbers do not match." If the completion flag convention exists on only one side, the batch processes nothing every day while raising no error. Silently doing nothing is discovered the latest. Trailer verification and reconciliation are the devices that make that quiet failure loud.

Steps

1. Read /opt/lab/fixtures/eai/file/layout.md and create /root/f/layout.csv. The first line is seq,field,start,length,type. start is a position counted from 1, and sort in seq order.
2. Create /root/f/parse.sh. It takes one argument (a data file) and outputs only the data records that start with D, with the 6 fields below separated by pipes (|).

   SALE_DT|CUST_ID|CUST_NM|PROD_CD|QTY|AMT
   

   Strip leading and trailing spaces from character fields, and strip leading zeros from numeric fields too. (Do not output REC_TYPE.) Save the result of running it on /opt/lab/fixtures/eai/file/SALES_20260801.dat to /root/f/parsed.txt.
3. Create /root/f/verify.sh. It takes one argument (a data file) and exits with code 0 if the trailer's count and amount total match the data records; if they do not match, it prints the reason on the first line and ends with a non-zero exit code.
   • SALES_20260801.dat → must pass
   • SALES_20260802.dat → must fail
4. Convert /opt/lab/fixtures/eai/file/SALES_EUCKR.dat to UTF-8 and save it to /root/f/SALES_utf8.dat. After conversion, the Korean text must read correctly.
5. Create /root/f/pick.sh. It takes one argument (a directory) and outputs, one per line, only the file names for which a .ok flag file exists alongside a .dat file. Do not output the .ok files themselves.
6. Create /root/f/csvcheck.py. It takes one argument (a CSV file) and prints one line rows=<데이터 행수> fields=<헤더 필드수> bad=<필드수가 다른 행수> (the number of data rows, the number of header fields, and the number of rows with a different field count). It must correctly handle commas and line breaks inside quotes. Save the result of running it on /opt/lab/fixtures/eai/file/orders_quoted.csv to /root/f/csvcheck.txt.
7. Retain SALES_20260801.dat under /root/f/archive/20260801/ compressed with gzip. It must be the original file name with .gz added.
8. Create /root/f/recon.csv. The first line is item,source,target,diff,result. Compare /opt/lab/fixtures/eai/file/SALES_20260801.dat (source) with /opt/lab/fixtures/eai/file/SALES_20260801.csv (target) and fill in two rows, a count row and an amount row. result is OK if they match and NG if not.

Notes

• Cutting by bytes: cut -b 1-8, by characters: cut -c 1-8
• Encoding conversion: iconv -f EUC-KR -t UTF-8 입력 > 출력 (the placeholders are the input and the output)
• Stripping leading zeros: $((10#$값)) or sed 's/^0*//' (beware of an empty string when all zeros) (the placeholder is the value)
• Use Python's csv module for CSV parsing. If you split by hand, you get quote handling wrong.
• Common mistake 1: counting the header (H) and trailer (T) lines as data.
• Common mistake 2: cutting fields containing Korean text by characters, so the digit positions drift.
• Common mistake 3: including the .ok file itself in the list of files to process.

Organize the layout definition

Read /opt/lab/fixtures/eai/file/layout.md and create /root/f/layout.csv. The first line is seq,field,start,length,type. start is a position counted from 1, and sort in seq order.

Positions are counted from 1. The next field's start position is the sum of the previous fields' lengths plus 1. Check that the end of the last field matches the total record length.

Fixed-length parser

Create /root/f/parse.sh. It takes one argument (a data file) and outputs only the data records that start with D, with the 6 fields below separated by pipes (|).

SALE_DT|CUST_ID|CUST_NM|PROD_CD|QTY|AMT

Strip leading and trailing spaces from character fields, and strip leading zeros from numeric fields too. (Do not output REC_TYPE.) Save the result of running it on /opt/lab/fixtures/eai/file/SALES_20260801.dat to /root/f/parsed.txt.

In cut, -b (by bytes) and -c (by characters) differ. When Korean text is mixed in, this difference changes the result. You also have to decide how to handle the leading zeros of numeric fields.

Trailer verification

Create /root/f/verify.sh. It takes one argument (a data file) and exits with code 0 if the trailer's count and amount total match the data records; if they do not match, it prints the reason on the first line and ends with a non-zero exit code.

• SALES_20260801.dat → must pass
• SALES_20260802.dat → must fail

Verification must be done before processing. If you do it during processing, half is already reflected. You must look at both the count and the total.

Encoding conversion

Convert /opt/lab/fixtures/eai/file/SALES_EUCKR.dat to UTF-8 and save it to /root/f/SALES_utf8.dat. After conversion, the Korean text must read correctly.

If a received file looks garbled, suspect the encoding first. In domestic external integration, an encoding where Korean characters take 2 bytes is still used.

Select by completion flag

Create /root/f/pick.sh. It takes one argument (a directory) and outputs, one per line, only the file names for which a .ok flag file exists alongside a .dat file. Do not output the .ok files themselves.

A file without a flag may still be in transfer. Take the directory as an argument and output only the files to process.

CSV interface validation

Create /root/f/csvcheck.py. It takes one argument (a CSV file) and prints one line rows=<데이터 행수> fields=<헤더 필드수> bad=<필드수가 다른 행수> (the number of data rows, the number of header fields, and the number of rows with a different field count). It must correctly handle commas and line breaks inside quotes. Save the result of running it on /opt/lab/fixtures/eai/file/orders_quoted.csv to /root/f/csvcheck.txt.

In CSV, commas and line breaks can appear inside quotes. Simple splitting gives the wrong number of fields. Use a standard parser.

Retention after processing

Retain SALES_20260801.dat under /root/f/archive/20260801/ compressed with gzip. It must be the original file name with .gz added.

Split by date directory so hundreds of thousands of files do not pile up in one directory. Text compresses very well.

Write the reconciliation sheet

Create /root/f/recon.csv. The first line is item,source,target,diff,result. Compare /opt/lab/fixtures/eai/file/SALES_20260801.dat (source) with /opt/lab/fixtures/eai/file/SALES_20260801.csv (target) and fill in two rows, a count row and an amount row. result is OK if they match and NG if not.

It is easy to feel reassured if only the count matches, but if one record is missing and another is inserted twice, the count still matches. Look at the total too.