Preparing Spreadsheet for Export to Dataset

The purpose of this supporting function is to prepare an existing spreadsheet so that the information can be exported to a dataset in M3 via a PC file.

The contents of the spreadsheet can either be budget values or a forecast according to corresponding Transaction Type - Statistics in the dataset. These contents can either complement or replace existing data in the dataset.

Note that data can only be imported for a year at a time and that information about the date can not be retrieved from the spreadsheet. The date must, instead, be entered manually when the data is transferred in 'Sales Stats/Budget. Import from PC File' (OSS940).

Before you start

Before preparing a spreadsheet for export to dataset, these prerequisites must be met:

Follow these steps

The scope of the supporting function is described below:

  1. Inserting control column

    A column must be entered on the sheet. The column is used to specify the field name of the dataset for the balance key or the accumulator field that are represented by the rows on the sheet.

  2. Inserting control row

    A row must be entered on the sheet. The row is used to specify the field name of the dataset for the balance key or the accumulator field that are represented by the columns on the sheet.

  3. Marking *FRF-cell

    Enter *frf in the first column of the control row. This selection is used to identify the control column and control row.

  4. Marking off non-current rows and columns

    Rows and columns that contain information that will not, or cannot, be transferred to M3 are marked off. A column is marked off by typing *txt in the column in the control row. Rows are marked off by typing *txt in the row in the control column.

  5. Connecting cells to database field

    Columns that contain balance keys or accumulator values must be marked with the field name the information corresponds to in the dataset. This is done by typing the field name in the control row for each column.

    Rows that contain balance keys or accumulator values are also marked with a field name. The field name is printed in the control column for each row.

  6. Adding period number

    A period number must be entered for every row or column that contains an accumulator field. This information is necessary because period numbers are retrieved from the spreadsheet when exported.

    The period number can easily be added to the spreadsheet manually, either by copying it or by using the sheet's arithmetic functions.

  7. Marking repetitive key values

    If a column contains repetitive key values, that is if various rows in a column belong to the same key value, every row must be marked. In other words, it is not enough just to mark the first row.

Examples

These examples illustrate how a spreadsheet must be changed to enable the export of information to a dataset in M3.

Related topics