SpendMap User Guide (v14.5)

Zoom Window Out
Larger Text | Smaller Text
Hide Page Header
Show Expanding Text
Printable Version
Send Mail Feedback
Save Permalink URL

Navigation: System-Wide Features and Information > Integration Tools > Generic Integration Utilities > Generic Batch File Integration Utilities

Configure Import/Export File Layout

Scroll Prev Top Next More

(Module) > Utility > Data Interfaces ... Configure Import/Export File Layout

When using the "batch file" import/export utilities (as opposed to one of SpendMap's "direct" interfaces), this utility is used to configure the applicable Import/Export function to match the file format (layout) required by the other system. You can specify the type of file to use, select fields to include, field sizes, field order, delimiter, etc.

SpendMap supports the following interface file types:

•Excel (XLS or XLSX)

•Text/ASCII (TXT or CSV)

•FoxPro (DBF)

•Integration using SQL tables

The file type and layout can be different for each of the 30 or so built-in interfaces. For example, you might import Item Master File data using XLS files but export invoices using CSV files.

Also, you can have multiple file configurations per interface. For example, you might be importing item catalog data from multiple suppliers, each using a different file type and layout.

For more information about the integration tools in SpendMap, see Data Interfaces - Overview and General Information.

Select a Configuration File

This screen shows a list of import/export configurations.

If you are using this interface with only one other system, you will only need a single import/export configuration. However, if you will be interfacing with multiple systems, you can create a unique configuration for each system if needed, each using a different interface file type and layout. If you set up more than one configuration, you will be prompted to select one of the configurations when you run the applicable interface.

Use the [PRINT] button to generate a report listing the details of the selected configuration.

Running interfaces unattended?

Interface File Configuration Screen

This screen provides an overview of the interface configuration, with settings for the overall file at the top of the screen, and a list of the individual fields within the file at the bottom of the screen.

All available fields for the interface will be listed in the browse list and the Include Column will show a check mark ( _check ) if the field has been included (enabled). For import configurations, some fields may be mandatory and will always show “ _check ”.

To add, remove or modify fields, select the field in the browse list and select [MODIFY] or just double-click on a line. This will take you to the Field Configuration Detail Screen.

Select [RESET] to start over.

Fields

Field Configuration Details Screen

This screen displays the field’s details. The field name and type are displayed in the top, left-hand corner of the screen. A more detailed description of the field as well as any special rules or additional details will be displayed at the bottom of the screen.

The Validate Code field is used for import operations when the field has a corresponding Master File. You can check ( _check ) this box to validate the import data against the current entries in the applicable Master File. If you check this box and the applicable piece of data is not a valid entry in the applicable Master File, the record being imported will fail validation and will not be imported. Following the import operation, you can view the import error log to see which records failed validation and were not imported. You can then make the adjustments and import the remaining items. If you do not check this box, data may be imported into the applicable field even though it is not a valid entry in SpendMap.

The fields in the middle of this screen are used to determine where (what position) the field is within the interface file. For example, when using a delimited ASCII/TXT file, you will specify a field position, whereas you will specify a column letter when using an Excel (XLS) file, etc.

The Default if Blank field can be used to populate a file, even if it isn’t populated in the parent system. That is, if you included this field in the configuration layout and it is possible that the field will be blank for some or all of the records to import or export, you can optionally specify a default value for those blank records. If a default is specified AND the field is blank for the record being imported or exported, then the default value will be used. In addition, if a default is specified but the field is NOT specified to be included in the import file, then the default will be used for ALL records when the import is run.

Field	Description
Format	•Indicates the type of file being used for this configuration, as specified when the configuration was first added. This cannot be changed. If a different file type is needed, you must create a new configuration
Description	•A description of the configuration, for reference purposes only.
Date Format	•For use with Text/ASCII file types, the format for all date fields. Use YY or YYYY to represent the year, MM to represent month and DD to represent day. •You can optionally use a slash (/) or other separator. For example, options include YYYYMMDD, DD/MM/YY, MM-DD-YYYY, etc.
Delimiter	•If you selected the “Text/ASCII – Delimited” as the Format type for this configuration, enter a delimiter to separate each field from the next in the interface file. The most common delimiter is a comma (,) but any character can be used. •To use a TAB as the delimiter (ASCII: 9), enter “T” (without the quotes). •You can optionally select () to include double quotes around text fields, which is commonly used if it is possible that the selected delimiter may appear within the data of a text field. The resulting entries in the interface file will look like this... "CHARACTERFIELD1", "CHARACTERFIELD2",123.45, 67890 If you do not Include double quotes around text fields, the resulting entries in the interface file will look like this... CHARACTERFIELD1,CHARACTERFIELD2,123.45, 67890
Captions	•Check this box if field titles are to be included in the first row of the interface file. Some software programs include field captions in interface files to simplify the setup process but this data is skipped during the import process. This option is not available for all formats (file types).
Delete/Overwrite Option	•For import configurations, check this box to delete the import file after the import has taken place as an indication that the operations was successful. •For export configurations, check this box to overwrite the export file if it already exists. Typically this would not be selected as the existence of a file commonly indicates that the last transfer did not complete successfully.