Adapting Excel Spreadsheets for Data Import

Overview:

With the releases of PIPE-FLO 12 and 14, the data export/import capabilities of Xlink have been replaced by the DataLink and Data Import. This change greatly extends the flexibility of data manipulation, but has left some of our customers with orphaned XLink spreadsheets. These spreadsheets can be quickly adapted for DataLink by switching out the XLink functions for the Datalink dlq function. Microsoft® Excel can be utilized to quickly generate the Python™ script that is required to load values into PIPE-FLO via Data Import.

Bulk loading of demand values into a PIPE-FLO model was one of the common applications of XLink and will be used to illustrate these methods. In this case, the user was copying in current Flow Demand names and values from a .csv file into the comp_air_xlink.xlsx and using it to update those values in the Compressed air system.pipe model. These files are attached below.

Step-by-step guide:

The spreadsheet was used to query current flow settings from PIPE-FLO and assign new values that were copied into the New Flow column. For Xlink, this would have been done with the pfequery() function in the Current Flow and Units columns and pfeassign() for the Assign column.

  1. Open the Compressed air system.pipe model in PIPE-FLO Professional 14.
  2. Open the comp_air_xlink.xlsx spreadsheet in Excel.

We will use DataLink's dlq function to get the Current Flow and Unit values from the model. The required Datalink Add-in file should install by default in PIPE-FLO 14, but it can be downloaded and reinstallled here: DataLink Add-In Definition File for Microsoft Excel

  1. In PIPE-FLO, click the Send to Datalink button  in the toolbar to populate the ODBC database with current information.
  2. In Excel Cell B2,
    1. Enter the formula =dlq("flow_demand", "flow", A2) if using PIPE-FLO Professional version 14 or earlier
    2. Enter the formula =dlq("flow_demand", "calculated_flow", A2) if using PIPE-FLO Professional version 15 or later
  3. In Excel Cell C2,
    1. Enter the formula =dlq("flow_demand", "flow_unit", A2) if using PIPE-FLO Professional version 14 or earlier
    2. Enter the formula =dlq("flow_demand", "calculated_flow_unit", A2) if using PIPE-FLO Professional version 15 or later
  4. Select these two cells and drag the lower left corner to populate the entire columns. You should now see the current values from the model.

The next step is to use Excel to generate the Data Import script for all of the flow changes. For example, here is the syntax required to set a flow value:

  • pipeflo().doc().get_flow_demand( 'fd_3' ).set_flow( flow_rate( 123.0, scfh ) ) if using PIPE-FLO Professional version 14 or earlier
  • pipeflo().doc().get_flow_demand( 'fd_3' ).set_flow_demand_operation( flow_demand_operation( set_flow_fd, flow_rate(123.0, scfh ) ) ) if using PIPE-FLO Professional version 15 or later
  1. In Excel Cell F2, enter the formula:
    1. ="pipeflo().doc().get_flow_demand('"&A2&"').set_flow(flow_rate("&D2&","&C2&"))" if using PIPE-FLO Professional version 14 or earlier
    2. ="pipeflo().doc().get_flow_demand( '"&A2&"' ).set_flow_demand_operation( flow_demand_operation( set_flow_fd, flow_rate("&D2&","&C2&") ) )" if using PIPE-FLO Professional version 15 or laterThis will merge your cell values into usable Data Import script.
  2. Select the cell and drag the lower left corner to populate the entire column.
  3. The completed spreadsheet is saved as comp_air_complete.xlsx.

Now that the text for the script is generated, it needs to be copied into a text editor and saved with a .py extension for use with Data Import.

  1. Select Cells F2 to F15.
  2. Copy them. CTRL+C.
  3. Open your favorite text editor, Notepad is fine.
  4. Paste. CTRL+V
  5. Save the file as comp_air.py

Now Import the file in PIPE-FLO Professional 14.

  1. In PIPE-FLO, click the Data Import button   in the toolbar and open comp_air.py.
    1. You can also drag the file onto the PIPE-FLO window and it will execute.
  2. The values in PIPE-FLO should now match those from the spreadsheet.


This illustrates a simple method to leverage work that has already invested in current Xlink or other spreadsheets, providing a path to move that data directly into PIPE-FLO. The real power of Data Import comes with the flexibility available via Python scripting. See this months Feature of the Month article for an advanced example of a Python script that will actually read a .csv file and import the values.