Creating Import Files and Templates

If your trial balance or budget data import files are in CSV-format or fixed-length ASCII- text, the field formats must comply with Voyager data standards. If the data is not in the specified format (as listed in “Import file field requirements” and “Budget formats for CSV files”), you must create a format template (FMT) file to define and describe the data that you plan to import.

You must adjust the format template so that you import only the fields that exist in the import-data file. The rows in the template must coincide with those in the import data. Also ensure that the field headers match between the import file and the template. If you remove any fields, you will have to renumber the template rows so they are sequential and coincide with the actual row order.

Trial balance and budget format template tables -- CSV and PRN

Before loading a trial balance or budget formatted by a non-Yardi system or application, you must create a format file that describes the information you plan to import.

The following table describes typical trial balance and budget fields that you can include in a format template (FMT) file:

Field 1:Code	Field 2	Field 3	Notes
PROPERTY	Field position number; or beginning column (fixed-length files)	0; or data record length (for fixed-length files)	The property affected by the import procedure. If a property is not identified in the import file, it can be identified in the import filter. An account balance or budget amount must be associated with a valid property in the destination database. Import will fail if Voyager cannot identify the property, either in the import file or as typed in the import filter.
ACCOUNT	Field position number; or beginning column (fixed-length files)	0; or data record length (for fixed-length files)	Required. An account balance or budget amount must be associated with a valid property in the destination database.
AMOUNT	Field position number; or beginning column (fixed-length files)	0; or data record length (for fixed-length files)	Required. An account balance or budget amount must be associated with a valid property in the destination database. For trial balances, positive figures are debits and negative figures are credits. (This is the method by which the information is stored and how Voyager imports it.)
REPEAT	Number of periods	Months in each period	Required, if loading more than one month of data. For example, to load a year of monthly data, use ,12 ,1; to load a year of quarterly data, use ,4 ,3.
BUDGET	1	1	Optional flag. Indicates that figures are budget amounts. Without the flag, Voyager interprets the figures as actual account balances or charges.
YTD *	1	1	Optional flag. Indicates that figures are ending balances. Without the flag, Voyager interprets the figures as month-to-date charges. Does not apply to loading budgets.
REVERSE *	1	1	Optional flag. Creates a reversing journal entry for the previous trial balance import. Does not apply to loading budgets.
PROPXLATE **	Property code in source file	Property code in destination database	Optional. Maps a property in the source data to a property in the Yardi database. If needed, multiple source properties can be mapped to one property in the destination database.
ACCOUNT NUMBER IN SOURCE FILE ‡	Account number in source file	Account number in Yardi database	Optional. Maps one account or a range of accounts in the source file to one account in the destination database.

Notes

* These flags are read only by the import process.

** Used only by the export process. Property codes cannot be mapped on import.

‡ Account numbers can be mapped on import or export.

Format-template files -- trial balances and budgets

The following table describes the format-template files for trial balance and budget imports:

Import File Type	Field 1	Field 2	Field 3
Comma-delimited (CSV)	Field name or code	Field position on import line; 1 for first, 2 for second, and so on.	0
Fixed-length	Field name or code	Column where field begins.	Field length, characters

The following guidelines apply to format-template files for trial balances and budgets:

c	The format template (FMT) file is a comma-delimited or fixed-length ASCII text file, with the extension “.fmt.” The standard name for a trial balance format file is journal.fmt.

c	The format template (FMT) file has three fields per line: the field name (a string enclosed in quotes), the field position, and the field length (if needed).

c	If the import data is in comma-delimited format, the field length column (Field 3 in the preceding table) will always contain a zero (0). If the data is in fixed-length format, this column defines the size of each data field.

c	Budget templates for import files can also be created as fixed-length format (PRN) files.

c	Budget templates can include property information in each line; a budget import file can contain multiple properties.

c	PROPERTY is an optional field, to be used if the file contains information for multiple properties.

c	REPEAT is an optional field, to be used if the file includes multiple accounting periods.

c	REVERSE is an optional field. It specifies that the figures are year-to-date. (In some cases, the YTD field can be applied instead, and you can omit this row when using month-to-date figures.)

