How to read/parse an X9.37 file via Apex?

Question

Background

X9.37 is a standard format used in electronic check exchange (when a depository institution sends an image cash letter file as a deposit to the Federal Reserve Bank, or when the Federal Reserve Bank transfers, presents or returns an image cash letter file to a depository institution).

This file format supports both ASCII and EBCDIC encoding.

A sample file can be downloaded here.

I see all kinds of junk (special) characters when I open this file in Notepad++. I tried changing the encoding to all formats listed in Notepad++ menu, but can't seem to view the text in a human-readable format.

There is more information regarding file format transformation in Page 4 of 18 in this white paper. For your quick reference:

... generalized tools do not understand the X9.37 file format. However, they can be coerced into browsing and editing X9.37 files when they can be sensitive to the four byte field zero lengths, which very conveniently define the beginning of each X9.37 record that occurs within the overall x9 file.

...editors must understand the character set being used to represent the actual data so it can be visually displayed. The easiest way to do this is to create an editing environment that supports either EBCDIC or ASCII data. Alternatively, you can transform your x9 file to a standard character set (eg, EBCDIC) before you begin your editing process.

I couldn't quite figure out how to coerce Notepad++ into revealing the contents of the sample file.

However, I was able to view the file via X9Assist tool by X9Ware. The sample file above when viewed in X9Assist looks like so:

i.e. there is (text) data in that file that contains some header info and details related to one or more checks being transmitted (the amount, routing number, etc) which I need to extract into Salesforce along with the image of the check itself (see screenshot above for clarity).

What did I try?

I attached this file to a record in Salesforce Lightning and did something in lines of:

ContentVersion.VersionData.toString();

Above threw BLOB is not a valid UTF-8 string exception, which made sense.

So I looked at this, this, this and this to see what others have recommended in such a situation. I tried a recommendation from one of the posts there:

public static String blobToString(Blob input, String inCharset){
    String hex = EncodingUtil.convertToHex(input);
    final Integer bytesCount = hex.length() >> 1;
    String[] bytes = new String[bytesCount];
    for(Integer i = 0; i < bytesCount; ++i)
        bytes[i] =  hex.mid(i << 1, 2);
    return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}

and called it like so:

blobToString(ContentVersion.VersionData, 'ASCII');

Above printed gibberish in debug logs:

Then I tried blobToString(ContentVersion.VersionData, 'ISO-8859-1'); with similar results.

However, blobToString(ContentVersion.VersionData, 'EBCDIC'); returned ERROR: System.StringException: Encoding EBCDIC is not supported

Can someone shed some light on how such a file format can be digested in Salesforce (if at all)? Open to any workarounds as well. Thank you.

Answer 1

Your example file is apparently in "EBCDIC" encoding. Apex only supports UTF-8, which EBCDIC is definitely not compatible with; EBCDIC is an IBM mainframe spec that is mostly a holdover from the days of punch cards.

Most modern consumer platforms don't provide native support for EBCDIC. It's not that the data is gibberish, it's just not in the standard encoding used by most consumer systems in use today.

I took the liberty of downloading the file and checking out some of the data in a hex viewer (you need a hex viewer for this task), and took out the first few bytes of data (one record's worth):

00 00 00 50 f0 f1 f0 f3 e3 f1 f1 f3 f0 f0 f0 f6 
f0 f9 f1 f1 f1 f0 f1 f2 f8 f2 f2 f2 f0 f0 f4 f0 
f8 f0 f5 f2 f0 f3 f0 d5 e4 e2 40 c2 c1 d5 d2 d6 
40 d5 d9 d4 40 40 40 40 40 c6 89 99 a2 a3 40 c2 
81 95 92 40 96 86 40 d5 96 99 94 c1 e4 e2 40 40 
40 40 40

I cross referenced this with a EBCDIC table, and came up with the following output:

00 00 00 50                |  NUL NUL NUL & (Record type 50?)
f0 f1 f0 f3                |  0103 
e3                         |  T
f1 f1 f3 f0 f0 f0 f6 f0 f9 |  113000609
f1 f1 f1 f0 f1             |  11101
f2 f8 f2 f2 f2 f0 f0       |  2822200
f4 f0 f8 f0 f5 f2 f0       |  4080520
f3 f0                      |  30
d5                         |  N
e4 e2 40 c2 c1 d5 d2       |  US BANK
d6 40 d5 d9 d4             |  O NRM 
40 40 40 40 40             |       (5 spaces)
c6 89 99 a2 a3 40          |  First (1 space)
c2 81 95 92 40             |  Bank  (1 space)
96 86 40                   |  of    (1 space)
d5 96 99 94 c1 e4 e2       |  NorAUS
40 40 40 40 40             |        (5 spaces)

I could keep going, but I think you can see where this is headed. This format is a fixed-field EBCDIC/ASCII record format, with no separators between the fields or records. You could imagine this to be a very special case of the CSV format.

The field definitions, of course, are outlined in the X9 specification documents. These are available for a fee depending on which features you need to support.

You're mostly on the right track; you need to first decode to hex, then manually decode from hex to EBCDIC, convert that to ASCII, and then you'd have a mostly readable file. You still need to have a copy of the specification in front of you to determine what each field means, how long it is, possible values, etc. I only broke up the above data semi-arbitrarily based on your screenshot and intuition. I know a few of the fields are definitely wrong, but hopefully you see what you're up against.

I realize this probably isn't the answer you were expecting, so here's the TL;DR for you:

It is possible, potentially even trivial, to decode these files if you have the specs laid out before you. It'll still use a ton of CPU time, and will be painfully slow to process files this size, but it is not impossible if you're determined.