OnePager Live Beta 2 Import of Data from Microsoft Excel

From OnePager Documentation
Jump to: navigation, search

About Importing Microsoft Excel Data to OnePager Live

4.0.1.4-OPL2 Not edited beyond this point.

OnePager Express (OPX) imports data from your Microsoft Excel plan and allows you to select which of the Microsoft Excel columns 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 plan's columns and rows and looks for column 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:
X52-4 4-(1)-06082015.png
(2) OPX recognizes information in the various Microsoft Excel plan's columns after finding and analyzing the column headers and after examining the type of data in each column. 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 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.

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 columns and merged cells in rows.
c) Once the column headers are located and the data types are analyzed, OPX associates these columns 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 column usage by selecting another column to serve the function from the dropdown list provided. The dropdown list is built from all the column headers that OPX found during its analysis pass. An example of a dropdown list of column header names taken from a Microsoft Excel plan is shown here:

X52-4 4-(2)-06082015.png

4) 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

1) OPX is shipped to you with a predefined set of templates that are based upon the BlueGrass Project 2J-303 often used in this Wiki for example purposes and available to you from the OPX tutorial. This means that all column usage settings within these shipped templates are based upon this Microsoft Excel 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 plan.

3) Accordingly, OPX uses the analysis techniques discussed above to modify the current template. This is necessary because, unlike Microsoft Project, where the columns have predefined meanings, the Microsoft Excel plan column assignments and meaning are totally determined by you. OPX will, therefore, make intelligent recommendations on Microsoft Excel plan column 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 OPX form shown above.
b) Selecting 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 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.
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 column usage. These settings are consistent with the selections displayed in the OPC form.
X52-4 4-(3)-06082015.png
e) Note in the highlighted rectangles above, that OPC 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 OPX has the intelligence to pick columns even though their headings may be somewhat misspelled.
f) 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.
g) 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 plan column headers and data types.
h) If OPX cannot find a column header name in the Microsoft Excel 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. 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

5) OPX 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.

6) When OPX cannot find the expected columns, one or more column heading names are missing or blank, the column 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:

X52-4 4-(4)-06082015.png

7) 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 OPX cannot find the columns 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. At this point you may want to correct the Microsoft Excel plan or select another one and start the UPDATE process again.

Message to Assist with Setting Import Flags When No Data Are Imported

1) For OPP version 5.3 we’ve added a warning message should your selected Microsoft Project source file Flag field contain a No in every row. The warning message looks like this:

P53-0 2-(16)-10262015.png

2) You have two options at this point:

(1) go back to your source file and fix the condition by specifying the rows you want to import or
(2) select the right-hand option in the message form which tells OPP to import all rows from your Microsoft Project source file.

3) For OPX version 5.3 the approach is the same and the above warning form will be displayed if OPX detects that the designated Flag field contains all No, False or is blank.

Related Articles

Basic Workflows (Portal) 7.0.1-OPL2 specifically those articles provided for OPX.

Merging the Wrong Tasks into a Project View from Microsoft Project 7.11.1-OPL2 Merging the Wrong Tasks into a Project View from Microsoft Excel 7.12.1-OPL2

(4.0.1.4-OPL2)