Overview
Data can come from a variety of sources into IFP - an inventory system, a supplier, costs/expenses database, another IFP database, etc. To allow this, IFP has a flexible import process accepting IFP packages, Excel, and delimited text files. Imports can also automatically convert codes, reallocate values and remap fields.
Below you will find the steps for importing data into a data file as well as further information on specific parts of the process:
Also listed are some common issues you may find when importing.
Importing into a Data File
To import data into a Data File:
- From IFP Home select Databases/Files > Data File Manager
- Start the Import Data File Wizard.
- Select the Summary or Daily folder corresponding to the year e.g. 2015
- Select the Target File e.g. A (15 Actual)
- From the toolbar select Tools > Import
- Select Import Type and click Next
- Set the Data Import Options and click Next
- Select the Source Data to import and click Next.
- For Package and External File imports, set or Load Field Mappings and click Next.
- Ensure that the Sample of imported data looks reasonable
- refer to your country-specific topics for examples.
- If required, Save field mappings to save new or updated field mappings for future use, or to use in an AutoTask.
- Click Next.
- On the Run Data File Import screen:
- Review the settings listed
- Run Import
- Check the summary and messages (if any). If the import did not run as expected, you can step Back through the wizard to correct any issues before re-running the import.
- If required, Save Results (the settings, summary and messages of the import)
- Click Next
- On the Save AutoTask screen you can, if using saved Field Mappings, save these import settings as a Data File Import AutoTask to run automatically or to step through to avoid entering the settings in future.
Import Type
External File
Excel, Access, CSV or Delimited Text file
- Click the browse button to select the source file. The file type selector indicates the possible supported formats (Excel, CSV, Delimited Text, Access). Select the file and click Open.
- The Source Format dropdown should already be set to the correct format based on the file extension. If this is incorrect, select the correct format.
- If the first row of the source file does not contain the column headings, then clear the First row contains column headings selection.
- For Excel or Access, select the Sheet, Named Range or Table name
- CSV files default to a comma as a column separator, though can use semi-colon or tab.
- Delimited Text files require a schema.ini.
Package
Use a Standard Report package set up in the current or any other accessible database. Note that total and subtotal rows are ignored (not imported).
- Select the database the package is in
- Click the browse button to select the package
- Select the Package type, then the required package
Data Import Options
Period to Import
This determines the first and last period (month or day for Summary or Daily files respectively) of data to be imported.
When selecting the Period to Import:
- The From period must not overlap with the locked part of the target file. For example, if the target file is locked to March, the first month must not be earlier than April.
- The To period must be the same as or after the From period.
- Only data within the periods will be affected by the import process.
- You can also use Period Tokens such as CM, RM, PM; allowing extra flexibility when used in AutoTasks.
Currency
Select the currency of the values in the source file.
Data to Import (Sales databases only)
Select whether to import Units, ASP or both. In order to import Values, select both Units and ASP. If a source file contains multiple data types (e.g. Units and Values) or you wish to import only ASP, then a field indicating data type is required, e.g indicating “A.S.P” as a complex expression for data type.
Add to Existing Values
If this option is selected, IFP will add source file values to any existing values in the target file for the same period. This can be useful if two or more parts of a forecast for each item are maintained in separate files. If this is not selected, the source file will simply overwrite existing data.
If you have multiple records for the same item code in a source file that need to be summed, then Add to existing values must be selected, otherwise only the last record will be imported.
Clear Target File First
If this option is selected, IFP will delete all data in the data file for the time period selected, regardless of which item codes are in the source file. To only delete data for specific item codes, see Clearing Specific Item Codes in a Data File.
Advanced Options
Further settings may be made on the Advanced Options tab:
Conversion Table
This allows a Code Conversion Table to be applied to the data in the source file during importing e.g. to convert item codes from a different source system, split an input value between multiple codes, etc. Clicking the browse button will allow you to select the relevant conversion table from the list available in the database. To remove a previous selection, click the Clear link.
Import data comments if found
If the source file was exported from IFP with comments, then these can be re-imported by selecting the option.
Field Mappings
A field mapping indicates which column(s) in the source file map to which field (column) in IFP. An appropriate field mapping may be supplied with your database - click Load field mappings and select the appropriate one. Simple one-to-one mappings can also be created by selecting the IFP field from the dropdown, selecting be mapped to [] in the source data and select the relevant column in the source sample. If you have just mapped January and the columns containing February through December follow in the source, then clicking Auto Map will automatically set the mappings for the remaining months.
For more complex mappings (where multiple fields need to be concatenated or partial fields are required), please contact Support for help with their creation.
Common Issues
Column / Mapping mismatch
A mismatch between the source file column names and the field mappings will generate the error message below. Check that the correct source file and field mappings have been selected. If so, and the source file format has changed (columns renamed), then the field mappings should be updated for the changed column(s) indicated in the error message.
Target Data File Locked
When clicking Next on Data Import Options an error message appears informing you the file is locked. If the selected import period is correct, check if the data file has been incorrectly locked.
Invalid characters in sheet name
Excel sheet or Access table names containing unusual characters such as / or \. Rename the sheet or table.
Column headers not as expected or show as data
First row contains column headings is selected and the first row contains anything other than column headers. If there are extra header or blank rows, remove these from the source file. If the source file does not contain headers, deselect First row contains column headings.
First row contains column headings is not selected and the first row in the source file does contain column headers (the column headers in the sample will show as F1, F2, etc). Either select First row contains column headings or remove the header row from the source file as required.
Some values imported as zero
A mixture of text and number cells within a specific column - some values may be ignored (zero will be imported). Note that in Excel, simply changing the column format to text or numeric will not fix this issue.
Data Appears without Columns or Headings
For text files, a schema.ini file is required. If this is not in the same folder as the source file, or the source file name does not match that in the schema.ini file, then IFP will not know how to split the text file. When this happens, the preview of the file appears without proper headings in columns, similar to the example below. The solution is to save the download file with the correct name (refer to your country-specific topics) in the same folder as the schema.ini file. Note that if the source file is a Standard IFP file, but the Import type was set to External File, the sample data will also look like this. In this case, go Back through the wizard and select Standard IFP as the Import type.
Possible Warning Messages
Code 'ABC01_AB_01' was not found. - when importing units / values the code to be imported does not currently exist in the database. You will need to either run an Item Code Import which includes the missing code, add the item manually or use an exceptions template (if available).
'Code' or ‘Description’ field cannot be empty - if shown at the end of a list this could be due to rows beneath the final line of data in your import file that have been formatted (there may not be any visible entries) and therefore exist. These errors can be ignored and can be removed using Delete/Clear Contents on the blank rows in the import file.
If these are shown in the middle of the data, you should check your import file to ensure there are no blank lines in the middle of the data.
Comments
0 comments
Please sign in to leave a comment.