2

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:

enter image description here

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:

enter image description here

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.

3

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.

2
  • Thank you! You interpreted correctly that this is a fixed-field format. I created a new Custom Settings object with HEX to EBCDIC to ASCII mapping based on ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/… to resolve the input. Probably my last question: Embedded in there somewhere around position 370 (length = 18,628), is an image of the check in binary format. How can I extract it so I can save as an attachment in Salesforce? – Lightning Evangelist Mar 27 '20 at 12:37
  • @LightningEvangelist If you can identify the positions, you can use substring on the string you got from convertToHex, then convert that back with convertToHex. I presume the bytes are an actual binary file, so no further changes should be necessary (i.e. do not translate from EBCDIC to ASCII). – sfdcfox Mar 27 '20 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.