Importing From Excel

A Microsoft Excel spreadsheet can be used to make changes to objects in an AFT Fathom model. The values in the Excel file are located in specific locations as described in the Setting Up Excel section.

Setting Up Excel

When reading an Excel file, AFT Fathom will search for a worksheet named AFT Transfer. There can be other worksheets in the workbook but Fathom will only look for a sheet with this name. The file structure looks like the following:

A spreadsheet that shows the structure of Excel imports.

Figure 1: Sample AFT Transfer Excel Sheet

Row 1 and column A are not read by AFT Fathom - these cells can have any value in them, though it is recommended to enter the values above for the header row.

Each column has certain accepted values, which can depend on the values in neighboring columns.

Note: A template spreadsheet containing validation rules for the required layout is included in the AFT Fathom installation, or can be downloaded here. By default the file path is: C:\AFT Products\AFT Fathom\AFT Fathom Excel Import Workbook.xlsx

Apply Column

This column accepts Yes or No. Only changes marked as Yes will be imported into the model.

Object Type

This column must be a junction name or Pipe. Valid choices can be seen below in Table 1.

Object Number

This corresponds to the junction or pipe number.

Parameter

The specific parameter to be changed. These parameters vary based on the Object Name selected. Each parameter has a type (C/S/I) associated with it, as is explained in the next section.

Table 1: Object Types, Associated Parameters, and Change Codes

   Pipe

S Additional K Factor

S Ambient Fluid Velocity

S Ambient Temperature

S Buried Pipe Depth

S Design Factor - Pipe Fittings & Losses

S Design Factor - Pipe Friction

S Design Factor - Pipe Heat Transfer

S External Heat Flux/Tracing Flux

S External Heat Rate

S ID Reduction (Scaling)

S Initial Guess Flowrate

S Inner Diameter (for unspecified, cylindrical pipes)

S Length

I Nominal Size Increment

C Nominal Size Set (must match text in Pipe Property Window)

C Nominal Type Set (must match text in Pipe Property Window)

S Noncylindrical Pipe Flow Area

S Noncylindrical Pipe Wetted Perimeter

C Pipe Name

S Rectangular Duct Height

S Rectangular Duct Width

S Roughness Value (for unspecified friction models)

I Sizing/Cost Setting

S Soil Surface Temperature

I Special Condition

   Area Change

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

S User Specified K

   Assigned Flow

S Design Factor

S Elevation

S Flow Rate

S Initial Guess Pressure

C Junction Name

I Sizing/Cost Setting

I Special Condition

S Temperature

   Assigned Pressure

S Design Factor

S Elevation

C Junction Name

S Pressure

I Sizing/Cost Setting

S Temperature

   Bend

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

S User Specified K

   Branch

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Outlet Elevation

I Sizing/Cost Setting

S Source/Sink Flow Rate

S Source/Sink Temperature

   Check Valve

S Design Factor

S Forward Velocity to Close Valve

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss Value

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

   Control Valve

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss When Fully Open

S Outlet Elevation

S Setpoint

I Sizing/Cost Setting

I Special Condition

   Dead End

S Initial Guess Pressure

S Initial Guess Temperature

S Elevation

C Junction Name

I Sizing/Cost Setting

   General Component

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S K Factor

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

   Heat Exchanger

S Controlled Outlet Temperature

S Design Factor - Friction Loss

S Design Factor - Heat Transfer

S Heat Flow Into System

S Heat Transfer Area

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S K Factor

S Outlet Elevation

S Overall Heat Transfer Coefficient

S Secondary Fluid Flow Rate

S Secondary Fluid Inlet Temperature

S Secondary Fluid Specific Heat

I Sizing/Cost Setting

I Special Condition

   Jet Pump

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Outlet Elevation

I Sizing /Cost Setting

I Special Condition

   Orifice

S Area/Diameter

S Design Factor

S Exit Pressure

S Exit Temperature

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss Value

S Outlet Elevation

I Sizing/Cost Setting

   Pump

S Control Setpoint

S Delta Pressure to Re-Open Combined Check Valve

S Design Factor

S Fixed Flow Rate

S Fixed Speed (%)

S Forward Velocity to Close Combined Check Valve

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Liquid Depth

S Liquid Surface Pressure/HGL

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

S Submerged Suction Pressure

S Submerged Suction Temperature

S Trimmed Impeller Ratio as Percent

   Relief Valve

S Blowdown Pressure

S Design Factor

S Exit Head/Pressure

S Exit Temperature

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss Value

S Outlet Elevation

S Overpressure

S Remote Pressure

S Set Pressure

I Sizing/Cost Setting

I Special Condition

   Reservoir