c	Each field name that you include in the format-template file must match one of the field names listed in the “Required transaction fields table”. (The names are case-sensitive.)

c

The source data (CSV-format) and format-template files are not required to have the same name or be located in the same folder, because you can specify these parameters in the CSV-format file import filter. If you do not specify a location for the format-template file, but one exists in the default path data folder, Voyager uses that file.

c	You can also import budgets that include multiple sequential periods per file. Budget amounts can be either monthly or year-to-date figures.

c	You can export budget data from your source application to Microsoft Excel, save it in CSV-format, and then re-import it to Voyager.

c	The file format for a budget CSV-format import file must exactly match the layout positions in the format-template file.

The following table shows an example of a typical CSV-format budget import file:

PROPERTY	,1	,0
ACCOUNT	,2	,0
AMOUNT	,3	,0
REPEAT	,12	,0
BUDGET	,1	,1

For a fixed-length format file, you can add a third column containing the field length specification.

Account translations

If you import trial balances into a database that has a different chart of accounts than the source database, you can add the account mapping information to the format-template file.

The following table shows a sample format for adding account ranges to the end of an existing import file:

PROPERTY	,1	,0
ACCOUNT	,2	,0
POSTMONTH	,3	,0
AMOUNT	,4	,0
11100000	,19900000	,12000000
20000000	,29900000	,21500000
30000000	,39900000	,31500000

In this example, the first two columns in each line (for example, 11100000,19900000) represent an account range from the source database. If the range consists of a single account, you would make both numbers the same. Do not leave any positions empty. The third column is the posting account in the imported database, for account transactions from the source database.

Balance translations

To avoid missing a transaction posted to a closed period in the source database, your organization may need to import year-to-date balances. In this case, you can add two additional rows to the format-template file, as follows:

REVERSE This row creates a reversing journal entry of the last month's balances, and then imports the current year-to-date (YTD) balances. Because both the reversing entry and the new YTD balances are posted to the current period, the net total is the month-to-date (MTD) balances.

YTD This row calculates the current system year-to-date balances, and then subtracts those amounts from the year-to-date balances provided in the imported file. Voyager posts the difference as a MTD entry.

The following table shows an example of a format-template file for balance translations:

PROPERTY	,1	,0
ACCOUNT	,2	,0
POSTMONTH	,3	,0
AMOUNT	,4	,0
YTD	,1	,1

When you use either the REVERSE or YTD fields, the format file should include the field name and the values 1,1. Because these are only flags (and the CSV-format file will not contain the corresponding data), these values act as a placeholder for the remaining two positions.

Budget formats for CSV files

In order to import a budget CSV-format file into a Yardi database, you must first correctly format the file. Budgets are also imported from fixed-length or comma-delimited ASCII files that can include multiple sequential periods per file. Budget numbers can be either month- or year-to-date figures. Each line can include the property information, so several properties can be included in one file.

Each import-data file requires a format file that specifies how the data will be arranged. The format file must have the same name as the import file, except for an “.fmt” extension. For example, a budget file named 09BUDGET.PRN would have a format file named 09BUDGET.FMT. The content of the file is the same as the trial balance format file (for example, JOURNAL.FMT can be used as the default file name).

You must begin each line with the account code/number in quotes, followed by the account name in quotes, followed by twelve budget amounts.

If any of the twelve expected budget amounts is missing (for example, if you only import ten months of data), the import will fail and a message will appear, identifying the first line number of missing data.

If an entire account is omitted from the file, the budget for that account is assumed to be zero for all twelve months, and the import will continue normally.

When you plan to import budget amounts from a Microsoft Excel worksheet, a CSV, or a fixed-length ASCII-text file, use the following format for each line of data:

Property Code, G/L Account Code, G/L Account Name, Amount1, Amount2, Amount3, Amount4, Amount5, Amount6, Amount7, Amount8, Amount9, Amount10, Amount11, Amount12

Yardi recommends Excel as an editing tool for budget data, but you can also edit the data in any other application that can save and edit text or CSV files.

For budget import files that do not match this format, you must create a format-template file. For more information, see “Creating Import Files and Templates”.