Difference between pages "Automatically Cropping Empty Rows for OnePager Express for Version 6.1" and "OnePager Express Import of Data from Microsoft Excel for Version 7.2"

Revision as of 14:40, 31 May 2022

About Importing Microsoft Excel Data to OnePager Express

Illustrations used in this article are from OnePager Express but the feature's function, controls, and manual edits apply equally to OnePager Bundle when the source plan imported into OnePager Bundle is Microsoft Excel.

OnePager Express (OPX) imports data from your Microsoft Excel source plan and allows you to select which of the Microsoft Excel source plan fields to process and how they are to be processed through the OnePager choices (OPC) 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:

1) OnePager scans all your Microsoft Excel source plan fields and looks for field headers that can be useful for the creation of a chart. Once this analysis is complete, OPX populates the second page of the OPC form with its findings as shown below:

2) OPX recognizes information in the various Microsoft Excel source plan fields after finding and analyzing the field headers and after examining the type of data in each field.

OPX intelligently makes changes to the current Template Properties form's settings to reflect these findings and reports them to you in the OPC 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 so you know what to expect from OnePager Express when various field heading names and data types are encountered by OnePager Express from your Microsoft Excel source plan. There are four data types that OPX 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. OPX begins this analysis from top-left to bottom-right. OPX ignores merged cells in fields and merged cells in rows. Once the field headers are located and the data types are analyzed, OPX associates these fields with specific usages in the OPC form as illustrated above. Using the second page of the OPC form shown above, you can change the field usage by selecting another field to serve the function from the dropdown list provided. The dropdown list is built from all the field headers that OPX found during its analysis pass. An example of a dropdown list of field header names taken from a Microsoft Excel source plan is shown here:

When the Create new Chart button is clicked, OnePager uses your field assignments to create the chart.

Intelligent Changes Made to OnePager Express Current Template Properties Forms

OPX is shipped to you with a predefined set of Template Properties forms that are based upon the BlueGrass Project Plan Report - Express used in this Wiki for example purposes and available to you from the OPX tutorial. This means that all field usage settings within these shipped Template Properties forms are based upon this Microsoft Excel source plan. Since the flexibility of Microsoft Excel needed to be considered in the design of OPX, it was necessary to update the current Template Properties form based upon your Microsoft Excel source plan. Accordingly, OPX uses the analysis techniques discussed above to modify the current Template Properties form. This is necessary because, unlike Microsoft Project, where the fields have predefined meanings, the Microsoft Excel source plan field assignments and meaning are totally determined by you. OPX, therefore, makes intelligent recommendations on Microsoft Excel source plan field usage based on the findings of the intelligent engine.

You can change or override these findings as follows:

1) You can click the Edit current Template… button also on the second page of the OnePager choices (OPC) form shown above.

2) Clicking 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 Properties form and by the intelligent analysis described above.

4) If your Microsoft Excel source plan contains a field heading that exists in the Template Properties form, OnePager uses that fields.

Otherwise, it makes an intelligent recommendation from the field headings it discovers. 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 field usage. These settings are consistent with the selections displayed in the OPC form.

Note in the highlighted rectangles above, that OPC picked up the Task Name field for collecting tasks into rows. To access the form for selecting text column names, click the Text Column Properties... button to access the Custom Text Column form as shown above. The illustration above shows that the “Lemgth” field from the Microsoft Excel source plan is selected as the text column to appear in the chart. The “Lemgth” field is indicated in the Template form's Rows/Swimlanes tab for ordering rows. We purposely misspelled Length to illustrate that OPX has the intelligence to pick fields even though their headings are somewhat misspelled. You can 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 Properties form.

In other tabs of the Template Properties form, OPX has adjusted settings in a similar way to be consistent with its intelligent engine’s analysis of the Microsoft Excel source plan field headers and data types. If OPX cannot find a field header name in the Microsoft Excel source plan that can be correlated with a specific OPX purpose, OPX makes as intelligent a guess as possible and you are then able to make any change necessary. You are cautioned that all current Template Properties forms are altered when used in this way. We recommend that unique Template Properties form names be subsequently given to such modified Template Properties form for future reference and for sharing these modified Template Properties form with others.

Missing Fields, Field Headers, or Misspelled Field Headings

OPX also checks field headings when importing data in UPDATE mode. If the field was used in previous snapshots for any purpose, OnePager checks to assure that the new import for the snapshot has those same fields. When OPX cannot find the expected fields, one or more field heading names are missing or blank, the field heading is recognizably misspelled, or the data type is different from the previous snapshot, OPX provides a warning message as shown in the example below:

This can occur when you inadvertently attempt to update a chart from the incorrect Microsoft Excel source plan or the desired Microsoft Excel source plan was altered to the extent that OPX cannot find the fields needed to accomplish the UPDATE functions. You are given two choices when the message above is displayed:

Select Yes

By selecting the Yes button, the import proceeds. OnePager blanks some values that it cannot find. Note: The results can be unexpected.

Select No

Selecting the No button tells OPX to stop the import process and return control to Microsoft Excel. You can now correct the Microsoft Excel source plan or select another one and start the UPDATE process again.

Related Links

Basic Workflows (Portal) 7.0.1-72

Merging the Wrong Tasks into a Chart - OnePager Express 7.12.1-72

Data-Driven Task Links for OnePager Pro and Express 19.1.1.1-72

(4.0.1.4-72)