This topic shows how to import new item codes and P&L values from expense files in an Excel format.
Before importing data, please ensure that you have updated the exchange rates for the database. See How To Create and Edit Exchange Rates.
Importing Item Codes
- From IFP Home select Codes Maintenance > Import Item Codes
- Select External File, click Next
- On the Data Import Options screen set:
- If a conversion table is used, this is usually (None)
- If you wish to Overwrite Existing Records, this is usually unchecked
- To Auto-Create Code Validation Entries, this is usually selected
- Select the file you wish to import, e.g. MyExcelFile.xlsx
- Select the worksheet you wish to import, e.g. Sheet1
- Click Next
- Select the correct import mappings (refer to your specific topic)
- Click Next
Resolving Invalid or Rejected Records
If there are invalid/rejected records then the reason behind this needs to be investigated. Potential reasons for this could be:
- Empty rows leading to incomplete item codes
- Records are missing a description
- A mixture of text and numbers in a column could lead to incomplete imported data
- Auto-Create Code Validation entries was not selected
Apply Item Code Mapping Models
It is important that new item codes are mapped correctly before importing values. For example, in most cases the variable BPC Product is not defined in source files and has to be mapped correctly for any newly-imported item codes.
The mapping process only needs to be executed for item codes that do not currently have a mapped value. These item codes may be selected by using a condition as below.
- From IFP Home Select Codes Maintenance > Item Codes Maintenance
- Select only items which have no value for the required variable (such as BPC Product) using the following rule
- Apply the relevant mapping table, e.g. Assign BPC Product code conversion table
- Select the column by clicking on the column header (e.g. BPC Product)
- From the toolbar select Modelling > Apply Model
- Select the relevant conversion table (e.g. Assign BPC Product)
- Click Apply
Note: This process may also already be automated in an Assign Item Mappings Multitask which can be run from Databases / Files > Autotask Manager
- From IFP Home select Databases/Files > Data File Manager
- Start the Import Data File Wizard.
- Select the Summary folder corresponding to the year e.g. 2015
- Select the Target File e.g. A (15 Actual)
- From the toolbar select Tools > Import
- Select the Period to Import
- Set the Import Currency (this will usually be the default currency, unless individual topics state otherwise)
- Check the required Data to Import options. This is usually Units and A.S.P.
- Set the remaining options. This would usually be checking both Add to Existing Values and Clear Target File First
- Set the required conversion table. This is usually set to (None)
- Select the Import data comments if found option if required, although this is usually left unchecked
- Click Next
- Select the file to import, e.g. MyExcelFile.xlsx
- Select the correct worksheet, e.g. Sheet1
- Click Next
- Select the correct import mappings (refer to your specific topics)
- Click Next.
Lock Actuals Data File
Once the data has been successfully imported, it should be locked to the current month.
- From IFP Home select Databases / Files > Data File Manager.
- Select the relevant file
- Select Properties > Status and set the correct status for Value
- Click OK to save the change
Update the Database Environment Settings
Once the data is successfully imported, you should update the current month for the database. See How to Change Database Environment Settings.
The most common issue with the above process is due to the Excel file being saved with incorrect column names.
When this is done the error message below will be shown.
The solution is to rename the column headers in line with the template provided.
Other potential problems which may be encountered are:
- A mixture of text and number cells within a specific column. This can be resolved by using the text formula in Excel.
- Sheet names containing unusual characters such as / or \. Rename the Excel worksheet as IFP.
- First row contains anything other than column headers. Please delete any rows which are not column headers at the top of the Excel Worksheet.
- When clicking Next on Data Import Options an error message appears informing you the file is locked. Please follow the Lock Actual Data File section in order to unlock the file for the selected period.