Importing Excel Data
A Microsoft Excel spreadsheet can be used to make changes to objects in an AFT xStream 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 xStream will search for a worksheet named AFT Transfer. There can be other worksheets in the workbook but xStream will only look for a sheet with this name. The file structure looks like the following:
Figure 1: Sample AFT Transfer Excel Sheet
Row 1 and column A are not read by AFT xStream - 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 xStream installation. By default the file path is: C:\AFT Products\AFT xStream\AFT xStream Excel Import Workbook.xlsx, or the spreadsheet can be downloaded here.
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 Convection Coefficient External S Convection Coefficient Internal S Design Factor - Pipe Fittings & Losses S Design Factor - Friction S External Heat Flux/Tracing Flux 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) C Pipe Name S Roughness Value (for unspecified friction models) I Special Condition |
Area Change S CdA (for sonic choking) S Elevation C Junction Name S Loss Value |
Assigned Flow S Flow Setting S Flow Temperature S Elevation C Junction Name I Special Condition |
Assigned Pressure S Elevation C Junction Name S Pressure Setting S Temperature |
Bend S Elevation C Junction Name S User Specified K
|
Branch S Elevation C Junction Name |
Check Valve S CdA (for sonic choking) S Elevation S Forward Velocity to Close Valve C Junction Name S Loss Value S Pressure/Head to Reopen I Special Condition S Xt Value |
Compressor/Fan S Assigned Flow S Elevation C Junction Name S Nominal Efficiency I Special Condition S Speed (%) |
Control Valve S Control Setting S Elevation S Full Open Loss C Junction Name I Special Condition S Xt Value |
Dead End S Elevation C Junction Name |
General Component S CdA (for sonic choking) S Elevation C Junction Name S Loss Value I Special Condition |
Heat Exchanger S Heat Rate S Elevation C Junction Name S Loss Value I Special Condition |
Orifice S Area/Diameter S CdA (for sonic choking) SElevation S Exit Pressure S Exit Temperature C Junction Name S Loss Value |
Relief Valve S CdA (for sonic choking) S Elevation S Exit Pressure S Exit Temperature C Junction Name S Loss Value S Set Pressure I Special Condition |
Screen S CdA (for sonic choking) S Elevation C Junction Name S K Factor |
Tank S Elevation C Junction Name S Pressure S Temperature |
Tee or Wye S Elevation C Junction Name |
Valve S Exit Pressure S Exit Temperature S Elevation C Junction Name S Loss Value I Special Condition S Xt Value |
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 |
Compressor |
None Compressor Off No Flow Compressor Off Flow Through |
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 |
Relief Valve |
None Failed Open Ignore Relief Valve |
0 2 5 |
Valve |
None Closed |
0 1 |
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 xStream 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.