When creating import files, follow these important guidelines:
Files must be created in ASCII format.
Each file is made up of a number of records (or rows), separated by a carriage return. If you are creating your import files in a word processor, be sure to save the file as a .TXT (text) file, not .DOC (document). Most spreadsheet packages offer an option to save the file in both a CSV and comma delimited format which is an acceptable file format for importing.
Each record must start on a new row/line.
Each record must have commas as column separators; all fields in the record (even blank fields) must be separated from the next field by a comma.
Avoid using commas in all text fields (for example, do not include commas in the Resource Code Description). If a text string field contains one or more commas, surround the field with quotation marks. For example, "21-100, A" or "21,100".
Do not use commas in writing numerical values (i.e., write 10000 not 10,000), or dollar signs in dollar amounts (i.e., write 10000 not $10000).
Use decimal points when writing dollar amounts (i.e., DO write 10000.00).
Use the minus symbol (-) preceding dollar amounts to indicate negative values. To indicate positive values, enter the numbers without a symbol or precede the dollar amounts with the plus symbol (+).
Numeric fields equal to zero can be designated by a zero or a blank. For example, you may enter either the delimiting comma for the previous field, a zero and the delimiting comma for the next field (,0,) or enter two commas together (,,). (This does not apply to date fields.)
Valid date formats:
Date Formats |
||
M/D/YY |
M-D-YY |
DMMMYY |
M/DD/YY |
M-DD-YY |
DMMMYYYY |
M/D/YYYY |
M-D-YYYY |
DDMMMYY |
M/DD/YYYY |
M-DD-YYYY |
DDMMMYYYY |
MM/D/YY |
MM-D-YY |
YYMMDD * |
MM/DD/YY |
MM-DD-YY |
YYYYMMDD |
MM/D/YYYY |
MM-D-YYYY |
|
MM/DD/YYYY |
MM-DD-YYYY |
|
D/MMM/YY |
D-MMM-YY |
|
D/MMM/YYYY |
D-MMM-YYYY |
|
DD/MMM/YY |
DD-MMM-YY |
|
DD/MMM/YYYY |
DD-MMM-YYY |
|
If no data is to be input in a field, you must still enter a comma as a column separator to indicate the blank field. For example, in the import record CAL,01FEB04,,172 there is a blank field between 01FEB04 and 172.
Any special characters that result in binary characters (for example, "em dash" (—)) are not to be used in MPM as they may cause problems in reports.
Fields will be truncated if they exceed the maximum size specified for that field.
Many of the topics that follow describe the file formats for imports. The formats are presented in tables with the following columns:
Field Position — Each record (row) is broken into a number of fields, separated by commas. The Field Position column describes where each field should be located in the record.
No. of Chars — This column lists the maximum number of characters this field may contain. If the Fee field contains 12 characters, you can enter less than 12 characters, but not more. MPM knows how long the field is because it starts and ends with a comma.
Type — This column shows the type of data you should enter into this field. The field types in the import files are listed with the following abbreviations:
A — Alphanumeric
N — Numeric
Y/N — Yes/No (field would contain Y or N only)
Field Content — This column describes the general content of the field's data, which should correspond to a field on the window for that data type. For example, the Resource Code field in the Actuals import file corresponds to the Resource Code that you would enter into the Actuals window.
Fields marked with an "*" are mandatory fields. You must provide data in these fields or you will receive an error when you run the import and the record will not be imported.