Column Mapping

Alternative to importing an .xml, data can also be loaded from XLSX and CSV spreadsheets. When using a spreadsheet to import data the user must specify how that data is imported into Fuzor. While this may take more time on initial setup, this allows the user more control over how the data is imported. settings are preserved and can be exported and reused so that future updates are easier. For more information about which columns can be mapped see Task Table Columns.

Notably both WBS and Task Type cannot be populated with column mapping.

The column mapping menu can be opened from the 4D Load Options menu or by right clicking a mappable column header and selecting the button that appears.

The Load Spreadsheet button opens the file browser for the user to select a spreadsheet to load data from.

The Current Column Map list denotes which columns from the spreadsheet should be used to populate specified columns in Fuzor. When a spreadsheet is first loaded the list is empty.

When a spreadsheet is loaded the file name is displayed above the settings to add new mappings to the schedule. There are four drop down menus that control how a new mapping is added.

The Source Column denotes the column in the spreadsheet where the data is loaded from.

Other columns allow the user to select from available Fuzor columns.

Column Type specifies a particular column within a set of dates. Possible values are Duration, Start Date, End Date and all supported Cost Types. A Custom Column is also an option from this drop down menu

Task or Baseline specifies a particular schedule to populate.

Date Type specifies a particular set of durations and dates to populate. Options are Planned, Actual, Start and End, and Remaining.

The Add Column Mapping button adds the mapping specified by the drop down menus to the Current Column Map List

When all desired columns are mapped use the Apply button to load the data from the spreadsheet

When a spreadsheet is loaded in for the first time a task will be created for each row of the spreadsheet that is populated with data. Only the columns that are specified in the Current Column Map list are populated with data. By default data is loaded sequentially. That means that the data in the first row in the spreadsheet is used to populate the first task in the Gantt Chart.

Identifiers can be set to ensure that even if the data in the spreadsheet is sorted differently than the Gantt Chart chart that the association between tasks in Fuzor and rows in the spreadsheet are preserved when making updates.

If planning on making an update to the schedule with column mapping, setting an Identifier is essential.

Use the checkbox next to a Mapping to use that relationship as a Identifier. For example ID is the Identifier for the mapping shown to the right. As a result when updated, tasks are repopulated if the ID column in Fuzor matches the ID column in a row of the spreadsheet.

A column must be unique to be a reliable Identifier. Recommended options are ID and Name. Fuzor ensures that both of these are unique even for newly created tasks.

Custom Columns can also be used as Identifiers yet Fuzor does not have any internal systems ensuring that these would have duplicate values.

Other settings help the user manage the current Column Map or Save and Load other maps to manage different spreadsheets.

The Reset Button Clears the Current Column Map.

Removes the associated mapping

Denotes that the associated column mapping is clear of issues

Denotes that there are potential problems with an associated column mapping. This is likely a result of a Column Mapping that references a column which does not exist in the speadsheet.

If another schedule needs to be loaded a column map can be stored in the Saved Column Maps list. These maps are saved to the cache file but they can also be used to export and Load in another project

The Save button stores the Current Column Map in Saved Column Maps.

The Load button overwrites the Current Column Map with the currently selected mapping in the Saved Column Maps.

The Export button saves the mapping selected in the Saved Column Maps as XLSX or an XML.

The Import button opens the file browser for the user to select a previously saved mapping.

Different spreadsheet editors store data on export differently. The parse settings control how Fuzor reads column data and dates.

By default, Fuzor only reads three different Date and Time Formats. These are displayed at the top of the parse settings. If the spreadsheet that you are using has a different Date and time format check the Custom Input Checkbox.

Use the Sample textbox to specify a custom Date and Time format. The sample legend is displayed when custom input is checked.

For CSV it is important to specify the proper cell separator. Usually this is a comma. If Fuzor seems to be reading the available columns improperly it is likely that the spreadsheet is using a different separator.

XLSX files store date values an integer above zero. Where zero is in reference to a particular date and each subsequent day is represented by an increment of 1. This date is usually the 31st of December in 1899. However, it is not uncommon for a spreadsheet to be off by a few days. Microsoft Excel uses the following day: January 1^st as a reference date.

The Microsoft Excel radio button can be used to automatically change the reference date appropriately

The Other radio button will work for most spreadsheets

The Custom input radio button is used to edit the Reference Date.