OnePager Express Import of Data from Microsoft Excel for Version 6.0
About Importing Microsoft Excel Data to OnePager Express
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 may be useful for the creation of a project view. 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 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.
1) There are four data types that OPX looks for when importing data from Microsoft Excel:
- (1) Dates
- (2) Numbers
- (3) Boolean (TRUE or FALSE)
- (2) Strings (A through Z, 0-9, special characters)
2) All data, regardless of data type, are imported from Microsoft Excel.
- a) OPX begins this analysis from top-left to bottom-right.
- b) OPX ignores merged cells in fields and merged cells in rows.
- c) 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.
3) Using the second page of the OPC form shown above, you may 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:
4) When the Create new project view button is selected, OnePager uses your field assignments to create the project view.
Intelligent Changes Made to OnePager Express Current Templates
1) OPX is shipped to you with a predefined set of Templates that are based upon the BlueGrass Project 2J-303 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 Templates are based upon this Microsoft Excel source plan.
2) Since the flexibility of Microsoft Excel needed to be considered in the design of OPX, it was necessary to update the current Templates based upon your Microsoft Excel source plan.
3) Accordingly, OPX uses the analysis techniques discussed above to modify the current Template. 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 will, therefore, make intelligent recommendations on Microsoft Excel source plan field usage based on the findings of the intelligent engine. You may change or override these findings as follows:
- a) You may click the Edit current Template… button also on the second page of the OnePager choices (OPC) form shown above.
- b) Clicking this button brings up the Template Properties form at the Rows and Swimlanes tab.
- c) 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 source plan contains a field heading that exists in the Template, OnePager will use that fields. Otherwise, it will make an intelligent recommendation from the field headings it discovers.
- d) 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.
- e) Note in the highlighted rectangles above, that OPC picked up the Task Name field for collecting tasks into rows.
- f) To access the form for selecting additional text column names, click the Text Column Properties... button to bring up the Custom Text Column form as shown above.
- g) The illustration above shows that the “Lemgth” field from the Microsoft Excel source plan is selected as the text column to appear in the project view.
- h) 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 may be somewhat misspelled.
- i) 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.
- j) 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.
- k) If OPX cannot find a field header name in the Microsoft Excel source plan that can be correlated with a specific OPX purpose, OPX will make as intelligent a guess as possible and you are then able to make any change necessary.
4) You are cautioned that all current Templates will be altered when used in this way. We recommends that unique Template names be subsequently given to such modified Templates for future reference and for sharing these modified Templates with others.
Missing Fields, Field Headers, or Misspelled Field Headings
5) OPX also checks field headings when importing data in UPDATE mode. If the field was used in previous snapshots for any purpose, OnePager will check to assure that the new import for the snapshot has those same fields.
6) 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 will provide a warning message as shown in the example below:
7) This circumstance can occur when you inadvertently attempt to update a project view 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.
8) You are given two choices when the message above is displayed:
- (1) By selecting the Yes button, the import will proceed. OnePager may blank some values that it cannot find. Note: The results may be unexpected.
- (2) Selecting the No button will tell 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.
Basic Workflows (Portal) specifically those articles provided for OPX.