Fixed Length Export and Import

Overview

Joe Murray is working on this for a client and hopes to contribute it to core. This is his working documentation - a work in progress.

As of 3.1, currently supported import types are csv and SQL queries, and the currently supported output type is csv.

Some external interfaces of applications exchanging data with CiviCRM produce and consume fixed length format files, and do not support csv. Typically these are legacy mainframe applicatioins. For example, banks may want monthly bank debit information uploaded in this format, and may export transaction results in it as well. Supporting this format will make it easier to enable data interchange with such applications.

It is currently possible to work-around the lack of support for fixed length formats for export but not import. For exports, one could create a view-only custom field, use code to set it to an appropriate value, and then do a csv export of just this field. This requires the skillset and effort involved in creating a custom module to implement hook_civicrm_custom. On import, a similar work-around is not available as the data used for matching with existing objects would be embedded along with other information in the fixed length record. This project aims to make it possible for non-programmers, and easier for all, to implement importing from and exporting data to fixed record length files.

Work to support this functionality will begin at the database and code level, and a UI to support the changes will be added as necessary. The target audience for this functionality is system integrators or administrators with the ability to code sprintf format specifications for fixed width fields.

Schema Changes

Add the following field to civicrm_mapping:

<field>
      <name>is_fixed_length</name>
      <type>boolean</type>
      <default>0</default>
      <comment>Is this a fixed length mapping?</comment>
      <add>3.2</add>
</field>

Add the following field to civicrm_mapping_field:

   <field>
       <name>fixed_length_format</name>
       <type>varchar</type>
       <length>64</length>
       <comment>sprintf format for this field</comment>
       <add>3.2</add>
   </field>

User Interface - Export

Selecting contact records for export has the same interface as at present.
When the Export Contacts action is initiated on a group of contacts (all or all selected records), the second page of the export wizard will have a third option added: Select fields for export to a fixed length file
Only the appropriate type of saved field mapping (ie normal csv or fixed length) will be made available for csv and fixed length exports

User Interface - Import

On the first page of the import wizard, add an option for Fixed Record Length (.txt). When selected, the rest of the page will appear as though Comma Separated Values (CSV) has been selected with the following changes:
- The name for the second fieldset is changed from Upload CSV File to Upload Fixed Record Length File (TXT), and change the help text under the Upload Data File field appropriately.
- Remove

First row contains column headersCheck this box if the first row of your file consists of field names (Example: 'First Name','Last Name','Email')

[Tentative UI:] On the second page of the import wizard, an additional column will be added on the left of the table titled, "Field input format". It will default to null on a first implementation. sprintf documentation of the formats used by fscanf will be provided as help, with an emphasis on examples of how to specify fixed-width fields during import. Future enhancements may allow users to specify column widths and types in a more user friendly format.
A button will be provided to refresh the parsing of the data in the first two records.

Code to Add

CRM/Import/DataSource/Fixed.php

Code to Modify

CRM/