Import Per Diem Rates

This feature allows for the importing of Per Diem Rate information into the Unanet system.

Roles

This screen is available to users having any of the following roles: Administrator and P&R Administrator

License

This feature is available with any Unanet edition.

Performance Tip Note:  Depending on the number of users in your installation and other factors such as the overall performance of your platform, the activity of importing one or many files may take up to several minutes.  Should this be the case with your installation, you may want to consider running these processes at non-peak times to minimize database contention.

 

Topics covered on this help page include:

 

You may also be interested in:


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 and number of historical entries. Some customers may be interested in loading a limited set of their own custom locations, others may choose to load entries based on their locale or country, and others may be interested in loading up data from one of the US government sources.  

Unanet Provided CONUS and OCONUS Per Diem Files

Unanet monitors the Department of Defense source and maintains a version of that data (manipulated into our import layout) on our  Support >> Download site (login is required - then look for Per Diems section), which you can download and import directly into your system periodically per your needs. We maintain both a CONUS and OCONUS set of files and attempt to make available new versions as soon as we notice the DOD has posted updates.  Unanet does not vouch for the accuracy/content of the data provided by the DOD.  

Customer Supplied Per Diem Files

If you prefer to manage the per diem data being imported on your own, you will need to manipulate the data into the Unanet required format described below.  Further, you will need to determine the frequency with which your data must be re-imported / updated.  

Listed below are a number of known US sources for this type of data.

 

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 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.

Note: The maintenance of the per diem rate tables is a customer responsibility as there are numerous sources of per diem data and each customer may have their own policy dictating which source is to be used -- and at what frequency the data must be kept up to date.  


Import File Record Layout

IMPORTANT: Note that there are multiple places within this documentation that say you MUST use double quotes for the strings in your import. If you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually. Manually adding double quotes within Excel would result in multiple sets of double quotes in the resulting .csv file and thus cause an error (should you attempt to import that resulting file). Please see Excel Tips for more information.

 

 

 

Field Header Name

Required/Comments

1

A

Country_State_Name

ALWAYS REQUIRED. A name identifying the primary location.  Typically this would be the name of a Country or State.  

Alpha-numeric, max of 45 characters.

2

B

City_County_Name

ALWAYS REQUIRED. A name identifying a sub-location within the primary location.  Typically this would be the name of a city or county.

 Alpha-numeric, max of 45 characters.

3

C

Effective_Date

ALWAYS REQUIRED.  The first date for which this particular per diem rate is effective.

Recommended date format:   yyyy-MM-dd    See Allowable Date Formats for more options.

4

D

Expiration_Date

ALWAYS REQUIRED. The last date for which this particular per diem rate is effective.  

Recommended date format:   yyyy-MM-dd    See Allowable Date Formats for more options.

5

E

Season_Start_Date

ALWAYS REQUIRED. The month and day upon which this particular rate's season begins.  

Format must be "mm/dd".

6

F

Season_End_Date

ALWAYS REQUIRED. The month and day upon which this particular rate's season ends.  

Format must be "mm/dd".

7

G

Lodging_Rate

ALWAYS REQUIRED. The maximum per diem lodging allowance for this location and season.  

Format  NUMBER (15,3) -- for example "01234567890.123 ".

8

H

Meals_Rate

ALWAYS REQUIRED.  The maximum per diem meals allowance for this location and season.  

Format  NUMBER (15,3) -- for example "01234567890.123 ".

9

I

Incidentals_Rate

ALWAYS REQUIRED.  The maximum per diem incidentals allowance for this location and season.  

Format  NUMBER (15,3) -- for example "01234567890.123 ".

10

J

Oconus_Indicator

Indicates whether the rate being imported is to be categorized as oconus. A value of  Y means the location is to be considered oconus.

Allowable values include Y or N (N is the default if not provided).  

This indicator was added in order to accommodate per diem tax calculations and is only taken into consideration on the Lodging Per Diem and the Lodging Allowable wizards.

 

