Import Data into Modules Example
In this example, a source file contains an actuals download for April to Jun 2013. This organization's financial year happens to start in April, and the accounting system denotes the financial year by the year in which it ends.
01 FY13 represents the first month of the financial year ending in 2014, so that translates to the calendar month of April 2013.
The Organization Profit Centers are represented by codes in the actuals import file, so M01, M02 represent the manufacturing profit centers, C01, C02 represent the consulting profit centers and H01, H02 represent the head office cost centers. In this example, data will be imported only for the M01 and M02 Profit Centers for April 13 – June 13.
ActualsImport1.txt is a tab separated text file. Here is a portion of the import file showing how the data set is arranged in columns and rows:
To import this data perform the following steps:
- Open the target module. For this example, the Income Statement target module has line items as rows, time as columns, a page for each Profit Center (CO1, C02, H01, H02, M01, and M02) and a page for each version (Actual, Budget, Forecast). The file needs to be imported onto the actuals page for the M01 and M02 Profit Centers for the first quarter.
- On the Data menu click Import.
- Click Upload New File and browse for the text file.
- The File Options dialog appears showing a preview of the import file.
- Change the Header Row to be row 3, and the First Data Row to be row 5.
- Click Next.
- On the Mapping tab, set up the mappings for the import from the source file import columns into the target module dimensions:
- At the right-side of the Mapping tab, you can select which column in the source file to use for the target module line item values:
- Go through each of the mapping tabs in turn. For Income Statement Line Items, select Match on names or codes:
- Because we selected Fixed version on the main Mapping tab for this example, on the Versions tab select the Actual version as the version into which data will be imported:
- On the Organization tab, select to Match on names or codes. (The codes were set in the Settings tab on the Organization list grid view).
- Time: Set the time format:
- Select Periods and set up a custom format MM???YY (for 01 FY12 , 02 FY12 etc).
- Choose the option such that the year number represents the end of the financial year and the month number represents the financial year month.
- Clear all time periods prior to import.
- Click Run Import. The Import Completed dialog appears:
- The Result Summary tab reports on the import process. In this example, there are 3 failures for each target line item. If we select the Details tab, we see that the failures are due to the fact that the source file contained data for a M03 Profit Center, which does not exist in the target module's Organization list:
- If you have any lines that failed in the import as they were not present in the target, you can insert the item in the list and re-run the import. (All the mapping and options are preserved).
- Click Close, then view the results of the import in the Income Statement target module:
Note that here we have selected to map a Fixed version to the Versions dimension — see step 10 below for how to complete this mapping.
On the right-side of the Organization tab, you can select the option you want to use for clearing the target items prior to importing the data. Here, All items has been selected: