- Contents
Exchange Manager Help
Create an Excel Import File
Use the Define Import Layout window to create an Excel file layout so that you can import it and then modify the file layout.
To create an Excel import file
-
In the Navigation pane, click Clients.
-
In the Clients pane, expand the appropriate folder and then double-click the client. The tab for the specified client appears in the Information pane.
-
Click the Import Files tab and then click the New Business, Financial Transactions, Maintenance, or Final Recall tab, depending on the import file type.
-
Click Modify File Source Layout. The Define Import Layout window appears.
-
In the menu bar, click View Excel Sample to open the sample file.
-
Save the file to any location, then modify the file to reflect the layout of your import files. Exchange Manager recognizes the following column heading names in Excel as input fields for the record type. Exchange ignores any other Excel column heading names.
Type: Data type.
string: Text or numeric characters.
int: Integer.
decimal: Decimal, two decimal places accepted by default.
dateTime: Ddate and time.
Name: Name that represents or describes the field within the record. Don't include spaces after the name.
Format: Format of the DateTime and Decimal field types only.
DateTime – If you don't use the standard format (MM/DD/CCYY), specify the format to use.
M=month, D=day, C=century, Y=year, H=hour, M=minute, S=second (for example, MMDDCCYYHHMMSS)
Decimal – Not required when the file includes a decimal or the last two integers are decimal places. Otherwise:
nodecimalpoint = No decimal point.
signedascii = Signed ASCII RMS format.
nodecimalpointwithsign = Plus or minus sign after integer.
Note: Some input strings that require complicated alteration or parsing before import may require custom programming. For assistance, contact Latitude by Genesys Support.
Width: Number of characters in the record. Required for fixed-length files; you can leave them blank for delimited files.
TableType: Record type. Specify one of the following for the first field within the record type only:
FileContext: Header or trailer record.
RowContext: Main record, usually repeated. Many import files contain only one record type, with this repeated record being the RowContext. Only define one RowContext record type per file. The RowContext record must contain the unique identifier for mapping (as defined on the General tab). This record can stand alone and has no parent. It's used for records such as account and payment.
Sub: Subordinate record. This record type depends on another record to link to account information and can occur multiple times for the same account (for example, notes, payment, maintenance records). Also specify parent and child relations to link the subordinate record to its account record. Subordinate records must follow the parent sequentially when they don't contain a field that links to the parent.
Key: Regular expression that identifies the record within the file.
MaxOccurs: Maximum number of times the record occurs.
1 = Record occurs once only. If only one sub record with this name exists for each account, or the record is a header, trailer, or main record, select this value.
* = Multiple records exist. If more than one sub record exists for the same account, select this value.
ParentTable: Name of the parent record for this record (only applies to subordinate table types).
ParentColumn: Name of the parent column used for record matching (only applies to subordinate table types).
ChildTable: Record name (only applies to subordinate table types).
ChildColumn: Name of the child column used for record matching (only applies to subordinate table types).
-
Delete the unused worksheets (for example, B through Z) and save the Excel file. After you create the Excel layout, you can import it into Exchange Manager to define your source file layout. For more information, see Import an Excel File Layout.