# 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.

- Open the
**Compressed air system.pipe**model in PIPE-FLO Professional 14. - 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

- In PIPE-FLO, click the
**Send to Datalink**button in the toolbar to populate the ODBC database with current information. - In Excel Cell
**B2**,- Enter the formula
**=dlq("flow_demand", "flow", A2)**if using PIPE-FLO Professional version 14 or earlier - Enter the formula
**=dlq("flow_demand", "calculated_flow", A2)**if using PIPE-FLO Professional version 15 or later

- Enter the formula
- In Excel Cell
**C2**,- Enter the formula
**=dlq("flow_demand", "flow_unit", A2)**if using PIPE-FLO Professional version 14 or earlier - Enter the formula
**=dlq("flow_demand", "calculated_flow_unit", A2)**if using PIPE-FLO Professional version 15 or later

- Enter the formula
- 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

- In Excel Cell
**F2**, enter the formula:if using PIPE-FLO Professional version 14 or earlier**="pipeflo().doc().get_flow_demand('"&A2&"').set_flow(flow_rate("&D2&","&C2&"))"**if using PIPE-FLO Professional version 15 or laterThis will merge your cell values into usable Data Import script.**="pipeflo().doc().get_flow_demand( '"&A2&"' ).set_flow_demand_operation( flow_demand_operation( set_flow_fd, flow_rate("&D2&","&C2&") ) )"**

- Select the cell and drag the lower left corner to populate the entire column.
- 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**.

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

Now Import the file in PIPE-FLO Professional 14.

- In PIPE-FLO, click the
**Data Import**button in the toolbar and open**comp_air.py****.**- You can also drag the file onto the PIPE-FLO window and it will execute.

- 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.

## Related articles