Data extract management > Create customized data extract

Create customized data extract

To create a customized card data extract:

1. From the main menu, select Administration > Data Extract Management > Finance Extract Template.

   The Data Extract Management - Finance Extract Template screen appears.

2. Click Create at the top left of the screen.

   The Create Template screen appears, defaulting to the General tab.

3. Type a name for your template in the Template Name field.
4. Type a name for your extract file in the File Name field.

   Note: When the extract is run, Spend Dynamics automatically adds a date stamp to the end of the file name.

5. Select an output file type for your extract from the File Type dropdown list.

   Note: The file types .xls and .xlsx (default) can support a maximum of 65,536 and 999,999 rows, respectively. Any additional rows will be truncated, and a warning message will appear to confirm the action. If you select .txt or .csv, the Format Amounts to 2 Decimal Places checkbox appears. If you are creating a new extract, the checkbox is selected by default.

6. If you select .txt as the file type, select what punctuation mark will be used to separate extracted information.
7. Select how you want dates to appear in your extract from the Date Format dropdown list.
8. Select how you want the extract to communicate if a value is in credit or debit from the Debit/Credit Indicator dropdown field.

   Note: If you plan to add the Transaction - Debit/Credit Indicator field to your extract template, select either D/C or DR/CR. Otherwise, select +/-.

9. If you want to repeat values, where possible, across multiple rows of transactions in the extracted report, select the Repeat Fields checkbox.
10. Select the Display Column Header Row if you want field names to display as column headings in the extract. If not selected, the data will be displayed without column headings.
11. To exclude payment-type transactions from your extract, select Exclude Payments.
12. To display any specific text at the top and/or bottom of your extract, type it in the File Header and/or File Footer text boxes.
13. Click the Fields tab.
14. Select a field category from the Field Category dropdown list. The fields belonging to that category automatically display for your selection and the selection is not limited to one category, this is merely a grouping items in the Available fields list.
15. Click anywhere on a field line from the Available fields list to add it to your list of Selected fields for inclusion in your custom extract. It is not necessary to click directly on icon.

    Note: The Select all link selects all the fields in the category. If a field is added to the selection, it is highlighted in grey to indicate inclusion. A field line can be added more than once to the selected fields; this may be useful for concatenated lists.

16. Select as many fields as you need from as many field categories as you need. You can include a maximum of 256 fields in your custom extract.
17. To change the positioning of a field (column) in the extract, click beside the field and drag it to its new location.
18. If required, click beside a selected field to remove it from your extract. To remove all fields and start over, click Remove All. Note that this cannot be undone.
19. To edit the properties of a field you've selected to appear in your extract:
    1. Click beside the field line item. The Edit Field Properties dialog box appears.
    2. If required, change the name of column header that will appear in the extract. This does not change the name of field as it displays within the Data Extract Management module and (optionally) define the maximum number of characters that will display as data output within the column.
    3. Click Save.
20. To insert a blank field into your extract:
    1. Select User-Defined from the Field Category dropdown list.
    2. Click beside Blank Field, then click and define a name for the column header that will appear in the extract.

       Note: Do not define a maximum field length.

21. To insert a customized field into your extract:
    1. Select User-Defined from the Field Category dropdown list.
    2. Click beside Custom Field, then click and define a name for the column header that will appear in the extract and (optionally) define the maximum number of characters that will display as data output within the column.
22. To join (concatenate) two or more fields of data into a single column of a customized finance extract:
    1. Click Concatenate.

       The Concatenate Fields dialog box appears.

    2. Enter the name of the column header for the concatenated fields that will appear in the extract.
    3. Select the Fields to be joined within the named column. Only fields available in the selected fields window can be added and are chosen by checking the checkbox beside each field in the list. These fields cannot be reordered here; however, they can be reordered in the selected fields panel after the concatenated group is defined.
    4. The separator for the selected fields is defined by selection from the Field Separator dropdown list. If a field has no data, for example, first name, middle name, and last name are in the group but there is no middle name, it will be substituted with a space unless No separator is selected.
    5. (Optional) Define the maximum number of characters that will display as data output within the grouped column. Note that if the joined fields exceed this character length, some may be omitted from the extract.
    6. Click Save.
    7. The concatenated group will appear in the selected fields panel and can be rearranged, edited, and removed as with normal fields.

       Note: The concatenated fields are indented to indicate that they will be joined into a single column in the extract. If the group header is removed, all the selected fields will be removed too. A concatenated group cannot be added to another group.

23. To see what an extract will look like using your new template,
    1. Click Generate Sample.

       The Generate Sample Extract dialog box appears.

    2. To determine what data will display in your sample, select an issuer and period and click Generate Sample.
    3. Select where you want the sample file to save. The sample file is created as an Excel spreadsheet.
24. To save your finished template, click Save.