The Export and Import tools allow the content of x9 data files to be directly accessed and manipulated. Export and Import are complimentary tools. Export will read an x9 file and create a CSV file with records formatted from the underlying data and images. The embedded images can be exported as TIF or alternatively converted to PNG, JPG, or GIF. Import is a complementary tool that takes a similarly formatted CSV file and creates an output x9 file. Although Import can be used to create an x9.37 file from scratch, this would be a complex effort and we instead highly recommend that Write be used instead.
X9Utilities Export
There are a variety of export formats, where the most commonly used is our fixed column layout where item data is parsed into fixed fields which will be easy to process in your application systems. You can alternatively export into the native format for the x9.37 specification that was used to create the file. These native exports are on a record by record and field by field basis. There are many command line options for these functions, so please refer to the X9Utilities Guide for more information.
Export versus ExportCsv
Export is a very flexible tool that supports a variety of output CSV and XML formats. Export can also operate against either single or multiple input files. All options are accepted via command line switches. ExportCsv is another alternative, and is different from Export in several important ways. Because of that, you need to consider your requirements and determine which of these tools will best meet your needs. Some of the major differences are:
- ExportCsv allows you do define output columns and their logical order; Export does not.
- ExportCsv allows csv column headers (row one) to be customized; Export does not.
- ExportCsv will only export a single x9 input file; Export will export multiple.
- ExportCsv accepts all parameters through an xml input file; Export takes them from the command line.
- ExportCsv will only export to csv; Export will also export to xml.
Exporting to CSV using Fixed Column Format
When a file is exported in fixed column format, there will be a single row for each item. The item record with attached addenda records are parsed into logical fields, making it much easier to access the content. The output columns will then be populated as follows:
| Column Number | Fixed Column Data Content for the Current Item |
| 1 | Record type |
| 2 | Amount |
| 3 | Item sequence number |
| 4 | Routing |
| 5 | MICR OnUs |
| 6 | MICR Auxiliary OnUs |
| 7 | MICR Epc |
| 8 | Documentation type indicator |
| 8 | Returns Acceptance Indicator |
| 10 | MICR valid indicator |
| 11 | BOFD indicator |
| 12 | Addendum count |
| 13 | Correction indicator |
| 14 | Archive type indicator |
| 15 | Credit account |
| 16 | Return reason |
| 17 | Forward bundle date |
| 18 | Return notification indicator |
| 19 | Payor bank name |
| 20 | Payor bank business date |
| 21 | Payor account name |
| 22 | Field4 parsed from the item MICR OnUs field |
| 23 | Account parsed from the item MICR OnUs field |
| 24 | Process Control parsed from the item MICR OnUs field |
| 25 | Reserved-1 |
| 26 | Reserved-2 |
| 27 | Reserved-3 |
| 28 | Image creator date from the front image |
| 29 | Image creator routing from the front image |
| 30 | Image reference key from the front image |
| 31 | Front image name (when exported) |
| 32 | Back image name (when exported) |
| 33-45 | First primary (26 or 32) or secondary (28 or 35) endorsement |
| 33 | Record type |
| 34 | Routing |
| 35 | Endorsement date |
| 36 | Item sequence number |
| 37 | Deposit account number |
| 38 | Deposit branch |
| 39 | Payee name |
| 40 | Truncation indicator |
| 41 | Conversion indicator |
| 42 | Return reason |
| 43 | Endorsing bank identifier |
| 44 | User field |
| 46-58 | Second endorsement |
| 59-71 | Third endorsement |
| 72-84 | Fourth endorsement |
| Etc | Continued for as many endorsements that exist for this item |
Exporting to CSV in Native Format
An alternative is to export into native format, where an example would be as follow:
01,03,”T”,123456780,123456780,20140810,1201,”N”,”VIEW”,”VIEW”,”A”,,,
10,01,123456780,123456780,20140808,20140810,1201,”I”,”G”,1,”X9Assist”,,”C”,,
20,01,123456780,123456780,20140808,20140810,57000000,1,,123456780,,
25,,,08777070,6,”29602722/5526″,0000010002,44000001,”G”,8,1,”Y”,01,0,”B”
26,1,123456780,20140807,44000001,,,,”Y”,0,,,
50,1,087770706,20140807,00,00,0006302,0,00,0,,,,,0,,
52,123456780,20140808,,44000001,,,,0,,,,,0000,,0,,0006302,”c:/users/x9ware2/documents/x9ware/files_Utilities/Test file with 2 checks exporter_IMAGES/Bundle_000003/Image_000007_amount_10002_front.tif”
50,1,087770706,20140807,00,00,0001865,1,00,0,,,,,0,,
52,123456780,20140808,,44000001,,,,0,,,,,0000,,0,,0001865,”c:/users/x9ware2/documents/x9ware/files_Utilities/Test file with 2 checks exporter_IMAGES/Bundle_000003/Image_000009_amount_10002_back.tif”
25,,,09777059,2,”60666044/5587″,0000010004,44000002,”G”,8,1,”Y”,01,0,”B”
26,1,123456780,20140807,44000002,,,,”Y”,0,,,
50,1,097770592,20140807,00,00,0006679,0,00,0,,,,,0,,
52,123456780,20140808,,44000002,,,,0,,,,,0000,,0,,0006679,”c:/users/x9ware2/documents/x9ware/files_Utilities/Test file with 2 checks exporter_IMAGES/Bundle_000003/Image_000013_amount_10004_front.tif”
50,1,097770592,20140807,00,00,0001865,1,00,0,,,,,0,,
52,123456780,20140808,,44000002,,,,0,,,,,0000,,0,,0001865,”c:/users/x9ware2/documents/x9ware/files_Utilities/Test file with 2 checks exporter_IMAGES/Bundle_000003/Image_000015_amount_10004_back.tif”
70,0002,000000020006,000000020006,00004,,
90,000001,00000002,00000000020006,000000004,”File Generator”,20140810,
99,000001,00000018,00000002,0000000000020006,,,
X9Utilities ExportCsv
ExportCsv provides a variation on the capabilities that are offered by the alternative Export function. ExportCsv is especially useful when you need to define specific csv output columns, or embed the export parameters within an xml control file. ExportCsv is especially useful if you need to generate export data in multiple layouts, and you want to maintain a single definition of all layouts in one place. However, if your only goal is to export to CSV using specific columns, then the easiest way to accomplish this is with the Export function using fixed columns, where you would then identify the specific columns on the command line. Please see the Export tool for more information on how to accomplish this.
ExportCsv field names
Fields to be exported are defined using their “record dot field” name. For example, 25.7 is the type 25 amount field. Fields can be selected from the following record types:
- Type 01 – file header
- Type 10 – cash letter header
- Type 20 – bundle header
- Type 25/31/61/62 – debit or credit records
- Type 50 – front image view detail record
- Type 52 – front image view data record
Special field names
In addition to the “record dot field” name format, there are several special field names that can be used for specific conditions:
| Field Name | Content |
| MicrRouting | Combination of the item 8 digit routing plus the 1 digit check it. |
| MicrAuxOnUs | Auxiliary OnUs field, which contains the check number from business checks, which can be up to 15 digits long. |
| MicrOnUs | OnUs field which is formatted as: Field4/AccountNumber/ProcessControl. |
| MicrEpc | External Processing Code (EPC) conveys special information regarding the proper handling of a check. EPC=4 identifies a forward IRD. EPC=5 identifies a return IRD. EPC=6 identifies an RCC (remotely created check). |
| MicrFld4 | Optional field 4 as extracted from the MICR OnUs field. |
| MicrAccount | Account number as extracted from the MICR OnUs field. |
| MicrProcessControl | Process Control as extracted from the MICR OnUs field. |
| MicrAmount | Item amount. |
| CheckNumber | Check number, which is first taken from AuxOnUs (when populated) and otherwise extracted from MICR OnUs. Check numbers within MICR OnUs must be at least four (4) digits in length. If your application requires MICR OnUs check numbers that are shorter than 4 digits, then you should instead write your own extraction logic using the provided fields. |
| ReturnReason | Best return reason extracted from the item addenda records. |
| FrontImage | Front image. |
| BackImage | Back image. |
Image formats
Images are typically exported into TIFF format, since that is the image exchange format. However, several other formats are available. These should be used carefully, since conversions can be imperfect, and they will always take additional time. The image formats that are available are as follows:
- tif
- png
- jpg
- gif
Image methods
In addition to the “record dot field” name format, there are several special field names that can be used for specific conditions:
| Image Method | Functionality |
| a | Absolute (fully qualified) file name. |
| r | Relative file name (contains only the base part of the fully qualified name). |
| b | Base64 basic format (the image will be embedded within the csv). |
| m | Base64 mime format (the image will be embedded within the csv). |