Difference between revisions of "OnePager Express Import of Data from Microsoft Excel"

Revision as of 22:44, 17 September 2015

OnePager Express imports data from your Microsoft Excel and allows you to select which of the Microsoft Excel columns to process and how they are to be processed through the “OnePager choices” form. Since Microsoft Excel is so flexible, OnePager is constructed with significant intelligence to determine how you want to represent the imported data. This built in intelligence is applied in two ways:

OnePager scans all columns and rows of the Microsoft Excel plan and looks for column headers that may be useful for the creation of a project view. Once this analysis is complete, OnePager Express populates the second page of the “OnePager choices” form with its findings as shown below:

OnePager Express recognizes information in the various columns after finding and analyzing the column headers and after examining the type of data in each column. OnePager Express intelligently makes changes to the current template settings to reflect these findings and reports them to you in the “OnePager choices” form shown above.

Data Import to OnePager Express

What follows is a discussion of how OnePager Express applies it’s built in intelligence functions for you. Our goal here is to provide sufficient understanding of the process such that you know what to expect from OnePager Express when various heading names and data types are encountered by OnePager Express from your Microsoft Excel plan.

There are four data types that OnePager Express looks for when importing data from Microsoft Excel:

• Dates
• Numbers
• Boolean (TRUE or FALSE)
• Strings (A through Z, 0-9, special characters)

All data, regardless of data type, are imported from Microsoft Excel.

a) OnePager Express begins this analysis from top-left to bottom-right.

b) OnePager Express ignores merged cells in columns and merged cells in rows.

c) Once the column headers are located and the data types are analyzed, OnePager Express associates these columns with specific usages in the “OnePager choices” form as illustrated above.

Using the second page of the “OnePager choices” form shown above, you may change the column usage by selecting another column to serve the function from the drop-down list provided. The drop-down list is built from all the column headers that OnePager Express found during its analysis pass. An example of a drop-down list of column header names taken from a Microsoft Excel plan is shown here:

When the “Create new project view” button is selected, OnePager uses your column assignments to create the project view.

Intelligent Changes Made to OnePager Express Current Templates

OnePager Express is shipped to you with a pre-defined set of templates that are based upon the “BlueGrass Project 2J-303” often used in this Reference Guide for example purposes and available to you from the OnePager Express Tutorial. This means that all column-usage settings within these shipped templates are based upon this Microsoft Excel plan.

Since the flexibility of Microsoft Excel needed to be considered in the design of OnePager Express, it was necessary to “update” the current templates based upon your Microsoft Excel plan.

Accordingly, OnePager Express uses the analysis techniques discussed above to modify the current template. This is necessary because, unlike Microsoft Project, where the columns have pre-defined meanings, the Microsoft Excel column assignments and meaning are totally determined by you. OnePager Express will, therefore, make intelligent recommendations on Microsoft Excel column usage based on the findings of the intelligent engine. You may change or override these findings as follows:

1) You may click the “Edit current template…” button also on the second page of the “OnePager choices” form shown above.

2) Selecting this button brings up the “Template Properties” form at the “Rows and Swimlanes” tab.

3) The settings found in the “Row & Swimlanes” tab are determined by the current template and by the intelligent analysis described above. If your Microsoft Excel plan contains a column heading that exists in the template, OnePager will use that column. Otherwise, it will make an intelligent recommendation from the column headings it discovers.

4) The figure below shows that several of the settings in the “Template Properties” form’s “Rows & Swimlanes” tab reflect the decisions that OnePager made concerning column usage. These settings are consistent with the selections displayed in the “OnePager choices” form.

a) Note in the highlighted rectangles above, that OnePager Express picked up the “Task Name” column for collecting tasks into rows, the “Lemgth” column for labeling rows, and again the “Lemgth” column for ordering rows. We purposely misspelled “Length” to illustrate that OnePager Express has the intelligence to pick columns even though their headings may be somewhat misspelled.

b) At this point you may change the Rows and Swimlane settings as well as settings in any of the other tabs. When you are ready, click “Save and use” button to make these changes part of the current template.

c) In other tabs of the “Template Properties” form, OnePager Express has adjusted settings in a similar way to be consistent with its intelligent engine’s analysis of the Microsoft Excel column headers and data types.

d) If OnePager Express cannot find a column header name in the Microsoft Excel plan that can be correlated with a specific OnePager Express purpose, OnePager Express will make as intelligent a guess as possible and you are then able to make any change necessary.

5) You are cautioned that all current templates will be altered when used in this way. Chronicle Graphics recommends that unique template names be subsequently given to such modified Templates for future reference and for sharing these modified templates with others.

Missing Columns, Column Headers, or Misspelled Column Headings

OnePager Express also checks column headings when importing data in UPDATE mode. If the column is used in previous snapshots for any purpose, OnePager will check to assure that the new import for the snapshot has those same columns.

When OnePager Express cannot find the expected columns, one or more column heading names are missing or blank, the column heading is unrecognizably misspelled, or the data type is different from the previous snapshot, OnePager Express will provide a warning message as shown in the example below:

This circumstance can occur when you inadvertently attempt to update a project view from the incorrect Microsoft Excel plan or the desired Microsoft Excel plan was altered to the extent that OnePager Express cannot find the columns needed to accomplish the UPDATE functions.

You are given two choices when the message above is displayed:

a) By selecting the “Yes” button, the import will proceed. OnePager may blank some values that it cannot find. Note: The results may be unexpected.

b) Selecting the “No” button will tell OnePager Express to stop the import process and return control to Microsoft Excel. At this point you may want to correct the Microsoft Excel plan or select another one and start the UPDATE process again.

(4.4)