S Cross-Sectional Area

S Design Factor

C Junction Name

S Liquid Surface Elevation

S Liquid Surface Pressure

S Liquid Temperature

I Sizing/Cost Setting

I Surface Elevation Type

S Tank Bottom Elevation

 

   Screen

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S K Factor

S Outlet Elevation

I Sizing/Cost Setting

   Spray Discharge

S Cd (Discharge Coefficient)

S Design Factor

S Discharge Flow Area

S Elevation

S Exit Pressure

S Exit Temperature

S Initial Guess Pressure

S Initial Guess Temperature

C Junction Name

I Number of Holes

I Sizing/Cost Setting

I Special Condition

   Three Way Valve

S Actual Percent Open

S Design Factor

S Elevation

S Initial Guess Pressure

S Initial Guess Temperature

C Junction Name

I Sizing/Cost Setting

   Tee or Wye

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Elevation

C Junction Name

I Sizing/Cost Setting

   Valve

S Design Factor

S Exit Pressure

S Exit Temperature

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss Value

S Open Percentage

S Outlet Elevation

S Restricted Flow Area

I Sizing/Cost Setting

I Special Condition

   Venturi

S Design Factor

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Loss Value

S Outlet Elevation

I Sizing/Cost Setting

   Volume Balance

S Initial Guess Pressure

S Initial Guess Temperature

S Elevation

C Junction Name

I Sizing/Cost Setting

   Weir

S Channel Width

S Downstream Bottom Elevation

S Downstream Cross-Sectional Area

S Initial Guess Pressure

S Initial Guess Temperature

S Inlet Elevation

C Junction Name

S Notch Width/Angle

S Outlet Elevation

I Sizing/Cost Setting

I Special Condition

S Surface Pressure

S Upstream Bottom Elevation

S Upstream Cross-Sectional Area

S Height of Weir

I Weir Type

 

Change Code

Each Parameter has restrictions on how it can be changed. All of the Parameters are decimal numbers, integers, or strings.

Table 2: Valid Change Codes for each type of Parameter

Decimal Number (S)

Integer Number (I)

String (C)

Set equal to value

Set equal to value

Set equal to value

Change by value (+/-)

Change by value (+/-)

Add string to list

Change by percent (+/-)

Delete string from list

Value

The actual value to change the Parameter to or by.

For special conditions there are specific values assigned to each special condition option, as are listed in the table below.

Table 3: Values used to import special conditions

Junction

Special Condition

Value

Assigned Flow

None

Closed

0

1

Check Valve

None

Closed

Open

0

1

2

Control Valve

None

Closed

Fully Open - No Control

0

1

2

General Component

None

Closed

0

1

Heat Exchanger

None

Ignore Heat Transfer

0

2

Jet Pump

None

Closed

0

1

Pump

None

Pump Off No Flow

Pump Off With Flow Through

0

1

2

Relief Valve

None

Failed Open

Ignore Relief Valve

0

2

5

Spray Discharge

None

Closed

0

1

Valve

None

Closed

0

1

Weir

None

Ignore Weir

0

5

Scenario Path Name

The Scenario that the changes are intended to apply to. If no scenario is present, the changes will be applied to the current scenario.

It is important that the Scenario name is uniquely qualified - if there are multiple scenarios with the same name, the full Scenario Path Name can be used. Scenario Path Names can easily be copied from AFT Fathom by right-clicking the scenario name and selecting "Copy Scenario Path Name."

Importing the File

When the sheet has been completely filled out, it can be imported from File -> Import Excel Change Data. When importing the data, the Object Change Log will appear, listing the changes that were made and any errors that occurred during the import.

Table 4: Excel Importing Error Messages

Error

Description

Scenario name is not unique in the model (use full scenario path name)

There are multiple scenarios with this name. Use the fully qualified path name (e.g. Base Scenario\US Units\Pump A).

Invalid change type

The Change Code is not valid for the Parameter. For example, trying to change a string type with Change by Value (+/-).

Invalid parameter ID

The parameter given does not match the object type (e.g. Length for a junction).

Invalid parameter value

The value cannot be changed because the value is not valie. For example, a pump speed cannot be negative.

Object not found

A pipe or junction with the given ID was not found.

Parameter conflict

The value cannot be changed because it conflicts with another setting. For example, the loss value cannot be set when the junction is using a resistance curve.

Scenario not found in the model

No scenario by the given name was found in the model.

Note: In order for the junction to accept the changes, some fields must already contain data. For example, if the control setpoint for a control valve is being changed, there must already be a setpoint and units entered into the control valve junction object. This is to ensure that the correct set of units are being used. These values can be set using Global Edit to an arbitrary value before the changes are made from Excel.