This example shows how to import data into a module. It illustrates how to map an import to map an import of a single column of data, a fixed version, line items, and Time.
The source file, actuals-import.txt is a tab-separated TXT file. It contains actuals for Jan to Mar 2022 and is structured as follows:
|M01||Cost of Goods||01 FY22||-62348|
|M01||Cost of Goods||02 FY22||-63450|
|M01||Cost of Goods||03 FY22||-62980|
|M01||Gross Margin||01 FY22||55198|
|M01||Gross Margin||02 FY22||57898|
|M01||Gross Margin||03 FY22||55674|
The Organization profit centers are represented by codes. M01, M02, and M03 represent the manufacturing profit centers. The sample above only shows the first few rows, but rows for M02 and M03 follow the same structure.
The Account column designates the type of account data: Revenue, Cost of Goods, and so on.
FY Month gives the time period for the data. The first two digits give the month, so 01 is January. FY22 gives the financial year as 2022.
Amount is the column for the data itself. So, in the first data row, 117546 is the Revenue for 01 FY22, for the manufacturing profit center M01.
The Income statement target module has line items on rows, Time on columns, and the Organization list and Versions as page selectors.
|Actual||M01 Profit center|
|Jan 22||Feb 22||Mar 22||Q1 FY22|
|Cost of Goods||0||0||0||0|
The Organization list, which contains list items for the profit center codes, has items for M01 profit center and M02 profit center, but not M03 profit center.
We can break down the import of this data into four stages:
- Upload the source file.
- Select the file options.
- Map the import
- Run the import.
To upload the source file:
- Open the target module.
- Select the Data menu from the toolbar at the top.
- Select Import.
- Select Upload New File and choose actuals-import.txt in the file upload dialog.
- Select OK.
The File Options dialog opens.
Most file options are detected automatically. For example, the source file is a tab-separated TXT file, so Tab is selected as the column separator. However, there's a gap between the header row and the first data row, so you need to enter 3 in the First Data Row.
You can optionally use the Set Default File menu to share the import with Everyone or Admins Only. In this case no one else needs to run the import, so this is left as No (keep private).
When you've selected the file options you want, select Next.
To map the import:
- In the Mapping tab of the Import dialog, select the source columns you want to act as a source for the target dimensions.
The Organization column is automatically mapped to the Organization list in the module.
- Select FY Month in the Source dropdown for Time.
- Select Fixed version in the Source dropdown for Versions.
- Select Account in the Source dropdown for Income statement Line Items.
When you select this, the import automatically detects that the final column, Amount, contains the data. This displays as selected in the Data values from column on the right.
- Select Only update imported cells on the right of the Mapping tab.
For an example of how imports work if you select Clear target prior to import, see Clear target items prior to module imports.
- Go to the Time tab and configure the timescale:
- Under How to map the source data to the timescale, select Periods.
- In the Custom fixed-position pattern field, enter MM???YY.
MM indicates the position of the digits that relate to months, the question marks indicate characters to ignore, and YY indicates the position of digits that relate to the year.
- In the Month number represents dropdown, select Fiscal Year Month.
- In the Organization tab, select Map items manually.
As M01 and M02 do not match M01 Profit center and M02 Profit center exactly, these do not automatically map. M03 Profit center is not present in the target dimension, so M03 displays without a match.
To map M01 and M02 manually:
- Select M01 under Source items, then select M01 Profit center under Target items.
- Select M02 under Source items, then select M02 Profit center under Target items.
- In the Versions tab, select Actual from the dropdown.
- In the Income statement Line Items tab, select Match on names or codes.
Select Run the import in the bottom right of the Import dialog.
When the import completes, an Import Completed dialog displays the results. Three items display as ignored for every line item, and a message advises that this is because M03 was not mapped.
You can add M03 Profit center to the Organization list, follow the same steps above, and match M03 to M03 Profit center at step 4. Or if you want the import to ignore M03, you can select Ignore Unmapped on the Organization tab. The items still show in the results as ignored, but the explanatory message no longer displays.