Export to CSV and XML

Applies to X9Vision?	Applies to X9Validator?	Applies to X9Assist?	| |	Applies to X937?	Applies to ACH?	Applies to CPA005?
No	Yes	Yes	| |	Yes	Yes	Yes

Export can automatically limit the scope of the exported data in several ways:

• You can specify a range of records to be applied to the export by entering the low and high record numbers. This will limit the export to a certain part of the file (for example, just one bundle or a group of items).
• You can specify that the export should be limited to certain record types (for example, only check detail records).
• You can combine these two selection capabilities (for example, only export the check detail records within a single bundle).

The input filename can be optionally inserted into the first column of the CSV export file. This option is helpful in several situations. First when you are exporting information from various files over a period of time and you need a mechanism to remember and record the origination source. Second is when you are exporting data from multiple files in a single export run, and similarly need to identify the file source for each exported row.

Single Versus Multiple File Exports

Most typically, a single file is being exported. In this case, the file that is exported will be the file that is currently loaded into the viewer, hence there is no need for a file selection dialog.

However, the export facility also allows you to select multiple input files to be exported in a single operation. This is a powerful function since it can read data from a large number of files to create your desired results. These capabilities should be used cautiously, since these exports can run for an extended period of time. This is especially true when there are a large number of files that are selected, when the files themselves are very large, or when the input files exist on external servers and must be read across a network.

Quoted Fields

Your CSV file can be created on either a “standard” or “quoted” basis.

• Our standard CSV format will only enclose a field within quote marks when it contains non-numeric data. Additionally, fields that are quoted will be trimmed to remove leading and trailing spaces. The result is that only the actual data remains.
• Our fully quoted CSV format will write all fields in quoted form and will preserve the leading and trailing spaces within each field. This format can be used if you need your CSV file to contain all data from each field and not be truncated in any fashion.

CSV Tools

You can use exported files as input to other applications such as Excel or your proprietary application systems. Export is an excellent tool to allow you to create CSV files that are shareable and can be used in a large variety of ways.

However, although Excel is a great spreadsheet tool, it does not fare well when used against CSV input. Because of that, X9Ware has developed our own Csv Editor, which is part of our X9Validator (AchValidator) and X9Assist (AchAssist) products. Alternative tools are Libre Office, NotePad, and NotePad++. These tools include search facilities allowing you to find data on a string basis, which can be very helpful when doing research.

Export Layouts

This table represents the available export formats with their attributes and common usage.

Description	Images Included?	Importable?	Selected Record Types?	Comments
(1)	CSV in native record format.	No	Yes, but only if the images are drawn as proxies since the actual images are not exported along with the data, and then also only when all record types are exported.	Yes	This format contains the field values within the selected record types per the file that is currently loaded and the associated rules specification. It provides a full representation of the data content for the selected record types, and a complete data representation of the entire file when all record types are selected. A common usage is to limit the export to certain record types based on need. For example, the export might be limited to the type 01 file header, the type 25 check records, and the type 26-28 addendums. The output line numbers will match back to the input file when all record types have been selected.
(2)	CSV in native record format with images.	Yes	Yes, but only when all record types have been exported.	Yes	This format contains the field values within the selected record types per the associated rules standard. The output line numbers will match back to the input file when all record types have been selected. This export file can be modified using various standard editor tools and then imported to create a new file which has been changed per user specific requirements. For example, the order of certain record types within the file can be changed; records can be removed; individual fields can be modified. Repair can also be run after the file is imported to correct trailer records when desired.
(3)	CSV parsed items into fixed fields.	No	No	Yes	Items are parsed into individual fields and exported into a fixed column format. This format can be easier to process since the items are populated on a standard basis into fixed columns. This item format is constant and does not vary regardless of the file type (forward presentment or returns) and the associated rules standard (x9.37 versus x9.100-180). Data fields for non-item groups (file headers, cash letter headers, bundle headers, etc) can be optionally included and will be presented in their native format.
(4)	CSV parsed items into fixed fields with images.	Yes	No	Yes	Items are parsed into individual fields and exported into a fixed column format.
(5)	CSV record groups into variable columns.	No	No	Yes	Output is constructed on a record group basis, where each group is the owner record type with all attached subordinate types. This format can be easier to parse since the output records for each group are concatenated into a single string which eliminates the need to separate the rows into record groups in your parser. This format has the further benefit that all output fields are present. Note that record types can be excluded and will not appear within the output. For example, you can exclude record type 50-52 if that data is not needed.
(6)	Tiff tag information.	No	No	No	This export format provides a vision into the tiff tags that are present for each image and is useful when performing a detailed image analysis for a specific originator or capture system.
(7)	Record data.	No	No	Yes	Records are exported in their text (txt) form, which provides access to their record data (eg, typically 80 bytes long) in the exact format as present on the input file. When using this export format, there is an option to append the record number as either a suffix or prefix to the written data. Appending the record number allows you to sort the data on various fields and still be able to resort the records into their original order. The record number also allows you to trace every record back to the original location within the original file.
(8)	XML.	Yes, in Base64 encoded format	No	Yes	Export to XML builds an output xml file that can be processed by other xml enabled applications. Please advise if you have requirements for this functionality and we would be glad to work with you on building new xml formats for specific vendor applications.
(9)	Errors.	No	N/A	N/A	Errors for the currently loaded file are exported and shared with others. All selection criteria applies (record number range and record types). The export can be limited by type (data or image) or severity (error, warn, and info). Output is in a fixed format which can be easily analyzed.
(10)	Use cases.	No	N/A	N/A	Use cases which can be used by Make and Scrub when creating test data. This export facility can be used in several manners. First is to create a use case file from a single file. Second is to merge the use case content from the current file into an aggregated use case file that is being constructed using data from a series of files. When that is done, the additional files are added one at a time. Duplicate use case entries will be removed and only new use case entries will be added to the aggregated use case file.

Type 52 Images (Applies to X9)

Export will insert the name of the each image file into its corresponding type 52 image view data record. The name is stored into field 5.19, which is normally houses the image data. By putting the check image file name into field 52.19, Export is able to logically associate each image with its type 52 record. Note that when using the x9.100-180 standard, images are stored in field 52.27, which is fully supported by this process.

When the type 52 record is exported, the image field is updated to contain one of the following values, subject to the selected options:

• Absolute file name, which contains the drive and path (folder) of each image file along with the actual file name. It is recommended that you use absolute file names since they fully describe the output file location. Absolute file names are required when the exported file is used by certain internal functions such as Generate.
• Relative file name, which contains the base file name only (it does not include the path). Relative names are useful when the exported CSV and images will be transported to other environments or systems.
• Base64-basic encodes the image using base64 and inserts the resulting string into the image field. This eliminates the external storage of the image in the file system and can simplify access to the image data. Base64 image exports run substantially faster than exports into the file system, since the operating system overhead to update the file system is eliminated. Image size is typically increased by 30-40 percent when using this encoding format.
• Base64-mime is similar to base64-basic encoding, just based on the MIME format.

Exporting Images (X9 Files Only)

When exporting images, you will be directed to select your output image folder. Export will then create a new sub-folder for each file within this high level folder. This folder creation applies for the export of just one file, to make the process consist with multi-file export.

Additional sub-folders are then created for each bundle using the record number of the type 70 bundle record. This has two advantages. First that the created bundle folder name will be unique and can be directly associated with the input file. Second that it allows the bundle folders to remain in ascending sequence for the overall file.

Output Image Folders (Applies to X9)

A high level output folder is provided to the export images function. A new subfolder will be created within the selected folder using the current file, which will then own a series of bundle level folders. Each bundle folder is named using the record number for that specific bundle, which is unique and preserves the relative position of the bundle within the file. This common file level approach supports either single or multi-file export.

Export includes an option to automatically clear an existing image folder, and it is highly recommended that this default is selected. If an output folder exists which is not cleared, then new bundles and image files will be created and merged into that folder structure which may overwrite existing files and can result in a confusing series of files and folders.

Type 52 Image Records (Applies to X9)

Export will insert the name of the each image file into its corresponding type 52 image view data record. The name is stored into field 5.19, which is normally houses the image data. By putting the check image file name into field 52.19, Export is able to logically associate each image with its type 52 record.

When the type 52 record is updated, the check image file name can be stored on either an “absolute” or “relative” basis. Absolute file names will contain the drive and path (folder) of each image file along with the actual file name, while relative file names do not include their path. It is recommended that you use absolute file names since they fully describe the output file location. Absolute file names are required when the exported file is used by certain internal functions such as Generate. However, you can also elect to use relative file names, which would allow you to transport the CSV and images to other folder structures and still be able to import the results.

Export as Items into Fixed Columns (Applies to X9)

Items can be parsed into logical field content and then exported into fixed columns. This format can be easier to parse since the location of individual data columns will be fixed which can simplify your subsequent parsing of this data. Specifically, the type 25 and type 31 records are parsed into logical items that will contain the following columns:

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

