Import Per Diem

Privilege Needed: Administrator

This feature allows Administrators to import 'per diem rate' information into the Unanet system.  This information can be either new entries or the data on the import can be used to update existing data. The table below shows all the fields that are on the per diem rate schedule table that can be imported. 

Sources for Per Diem Rate Information

There are a number of available sources for this type of per diem data -- each potentially having a slightly different layout.  Each Unanet customer will be responsible for selecting and manipulating the source file into the Unanet format specified below.   Further, you will also need to determine the frequency with which this data must be re-imported / updated.  Listed below are a number of known US sources for this type of data.  Unanet does not vouch for the accuracy/content of the data provided by these sites:

• GSA (General Services Administration) - CONUS (Continental US - Domestic), non-military schedule:

http://policyworks.gov/org/main/mt/homepage/mtt/perdiem/travel.shtml

• DOS (Department of State) - Foreign schedule (Outside Continental US - OCONUS less Alaska, Hawaii, etc.), non-military:

http://www.state.gov/www/perdiems/

• DOD (Department of Defense) - CONUS, OCONUS with and without military rates, also has a file with all rates, all history:

http://www.dtic.mil/perdiem/pdrates.html  (this site may be a good choice as it has all of the rate information on the "Relational History Files" link and appears to be updated whenever the other sites are updated).

Note: Currently, none of the formats available map directly to the necessary Unanet format, thus you will need to manipulate these files into the necessary layout.  For example, the DOD Relational History Files require you to eliminate a number of columns.  A common issue encountered when manipulating these files in excel is dealing with the format of the seasonal date fields (mm/dd), these columns should be formatted as 'text' otherwise excel may treat them as an equation. 

You may also be interested in the M&IE Breakdown import.

Processing Rules

Each imported record will be added or merged into the contents of the existing Unanet per diem rate schedule.  A general set of processing rules will apply to every record imported.  These rules are as follows.

• All fields are required in the import record.

• The contents of all name fields will be converted to upper case before processing.

• All rate values must be greater than or equal to zero.

• The expiration date must be greater than or equal to the given effective date.  If a Season End Day is earlier in a calendar year than a Season Start Day the single rate entry will be processed as though it specified two distinct seasonal periods.  For example, if the given Season Start Day and Season End Day are 09/15 and 04/15 respectively, the rate will be considered as applying to the two distinct seasonal periods of 01/01 – 04/15 and 09/15 – 12/31.  Note that this condition will result in the number of rates added being greater than the total number of records processed within the file.

• All rejected rate records will be written out to a file on the Unanet server as well as displayed at the bottom of the Per Diem Rate Import user feedback screen.

• Successfully importing rate records will not affect the enabled/disabled status of an existing location’s rates.

• All rates for newly imported locations will, by default, be set to enabled.

Import File Format

The file to import must be saved in a comma delimited format.   The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.

Examples:

• field1,field2,field3
• "field1","field2","field3"
• field1,"data containing commas (like this), should be double quoted",field3

You can create the comma delimited import file with any number of tools.  For those interested in using an Excel spreadsheet to create the file, you can click here to download an Excel Template with predefined headers and required fields.  

EXCEL NOTE: the fields that  start with ** are required fields.  

EXCEL NOTE:  Excel may add the double quotes around fields for you.  If using the excel spreadsheet template -- note that excel will automatically put double quotes around fields that contain commas.  Manually adding double quotes within excel would result in two sets of double quotes in the .csv file and thus cause an error on import.

EXCEL NOTE: When creating a comma delimited file using Excel, the saved ascii text file will contain a header row.  This row will be ignored by the import process and will not create any import warnings.  The import process will ignore the header (as well as any other rows) that begin with:    #COUNTRY-STATE NAME#     or     **

Import Per Diem Screen

The Import Per Diem screen simply looks like:

You select the file you would like to import and press the import button.   The import process will commit all data that passes the necessary validations.  That may result in some records being fully imported --  some may be partially imported -- and others may be rejected all together.

All Warnings and Error messages are written to the screen and can then be saved if desired.

Command Line Import

As an alternative to using the Import Screen described above, you can also invoke imports using the command line import functionality.  This functionality allows you to invoke an import without accessing the Unanet system application (user interface).  This may be useful if you would like to write an external process to extract data from an upstream system and programmatically load it into Unanet (without user intervention).  

Return to Unanet Table of Contents page

Copyright © 1998-2002 Computer Strategies, Inc., All Rights Reserved.
Last revised: September 13, 2002 .