The Spreadsheet Import panel

Content

Importing a simple, tabular spreadsheet
Importing spreadsheets with a complex cell structure
Reusing spreadsheet import tasks

Importing a simple, tabular spreadsheet

A flat tabular collection of data residing in a MS Excel spreadsheet can be imported in Synop Analyzer as a data source as follows:

• Select File → Import data from spreadsheet from Synop Analyzer's main menu.
• In the file selection dialog which opens up, choose the name of the Excel file which is to be opened.
• A new window named Spreadsheet opens up. In this window, you have to specify the name of the worksheet which contains your data (selector box Sheet name). The spreadsheet's first worksheet is preselected.
• Then you just have to press Start transformation. The data are read, and Synop Analyzer opens up an Input Data panel in which further user-defined data preparation and data specification steps can be performed.

Importing spreadsheets with a complex cell structure

In the following sections we will explain the features and functions of the spreadsheet import wizard at the example of the MS Excel file doc/sample_data/earnings_sheet.xls. The file contains the monthly earnings sheet for a small company with two locations for the period from January 2006 to March 2009. The figure below shoes a part of this Excel sheet:

In the present form, the data are not really suitable for being used by a forecasting and trend analysis tool: in the Excel sheet, meta data information (such as location, date or cost category) is intermixed with number cells, empty space cells, formula cells (such as EBT or Gross Profit II) and auxiliary title or text cells. Furthermore, the sheet contains accountant's corrections at year end (such as the column 13.2008 highlighted in the picture above); these corrections have to be distributed on the 12 months of the preceding year before the corresponding time series can be used for a forecast or trend analysis. We will see that Synop Analyzer supports various preprocessing steps on this input sheet in order to overcome the aforementioned problems.

From the Synop Analyzer main menu, we select File → Import data from spreadsheet. A file chooser dialog opens up.

We select the file doc/sample_data/earnings_sheet.xls in the file chooser dialog. A new Spreadsheet window opens on the main canvas:

The upper right part of the window contains several input fields in which we can specify how the spreadsheet is to be used. The lower part of the window shows the effects of these specifications.

• In the field Meta data rows we specify that the second and third row of the Excel sheet contain two different kinds of meta data information which we would like to use in our analysis. By typing 2:Location 3:Month we indicate that we want to refer to the meta data in row 2 under the label Location and to the meta data in row 3 under the label Month.
• Similarly, we indicate that the first column (A) of the Excel sheet contains a meta data information to which we want to refer under the label CostCategory.
• Our goal in this example is a cost structure analysis. Therefore, we only maintain the rows containing the various cost category figures and we discard the other figures such as Total Sales, Gross Profit, EBIT or EBT. That's why we type 1 4 5 6 8 16 18 20 21 into the field Ignored rows.
• The columns N, AA, AN, BA, BN and CA of the Excel sheet contain the accountant's corrections at year-end for the two locations. We want to distribute these corrections equally on the 12 months preceding the correction and discard the correction month 13. Therefore, we enter N AA AN BA BN CA in the input field Distributed columns.

The specifications decribed above are automatically reflected by an adapted coloring scheme in the tabular representation of the currently active spreadsheet in the lower part of the screen: spreadsheet cells containing meta data information are displayed with a green background, cells with values to be distributed among other cells have a blue background, cells which are to be ignored are grayed out and 'normal' value cells are displayed with white background.

Finally, we click on the Start Transformation button. An instant later, the pop-up window closes and the transformed flat file earnings_sheet.txt is written into our chosen target directory.

The generated file contains the columns Location, Month, CostCategory and Cost. The new file is suitable for a statistical analysis using the entire set of Synop Analyzer functions. The new file is suitable for being read into Synop Analyzer, and it is automatically opened in the Input Data panel.

Reusing spreadsheet import tasks

If you enter a file name into the input field named Parameter file name in the spreadsheet import opp-up window (the file name should end with .xml), then the specifications that you perform in the pop-up window will be saved to that file automatically when you press the button Start transformation. You can later load these settings by selecting File → Import data from spreadsheet, by changing the file type to parameter file (.xml) in the file chooser dialog and by navigating to the previously stored parameter file.

You can also save and re-load spreadsheet import settings as a part of a larger data import process. To that purpose, leave the spreadsheet import window by pressing Start transformation, optionally perform further data import settings in the left column of the main window and save the settings by selecting File → Save Data Load Task. In this case, your spreadsheet import settings are stored within the resulting parameter file. You can later execute this data load task by selecting File → Open Data Load Task.