Export as Native Record Format

Native format follows the current rules pecification. For example, an input file encoded per the x9.37 DSTU specification will be exported per the record layouts that are defined by that standard. You must reference the associated file specification to obtain a list of the fields that are exported by record type. These fields will be the same as displayed within our file viewer. This export format has the advantage that it covers all record types and all fields.

When using this export format, there is an additional option to append the record number as either a suffix or prefix to the written data. Appending the record number allows you to sort the data on various fields and still be able to resort the records into their original order. The record number also allows you to trace every record back to the original location within the original file.

Export as Groups into Variable Columns

Records can be exported as groups and not individual record types. A record group consists of the owner record type (for example, a type 25 or type 31) followed by all records that are attached to that owner. Although the concept of record groups only applies to items, it also logically applies to other record types when they are the owner of type 68 user records. For example, a record group might consist of only a single type 01 file header, but it could also consist of a type 01 file header followed by two type 68 user records.

Record groups are exported as a CSV of all record types and fields within the group. For an item, this single CSV row might contain fields for the following record types: 25, 26, 28, 52, 54, 52, and 54. Also remember that record types can be excluded from this process. For example, you can exclude record types 50 and 52 from the export, and in that case this same record group would be exported as: 25, 26, 28, and 28. If you only need the item with the attached BOFD endorsement, then you can exclude 28, 50 and 52, and then the exported record group will consist of just the 25 and 26 records. Although these are examples of forward presentment files, the process applies equally to return files.

This export format is provided as a convenience since it may be easier to parse by your application programs. Although the concept of record groups is shared with item export, it has the benefit that it includes every field with the individual record types will be exported.

Export As Errors

Errors can be exported in CSV format to allow you to get a list of errors associated within a given file that you can easily share with others. All of the previously stated selection criteria applies (record number range and record types). In addition, error export allows you to indicate that you want to limit the export based on:

• All errors
• Record (data) errors only
• Image (tiff) errors only

You can also identify the severity of the errors to be exported. This can be all error severities, or combinations of error level, warn level, and info level. The field data that is written is aligned per the columns as depicted on the Errors tab. This list of fields is as follows: Error description; Error identifier; Error record number; Error field number; Error field name; Error field data position; Error field data length; ;Error field value; Error field mandatory indicator; Error field list of allowable values; Error field primary edit rule; Supplemental information; Item amount; Item ECE sequence number; Item routing number; Cash letter record number; Cash letter identifier; Cash letter amount;& Cash letter business date; Bundle record number; Bundle amount; BOFD routing number; BOFD business date; BOFD sequence number.

Export as Use Cases

Use case files are input into various X9Assist functions, and very specifically Scrub and Make. In particular, the basic use case files which are utilized by Scrub contains four fields: Routing; Account number; Process Control; Auxiliary OnUs.

Export Use Cases creates a file that is compatible with the above and contains the following fields: Routing; Account number; Process Control; Auxiliary OnUs; Amount; Item sequence number; EPC; Documentation type indicator; Return acceptance indicator; MICR valid indicator; BOFD indicator;& Correction indicator; Archive indicator; Return reason; Return notification indicator; BOFD routing.

The export use cases function allows you to create these lists using your production data, with data extracted from one or more files. The creation of these use case files from your production files will guarantee that valid MICR line combinations are used by X9Assist when creating sanitized checks. This will help to ensure that your downstream application systems will be able to process the sanitized data and will not arbitrarily reject items based on MICR content.

This function allows you to either create a new use case file or to merge data into an existing file that you have previously created. By merging into an existing use case file, you can build use case files which contain a large number of routing and account numbers. The merge process ensures that any given routing/account number combination will only appear once in the accumulated use case file, no matter how many times it may appear in the individual files that you are using for this extract and accumulation process.

Using this function, you may want to create use case files (for Scrub) that could be used for a variety of different functions. For example, you may want to have different use case files for forward presentment versus returned items files. You can then use these accumulated files for common functions such as Scrub, as an alternative to the vanilla files that can instead by created using the Use Case Editor.

Export as Tiff Tags (Applies to X9)

The IFD directories from Tiff images can be exported into CSV format. The export is in CSV format and will contain one row for each tff tag within each image. The content of each exported row is as follows: Record number;& Tiff tag index; Offset of the tiff tag within the image; Tag; Type; Count; Value; Directory entry in hex.