Importing Sales Data from an Excel file

Overview

This topic shows how to import new item codes and sales units and values from sales files in an Excel format.

Before importing any data, please ensure that you have updated the relevant exchange rates for the database.

Importing Item Codes

1. From IFP Home select Codes Maintenance > Import Item Codes
2. Select External File, click Next
3. On the Data Import Options screen set:

• If a conversion table is used. Leave as (None) if not required.
• If you wish to Overwrite Existing Records. Leave this unchecked unless required.

• 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

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

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:

1. Empty rows  leading to incomplete item codes
2. Records are missing a description
3. A mixture of text and numbers in a column could lead to incomplete imported data

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

1. From IFP Home Select Codes Maintenance > Item Codes Maintenance
2. Select only items which have no value for the required variable (such as BU) using the following rule
   
3. Apply the relevant mapping table, e.g. Assign BU code conversion table

• Select the column by clicking on the column header (e.g. BU)
• From the toolbar select Modelling > Apply Model
• Select the relevant conversion table (e.g. Assign BU)
• 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

Importing Data

1. From IFP Home select Databases/Files > Data File Manager
2. 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.

1. From IFP Home select Databases / Files > Data File Manager
2. Select the relevant data file
3. Select Properties > Status and set the correct status for both Units and ASP
   
4. 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. Note that simply reformatting cells will not resolve this issue.
• Sheet names containing unusual characters such as / or \. We suggest renaming the Excel worksheet as IFP.
• The 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.