Note: Check out the Unanet Data Model for specific field data types, lengths, and other attributes.


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.

Importing a Sub-Set of Columns using a Field Header Record

If you are not using the default column layout sequence layout as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicate what data is contained in each column).  The specific Header Names for each column are listed in the table above.


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.

 

Important Note Regarding Historical Rate Entries.  When an entry is imported, it will replace all existing entries for that same location.  Thus, if the data source you are using modifies the unique name of a location, or splits a location into two locations, when importing the new records, the old/historical records will remain.  As a best practice, you may want to always exercise the "First Purge all existing Per Diem Rates in the system" option such that with each new import, you first clear out the existing records and replace with the new data set.   

If you are importing both Conus and Oconus files, you would only want to use the "Purge" option with the loading of the first of the two files.


Excel Template

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 download an Excel Template with predefined headers.  

See the Excel Tips regarding the use of double quotes and more.


Import Screen

The Import Per Diem screen simply looks like:

Field Descriptions:

Field

Description

Type of Import

Select the appropriate import type from the list of available types of imports.

File to Import

Enter (or browse for) the file that you would like to import.

Output Options

Specify the level of detail included in the output.  

Per Diem Rates

First Purge all existing Per Diem Rates in the system.

When an entry is imported, it will replace all existing entries for that same location.  Thus, if the data source you are using modifies the unique name of a location, or splits a location into multiple locations, or multiple locations are merged into a single location, etc.,  when importing the new records, the old/historical records will remain (i.e., those existing records that do not have a name that exactly matches a new location being imported will remain).  

As a best practice, you may want to always exercise the "First Purge all existing Per Diem Rates in the system" option such that with each new import, you first clear out the existing records and replace with the new data set.  

Note that this purge will NOT impact any expense report entries (rates already stamped on the expense reports will remain as is; the existing reference data is all that is being removed). 

Conus & Oconus

If you are importing both Conus and Oconus files (or any "set" of multiple files), you would likely only want to use the "Purge" option with the loading of the first of the files (so that you're not immediately deleting what you just imported).

Note that you can also remove all per diem rate entries via the Per Diem Rates maintenance screen. However, due to the enormous volume of data, the screen may take a long time to draw, and thus this option may be a preferred means to remove all existing per diem rate entries.

If an error is encountered in the data file after a successful purge, the data will remain deleted even if no records are successfully loaded (i.e., there is no "rollback" to the purge operation).

 

The import process is triggered by pressing the Import button.  All Warnings and Error messages are written to the screen and can then be saved if desired.  While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database.  The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the Import results page. The rejected record file name will be the import name (from the Type of Import drop-down on the Admin>> Import page) , concatenated with a space and "Errors.csv" (e.g., Item - Master Errors.csv, Alternate.csv, etc.). The file will be placed in the Unanet temporary directory (which is defined by the Temp Directory (Fully Qualified Directory Name) (unanet.temp_directory) Unanet property). You can also click on the Download the error file link at the bottom of the Import results page (sample image follows).

 


Sample File

If you would like to experiment in a test environment, you can download and import per diem rate files from our support site.  

Note: The maintenance of the per diem rate tables is a customer responsibility as there are numerous sources of per diem data and each customer may have their own policy dictating which source is to be used -- and at what frequency the data must be kept up to date.  

Because we are not the owners of the per diem rate information, we cannot vouch for the accuracy of their data. However, we do convert the All History Data files from the DOD source into our per diem import format periodically and post these files on our Support >> Download site.   For the most up to date information, however, you may want to convert the files yourself from your data source of choice.

One word of warning.
The Per Diem Rate file uses a mm/dd format for specifying season start and end.  If you open this file in Excel, Excel will automatically convert these values to full dates in dd-mmm format; e.g., 1/1 becomes 1-Jan.  If you save the file with these converted values it will not import correctly.

 

Related Topics