Importing P&L Data from an Excel file

Overview

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 Source Data to import:

Select the file you wish to import, e.g. MyExcelFile.xlsx
Select the worksheet you wish to import, e.g. Sheet1
Click Next

Click Load Field Mappings

Select the correct import mappings (refer to your specific topic)
Click Next

Ensure that the sample data looks reasonable (refer to your specific topics for an example of this). Click Next

Click Run Item Codes Import to start the import. Ensure there were no Invalid or Rejected records by checking that all records were either Imported or Skipped (if previously imported). Click Next

If required, save the item code import process as an Autotask

Click Finish

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

If required, save this as an Autotask

This process should then be carried out on all other variables for which a mapping table is available. For more information see Applying Item Code Mapping Models

Note: This process may also already be automated in an Assign Item Mappings Multitask which can be run from Databases / Files > Autotask Manager

Importing Data

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 External File. Click Next.

On the General tab:

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

On the Advanced Options tab

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 Source Data to import

Select the file to import, e.g. MyExcelFile.xlsx
Select the correct worksheet, e.g. Sheet1
Click Next

Click Load Field Mappings.

Select the correct import mappings (refer to your specific topics)
Click Next.

Ensure that the sample data looks reasonable (refer to your specific topic for an examples)

Click Run Import to start the import. Ensure that there were no Invalid or Rejected records by checking the summary box to ensure that all records were either Imported or Skipped (if previously imported). Click Next

If required, save the monthly values import process as an Autotask

Click Finish

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.

Common Issues

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.

Articles in this section