Supported File Formats
Our royalty software can import data from the following types of files:
- Microsoft Excel
- Comma delimited text files
- Microsoft Access databases
When importing from Excel close the spreadsheet before beginning the import. If the spreadsheet is open dates will be imported as numbers.
If importing from a Microsoft access database, a dialog box will appear where you can select a table or query from your Access database to import.
Source Data Layout
Information in the source data file must be arranged in rows. The first row above the data contains the column headers. In EasyRoyalties Version 4 you can select the header row during the import process. Usually this is row one.
The columns can be arranged in any order, and column headers can be named anything. The import function includes a mapping step where the columns in the source file are matched to the appropriate columns in the database.
Each data component should be in a separate column. This means that city, state and postal code should be in separate columns. The organization name should not be in the first name or last name field. The title and sub-title fields should not share the same column.
If different data components; data fields, are in the same column the import file will need to be cleaned up prior to importing.
Special data considerations
Do not include foreign currency symbols in foreign currency amounts. Transactions that can be recorded in EasyRoyalties as foreign currency will have an associated currency field. Provide the currency in this separate field.
When specifying currency, use the official 3-character currency code (ISO 4217). A list of these codes is provided in the Options & Settings > Exchange Rates > Lookup menu.
Yes/No fields can be specified with any of the following:
-1 (for Yes)
0 (for No)
Dates can be specified using the date format corresponding to your computer’s regional settings.
With Microsoft Excel spreadsheets; however, problems may arise with dates that are provided as date datatypes and dates may not be interrupted correctly even if the format is correct. If the date appears as a number in the import preview screen confirm that the excel workbook is closed. If the excel file is closed and the date is displayed as a different date then do the following:
- Create a new column next to the column containing dates and give it a column name.
- Create a formula in the new column as follows: =TEXT([date cell],”mm/dd/yyyy”)
The [date cell] is the cell in your spreadsheet containing the original date. “dd/mm/yyyy” corresponds to your system’s regional settings for data displays (e.g. in the United States, use “mm/dd/”yyyy”)
The resulting column will contain text information. Map this column instead of the original date column when importing.
If you have any questions about the format of your import file you can submit your file to us for review. Clients with a Premium Support Plan can schedule an online review session. New clients receive 90 days of premium support.