- Standardizing Data with AutoCAD P&ID
- Using Fasttrack Plant for Standardizing Data
Today we will dig deeper in how to setup Excel to import data to/from Fasttrack. Our example will be built around a line list, since projects commonly have non-Plant 3d users like process engineers filling in that information. As mentioned previously, I like using Excel as a basis because that gives the user more flexibility with the data source, and adds the ability to link items based on formulas. That being the case, some advanced Excel skills are required, but relevant formulas will be documented and Excel forums are a great source of information for how to setup complex formulas.
The setup will consist of the following procedures:
- Setup base table or data source.
- Configure Excel lookups
- Use Fasttrack Plant to import the data
Setting up the base table or Data Source
Again, by setting up our foundation on Excel, your options are open to being able to use any data source that Excel can use, including SQL, Access, and a host of other source.
In this example, I setup a workbook with Service information like pressures and temperatures in my OneDrive: “OneDrive – ECAD, Inc\Applications\Plant 3d\Database Information\Service Data.xlsx”
Download a copy of the workbook used with a sheet that includes the formulas referenced here:Service-Data.xlsx (326 downloads) 2017-Project-Pipe-Line-Group.xlsx (340 downloads)
Obviously, this isn’t a full list of data, but it provides enough that you can see a functional implementation.
The second sheet, Sample Formulas shows how to setup the looks up to fill in the data.
For this exercise, the services workbook is setup to be the single source of data for all projects. This way, only one workbook must be managed, and update it as needed. In your situations, you may need a project-specific workbook, you will need to adjust to match your criteria. From that single workbook, the plan will be to link in a data tab to any project workbooks, and then reference the data through that.
In the project workbook that started out as a line group export from the data manager (you could use a properly configured line list from the report creator as well), a Service Data worksheet was created to import the data from our master services workbook. Here’s a great article for the basic technique:
With that data linked, the project spreadsheet now has the data from the external source on a tab. The master data can be updated in the project workbook at any time by clicking the refresh button in the data tab.
Configure Excel Lookups
Here’s the tedious part (but the formulas are already defined for you, so it shouldn’t be too bad). In each property that you want filled in automatically, you have to setup the formula. Let’s start with looking at hooking up the Design Temperature formula. Here’s another site’s explanation of index match: https://www.deskbright.com/excel/using-index-match/
In this example, we have a check to see if the service column C, has a matching property in the Service Data worksheet. If there is a match, it puts in the value. If there is not a match, the Design Temperature property will stay blank.
Let’s break this down into it’s pieces. The part responsible for the check is =IF(ISERROR(),””,INDEX…blah). Basically, if there is an error, just use blank (“”), if there is not an error, use the successful look up.
Removing the error checking leaves us with, let’s move into the inner part of the formula, the MATCH (=MATCH($C2,ServiceData!$A$2:$A$301,0)
In this case the result is 1. The match formula is saying find the row from the range A2 to A301 in the service data sheet (below) that matches the value given in c2 (P).
The match formula is a way to find the row that we care about!
The second part of the lookup is the index formula. To better understand index, let’s replace the match formula with the value we know is correct (1), and you’ll get this: =INDEX(ServiceData!$A$2:$E$301,1,3)
The index formula takes a range (our master data that is referenced on the Service Data sheet), looks at the row we pick (1 from the match formula), and then allows us to pick a column that has the value we care about (3), which in this case is the design temperature.
Once your formula is setup and returning the correct values, drag through the rest of the worksheet to have all the rows filled with the same formula. The $ in front of the cell references (like $A$2:$E$301) ensures that those values don’t get incremented. $C2 will become $C3 as you drag the formula to apply to other cells.
Using Fasttrack Plant to Import the Data
The final piece is to get the data back into the Plant project. The default AutoCAD Plant 3d import doesn’t work (AFAIK) because it doesn’t read the values from the formulas. Also, our plugin will batch import without having to open every drawing and confirm the updates which can take significant time on a large project.
The import for Fasttrack is setup in 6 steps after clicking the FT Manage > Edit Excel Sync button
- Click the add (+) button to create a new map.
- Fill in the description
- Pick a sample Excel file (our project export from the data manager) and indicate whether to use the file to sync
- Set the header row number (it will use the column names matching the data manager export.
- Pick the import tab.
- Enter the Class name (database table) that the data should be imported to.
When the import is setup click OK.
The you can click the Import Excel button, pick your spreadsheet, and the data will populate for your line groups!
Thanks and feel free to leave comments below.