DOS – Still Relevant

Not only is DOS still relevant, its often one of the quickest ways to get some things done.

On XtraCAD.com, someone recently asked how to get the system Date and Time in an Autodesk Fabrication COD script. I provided a solution that uses DOS commands inside a COD script. That solution is explained in more detail here.

DOS’s “Date” Command

Using DOS, we can use the “DATE” command with the “/T” Switch to output the current date to a DOS prompt.

The information given by this simple command is all we need. A script can easily read the data if the output is redirected to a data file.

We’ll get a little more ambitious and get particular on the formatting. We’ll remove the “Tue” and format the date in the format “yyyy.mm.dd”.

To do this, we can use the “FOR” command in DOS.

for /F "tokens=2-4 delims=/- " %A in ('date /T') do echo %C.%A.%B

The Red circled area is the command. It takes the data from the Date command and breaks it apart by the delimiters (DELIMS) which is spaces ( ), forward slashes (/) and dashes (-). You’ll note that the Date doesn’t actually contain any dashes so it’s just ignored.

The TOKENS specifies we want the 2nd thru 4th items of data. They will be assigned sequentially to variables starting with “%A”. The Green circled area is the resulting output ECHO’d to the DOS Window in the format we want.

  • %A = 2nd piece of data (month)
  • %B = 3rd piece of data (day)
  • %C = 4th piece of data (year)

Sending Data to a File

Now that have our DOS command, the next step is to send the output to a file on disk. Doing this will allow the COD script to read it back later.

For this purpose, we use a re-director to pump the data to a file. We simply append a suffix like this…

for /F "tokens=2-4 delims=/- " %A in ('date /T') do echo %C.%A.%B>"C:\Temp\COD Data.Txt"

A few notes about redirecting data to a file….

  1. We use double quotes around the file path and name in case it contains spaces. This way, DOS doesn’t interpret the space as a separator between commands.
  2. The Greater-Than (>) symbol is used to redirect output to a file. If the file already exists, it will be overwritten.
  3. Double Greater-Than (>>) symbols can be used to “append” to the end of an existing file. This is handy if you want to add more data to the same file. If the file doesn’t already exist, it will be created.

We Have DOS, Now for the COD Script

Now that we have our DOS syntax down, we can start writing out COD Script. To start, I typically generate a few variables that help me format things.

REM ------------------------------
REM DQ = Double Quote Character
REM CR = Carriage Return Character
REM ST = Single Tab Character
REM WF = Working File
REM ------------------------------

DIM DQ = ascii(34)
DIM CR = ascii(10)
DIM ST = ascii(9)
DIM WF = "C:\Temp\COD Data.Txt"

Because some of our syntax contains double quotes, and because strings (text) in a COD script also contain double quotes, having multiple double quotes in a row can be confusing. Additionally, sometimes the script has trouble understanding where one string ends vs what’s a string containing a double quote.

To handle this, I set a variable (DQ) that will represent any double quote within a COD Script string. I also use a Carriage Return (CR) variable and a Single Tab (ST) variable for formatting purposes that you’ll see later.

Lastly, I also set a variable for the data file. It’s at the beginning, it’s easy to find and change without having to get in the middle of a lot of confusing formatted string data later.

Executing DOS From the Script

To execute an external command from a script, we can use the EXEC function.

EXEC(<“command”>, <execution mode>, <“command data/arguments”>)

Here’s the 3 pieces of data we’ll need…

  1. <command> = “CMD.EXE”
    This is the DOS Command Interpreter
  2. <execution mode> = exec_wait + exec_show_min
    These are a couple variables that tell the external program to “Wait” until finished before proceeding with the rest of the script and to minimize the Window.
  3. <command/arguments> = The Prior DOS Syntax (with modifications) goes here.

The CMD.EXE program takes an argument of “/C” followed by the command it’s going to execute which is our DOS Syntax. Pay close attention, because here’s where we’re going to have to break up the DOS commands and embed our variables for the embedded double quotes.

The below is a single “String” with double quotes on each end. It also has a lot of double quotes inside the text which will confuse you and your script. This below syntax is WRONG and needs to be corrected….

"/C for /F "tokens=2-4 delims=/- " %A in ('date /T') do echo %C.%A.%B>"C:\Temp\COD Data.Txt""

To do this, it’s easier to illustrate in color. We’re going to take one very long complicated string, and break it into several smaller strings when there’s double quotes within the string.

That is, where ever there’s a double quote within the string, we’re going to make a smaller string before and after, and piece them back together and use our DQ variable to embed the double quote between them.

This works for the first two double quotes. But at the end of the string, we’re going to do something a little different. Here, we want to remove file name and use the WF variable we set earlier to store the file name. And because the filename may have a path, we surround it with DQ variables to embed it in double quotes.

The next piece of code should look like this when we’re done. This will run our DOS command and dump the date to a file,

REM ---------------------
REM Get Date (yyyy.mm.dd)
REM ---------------------

Exec("cmd.exe", exec_wait + exec_show_min, "/C for /F " + DQ + "tokens=2-4 delims=/- " + DQ + " %A in ('date /T') do echo %C.%A.%B>" + DQ + WF + DQ

Reading Our Data File

Now that we’ve dumped the data file to disk, we can read it back in from the COD script using the following code…

DIM myDate
Object myFile as File (WF, forinput+istext)
myDate = myFile.Readline()
myFile.Close()

This code opens the file and reads its data and saves it to a variable. You’ll also note that this code doesn’t add extra double quotes around the WF “Working File” variable. That’s because they aren’t needed here, and will in fact cause problems. The COD Script language is actually better at handling files with and without spaces because it uses a comma (,) as it’s data separator between the file name and file read modes.

Displaying Our Data

Last, we can display the data in a simple debug dialog. Here, you’ll see I make use of the ST variable to place a single tab between the data purely for formatting purposes.

Debug "Date:" + ST + myDate

The Bigger Picture

The following code takes all the above principals and goes a little further. With everything you’ve learned, you should be able to figure out what it’s doing and how. It’s doing all the same things plus a little extra…

  1. Also writing TIME and the USERNAME of the currently logged in Windows User to the data file.
  2. It’s “Appending” the TIME and USERNAME using “>>” instead of “>” that DATE uses.
  3. It’s reading 3 lines of our data file
  4. The data file is deleted after it’s read leaving our system clean of temporary files.
  5. The data is displayed by also using the CR (Carriage Return) variable to start new lines for the additional pieces of data.
REM ------------------------------
REM DQ = Double Quote Character
REM CR = Carriage Return Character
REM ST = Single Tab Character
REM WF = Working File
REM ------------------------------

DIM DQ = ascii(34)
DIM CR = ascii(10)
DIM ST = ascii(9)
DIM WF = "C:\Temp\COD Data.Txt"

REM ---------------------
REM Get Date (yyyy.mm.dd)
REM Get Time (hh:mm)
REM Get User (login name)
REM ---------------------

Exec("cmd.exe", exec_wait + exec_show_min, "/C for /F " + DQ + "tokens=2-4 delims=/- " + DQ + " %A in ('date /T') do echo %C.%A.%B>" + DQ + WF + DQ)
Exec("cmd.exe", exec_wait + exec_show_min, "/C for /F " + DQ + "tokens=1-2 delims=: " + DQ + " %A in ('time /T') do echo %A:%B>>" + DQ + WF + DQ)
Exec("cmd.exe", exec_wait + exec_show_min, "/C echo %username%>>" + DQ + WF + DQ)

REM ---------
REM Read Data
REM ---------

DIM myDate
DIM myTime
DIM myUser

Object myFile as File (WF, forinput+istext)

myDate = myFile.Readline()
myTime = myFile.Readline()
myUser = myFile.Readline()
myFile.Close()

REM ----------------
REM Delete Data File
REM ----------------
Exec("cmd.exe", exec_default + exec_show_min, "/C DEL " + DQ + WF + DQ)

REM ------------
REM Display Data
REM ------------

Debug "Date:" + ST + myDate + CR + "Time:" + ST + mytime + CR + "User:" + ST + myUser

Whole program can be downloaded here…

COD Script Updates – Part 2

I didn’t plan up updating scripts again so soon but I found a couple more undocumented properties. I thought I’d post them sooner rather than later.

The two ITM properties I found are “BOX” and “E-Tag“.

BOX is only visible from CAMduct. It’s intended purpose appears to be for specifying a “Box” for the ITM in question for shipping purposes but you could use it or anything. Despite it being visible only in CAMduct, using COD Scripts, you can read and write it from ESTmep or CADmep too.

E-TAG is visible from any of the Fabrication products. It’s used for Equipment Tags. You can see both properties from here if in CAMduct or only E-Tag is ESTmep or CADmep.

Scripts Updated

  • All Debug Scripts – Nothing major, just formatting in the comments section.
  • WriteAll_Props (Job).cod – Updated to support BOX & E-TAG properties.
  • WriteAll_Props (Library).cod – Updated to support BOX & E-TAG properties.

Scripts Added

  • Debug ITEM Box.cod
  • Debug ITEM E-Tag.cod
  • WriteAllBox (Job).cod
  • WriteAllETag (Job).cod
  • WriteAllBox (Library).cod
  • WriteAllETag (Library).cod

You can download the *current* versions here.

Scripting property reference has also been updated here.

COD Script Updates

I’ve made a couple updates to the Autodesk Fabrication script libraries. If you use them, you can download updated versions from here.

Scripts Updated

  • Debug
    • Debug ITEM Library.cod
    • Debug ITEM Sealant.cod
  • Job Items
    • WriteAllLibrary (Job).cod
    • WriteAllSealant (Job).cod
    • WriteAll_Props (Job).cod
  • Library Items
    • WriteAllLibraries (Library).cod
    • WriteAllSealant (Library).cod
    • WriteAll_Props (Library).cod

Issues Corrected

Issue 1: Scripts accessing the “Library” property were failing on CID/Pattern 2199. Scripts have been updated to watch for this and report it as an ‘Unknown‘ Library.

Issue 2: Some CID/Patterns can be configured to be pipework or duct work depending on the “Pipework” option’s “Yes/No” status. Scripts were updated to properly report or ignore this property depending on the Sealant value being present.

If the option is set to “Yes“, the pattern is a pipework item. If set to “No”, the pattern is a sheet metal item. Sheet metal items contain the “Sealant” property where as Pipework items do not.

This condition is present in the following CID/Patterns…

149838 902110112381239
12401241124212471248

Special thanks to Kyle Speropoulos of MMC Contractors in Kansas City for alerting me to this issue.

COD Scripting References Added

Under the resources menu for Autodesk Fabrication, the COD Scripting section has been updated. This now points to another sub-menu of additional COD Scripting reference information.

The link to the COD scripting libraries has also moved one level deeper so if you have a bookmark and it comes up blank, you many need to re-link it.

I still have more information to add that I’ve not previously documented. These are more advanced functions or other undocumented functions. I’ll post notices as I update them.

You can find the additional COD Scripting information compiled here. Direct links to all the scripting reference information is below…


Microsoft PowerBI w/Autodesk Fabrication

For years, I’ve shared my Fabrication COD scripts with the industry. These can be used to examine database content using Excel.

Tyler Phillips of Bruner Corporation recently posted a nice article on LinkedIn about using Microsoft PowerBI. His PowerBI dashboard provides a great way to help visualize the data behind your Autodesk Fabrication content.

Simply put, the scripts I share dump property data to multiple CSV files. Tyler used that data for some of the PowerBI data sources. This allowed him to built a dashboard which helps him visualize and navigate the data in a more meaningful way. And better yet, he publish a fantastic article on LinkedIn that explains how to do it.

Microsoft PowerBI Dashboard of Autodesk Fabrication Content

This is a great example of leveraging data from multiple sources. PowerBI help you mash it together to give you meaningful information that’s simple to understand and navigate.

If you’ve ever struggled with ESTmep reporting, just think of the possibilities. By taking the above concept and using it across CSV exports from ESTmep you could easily overcome gaps in estimating reporting.

Great job Tyler! Really appreciate you sharing.


Additional Resources

Revit Fabrication Parts – Pattern Support Analysis

It no secret that not every Autodesk Fabrication pattern is supported in Revit. I’ve recently finished a more in-depth analysis of which patterns are and are not supported in Revit. Each of the 703 different pattern types were tested in each version and each update of Revit.

On the Fabrication COD Scripts page, you can find a Revit Support Report script. That script will analyse your ITM library and generate a CSV report of the status of every ITM in your Fabrication Configuration.

You can also find the complete results of my testing on the Revit Support page. Here’s a summary of the the testing…


Tested Version

Each of the below versions of Revit were tested with all the the 704 different patterns:

20162016R22061R2SP7
2017.02017.12017.2
2018.02018.12018.22018.3
2019.02019.12019.2

Testing Criteria

“Supported” for purposes of this testing is based on whether Revit allows a particular pattern to be used in the Fabrication Part Browser.


Testing Results

Testing results will have one of the following status descriptions.

  • No – No Revit support
  • Varies ({verison}) – Revit support varies between Revit version
  • Yes (Risk) – Revit does support but use NOT recommended due to issues
  • New ({version}) – New Pattern supported in later Revit versions
  • Yes – Pattern is supported in all Revit versions with no significant issues

Support Status = No

No – Parts not supported by the Parts Browser are obviously listed as having “No” support. There are a total of 84 different patterns listed in this category. They are as follows…

125126127
128129130141158202203
204205206207208209210211212213
215216218220221228230301302308
503505521530764765803804805806
833873910913928960961964966969
974980983985994996999104911021106
1114114211501152115711611162116511681169
1181119411961198218521892190219121922199
2200287331083873

Support Status = Varies

Varies (version) – In some cases, Fabrication Parts were initially allowed or supported in earlier versions and later were restricted. These parts have a status of “Varies” followed by the version where the change occurred. It’s recommended you avoid use of these patterns even in versions where they work as they will not be supported in later versions. There are 17 patterns that once worked in Revit but are no longer supported. They are as follows…

0182222381845853855864876898
903912915971110511701175

Support Status = Yes (Risk)

Yes (Risk) – Patterns that list “Yes (Risk)” are technically supported in Revit but I highly recommend avoiding their use. Patterns with this status have issues like inserting at the Revit’s Project Base Point and not the cursor location. They also have a tendency to crash Revit if you attempt to grip or edit them. As a result, I highly recommend avoiding their use. There are 29 patterns in this category. They are listed here…

119189317346347348349350351352
353368369390392397398415810828
922930962963968973115911601176

Support Status = New

New (version) – Some patterns were not around in earlier versions of Revit but later added and supported in Revit. These patterns are listed as “New” followed by the version of Revit they became available in. These patterns are generally safe to use for Revit. There are 19 patterns in this category which are listed here…

217120612071238123912401241124212431244
124512461247124812491250219721982217

Support Status = Yes

Yes – Parts allowed by the browser that work across all versions of Revit are listed with e “Yes” status. There are 555 patterns in this category. They are listed here…

12345678910
11121314151617181920
21222324252627282930
31323334353637383940
41424344454647484950
51525354555657585960
61626364656667686970
71727374757778798081
82838586878889909192
939596979899100101102103
104105106107108109110111112113
114115116117118120121122123124
132133134135136137139140142147
148149150151152153154155159160
162163165166169170171172173183
184185186231250251252253254255
309311313314315316318319320321
322323324325326327328329330336
338340341342343344345354355356
357358359360365366367376377378
379380382383384385386387388391
393394395399401410411412413417
420421430431440441450451460461
470471472480501502504506507509
514515518519522523524525526533
535555556557580751760761800801
802807808809811812813814815817
818819820821822823824825826827
830831832834835836837838839840
841842843844846847848849850851
852854856857858859860861862863
865866867868869870871872874875
877878879880881882883884885886
887888889890891892893894895896
899900901902904905906907908909
911914916917918919920921923924
925926927929931932933934935936
938939940941942943944945946947
948950951952953954955956957958
959965967970972976977978979981
9849869879889899951000110111041107
1108110911101111111211131115111611171118
1119112011211122112411251126112711281129
1130113111331134113511361137113811391140
1141114311441145114611471148114911511153
1154115511561158116311641166116711711172
1173117411771178117911801183118411851186
1187118811891190119111921193119711991200
1201120212031204120515221972204020412042
2044204720512060207120722082209721082148
2149215521602182218321842186218721882193
2194219521962326238623882522252325242751
2814282128572868286928752881288228832884
2885288628992900290129022903290429052906
2907291629382965296629672979304130513060
30713386352235234522

Autodesk Fabrication COD Script Libraries

Over the years, I’ve written a number of scripts helpful for managing an Autodesk Fabrication configuration. I’ve given them away in my Autodesk University session I’ve taught so they’ve circulated around a bit.

I’ve rewritten most, streamlined them, made enhancements, added others, etc, etc. Because I’m always updating and changing them, I thought I’d host them here too. I can then just post when I update them.

There’s are 2 sets of scripts covering the following topics…

  • Debugging Properties Scripts
  • Job Item Scripts
  • Library Item Scripts

One set is for use in 2019.0 and earlier versions (but work in any version), the others are designed for 2019.1 and later when Autodesk added support for the Pattern Number property.

You can get to the scripts from the menu or click here. The scripts are free to use for all except employees of ENGworks or anyone working on the behalf of ENGworks. (contractors, consultants, etc.) who are prohibited from use.

CID’s That Support Field Layout

I’ve been asked from time to time, which Autodesk Fabrication Patterns (CID’s) support exporting points for field layout. If you want to use Trimble, Topcon or similar hardware for field layout like hanger inserts for MEP, these points are important.

So, with this post, we’ll show you that information but also hope to accomplish something else in the process. I’ll walk you through a couple ways to do it, one significantly better than the other. It’s not really important that you know how to do this same process again, once you have the information, you’ll likely save it somewhere. However, knowing how to do this process will hopefully give you ideas about techniques you can use for other types of data mining and extraction. It’s not hard after all, you just need to get creative with few things you already likely know how to do.

If you’d rather just get to the data, scroll to the end of this post and you’ll find the list of pattern numbers.  Otherwise, follow along and at the same time, explore some options for learning how to get this type of data yourself.

Step 1 – Get a copy of Every CID

The MAKEPAT command in Fabrication is how we create new ITM’s based on a particular CID. Most people working with Fabrication for a while know that. What they may not know is that you don’t have to randomly type patter numbers and run the command hundreds of times. CAMduct can do them all at once. If you don’ have CAMduct, install a trail version which you can download from Autodesk’s web site.

After starting CAMduct with a new project, the first step is to turn off prompting for Oversize which would keep prompting you on some large sheet metal patterns that exceed the material size. We turn this off to make the process run without pausing for user input.

Turn OFF Oversize Checking

The next step is to display CAMduct’s Command Window screen by pressing “CTRL-SHIFT-C“. 

Pressing “CTRL-SHIFT-C” on the Keyboard Displays the Command Window.

Next, in the Command Window, type the command “MAKEPAT ALL” and press <Enter>. (Note that the Command Window is also part of ESTmep but the MAKEPAT ALL function is not supported in ESTmep, only CAMduct.)

MAKEPAT ALL” will Create One of Each Pattern

Depending on the speed and resources of your computer or even other running processes, this can appear as if it’s locked up CAMduct at times. However, be patient and let it sit. Unless the software crashes, the process will complete. I’ve seen cases where it can take over 10 minutes or more.

When complete, there is no prompt. You’ll just notice the cursor back in the Command Window’s typing area. The last pattern should be CID 4522. You’ll also see the Job Contents will contain each pattern that was successfully taken off. These patterns exist only in this job, not in your database so you’ll want to save this job in CAMduct’s MAJ file format for future reference. If you like, you can download my copy here.

All Successful and Supported CID’s Are Now in One MAJ.

Step 2 – Examine Patterns

Now that we have a file with all the patterns, we can examine them. There’s a lot, and doing each one individually will take a lot of time. If you’ve ever been to one of my Autodesk University sessions, you’ll know that I frequently supply a lot of Fabrication COD scripts. This is where they can come in handy. You can download them here.

I typically place my COD scripts under the Scripts folder in my Fabrication Configuration like so…

Scripts Extracted to the Configuration’s “Scripts” Folder

If you look at a Hanger CID (1249 for example) you’ll see in it’s Options tab that there’s an “Export” option that tells CADmep if it should export field layout points or not.

The “Export” Option Controls Field Layout Point Exports

We’ll use this to determine which CID’s export points which is where the scripts come in. For this example, we’ll be using the following script…

.\Scripts\Job Items\WriteAllOptions (Job).cod 

This script will write all the Options for all the item in the CAMduct Job. To do this, click the Job Contents button on the ribbon, select all the items in the job, Right-Click and select the Execute Script option then browse to the above referenced COD script.

Use “Execute Script” to Run a COD Script on All Selected Items

After the script completes, it’ll have written to a text file all the options and their values for each of the items in the job along with their CID. You can find the file located in the root of your fabrication configuration’s Items folder. This is the file you’ll be looking for…

.\Items\WriteDWGOptions.Txt

Using Microsoft Excel, open the text file. Make sure you change the file extension to Text Files so Excel displays the file you created.

Change Excel’s File Type to “Text Files (*.prn;*txt;*.csv)

This will bring up Excel’s Text Import Wizard. Select the “Delimited” radio button and click Next.

Use “Delimited” as the Import Option in Excel

In the next screen of the Wizard, you’ll select the “Comma” toggle as one of the delimiters and then click the Next button.

Use the “Comma” Option as the Delimiter

In the next wizard page, you’ll highlight all the columns in the bottom portion and select the “Text” radio button to tell Excel to treat all the columns as text. From there, click the Finish button.

Change All Columns to “Text” Data Format

From here, Excel displays a line item for each option of each item in the job. You can now quickly filter this list by clicking the Data ribbon menu and selecting the Filter button. This adds a drop down arrow control in the first row of each column. “Column E” is the “Name” of the option. When you click the drop down arrow in that column, you see a list of all the values. Uncheck the top “(Select All)” option to deselect all the values and then scroll to the “Export” option and select it. This tells Excel to only display rows containing this text. From here, click the OK button.

Use AutoFilter to Display Only the Rows that Contain the “Export” Option

Once this is done, you’ll get a very short list in Excel. The left most column, “Column A“, is the CID  or Pattern Number for each item in the job (we make one of each pattern) that has the Export option.

Excel Quickly Filters Options to Only Those With an “Export” Option.

So now we have a list of Pattern Numbers that you know will export points for field layout. You also learned how you can use some of the scripts I have to Export and mine data from your fabrication configuration. The question you have to ask now is, are we certain this data is correct? 

Autodesk Fabrication allows you to rename Dimension and Option names. That would suggest that it’s possible to miss an option that was renamed to something other than Export. In this case, because we used CAMduct to create all the patterns from scratch, it’s most likely that it’ll have all the default names for it’s Options and Dimensions.

We’re still not in the clear however. It’s possible some patterns that support field layout points don’t have an Export option or have a similar option but name it something else. For this reason, we can’t be certain we know ALL the CID’s but we can be certain that we know SOME of them.

Step 3 – An Second (Better) Approach

How can we be certain we know ALL the CID’s that support field layout point exports? The simple answer is to export all of the items and see which have points.

To do this, we can use Fabrication CADmep. Start AutoCAD and load Fabrication CADmep. From here, if you saved the job you created in CAMduct as an MAJ file, you can use the “OpenJob” command in CADmep to import all those Items into CADmep.

CADmep will display all the items in the Fab Viewer before importing. Simply click the OK button to complete the import process. 

MAJ Imported into CADmep Displays Items in an Array

Once the MAJ is imported, CADmep will display all the Items in a large array. From here, we can use the “FPOINTE” command (previously the “TRIME” command) to export all the points. When the command asks you to select the points, type “ALL” at the select objects prompt. This will display the Field Point Sort dialog.

All Items Supporting Field Point Exports Will Appear in this Dialog.

Now we could finish exporting the points at this time but we’re not going to. This is because not all the information in this window actually gets exported. Specifically, the Tag column which displays the Item number (not the CID) and we need to know which Item number (which CAMduct sequentially numbered) to find the Item so we can check it’s CID later.

Instead, we’re going to select all the points in the bottom of the dialog, Right-Click and select “Copy” to copy the entries into the Windows Clipboard.

Without going into all the steps here, the next thing we naturally want to try is to Paste the items into Excel. If you go ahead and try it, you’ll likely notice that the coordinate fields are missing (that’s an interesting story with some history in itself) which isn’t a big deal for our purpose. But upon closer examination, you’ll also see that the columns aren’t lining up in Excel. Pasting this data into Excel is not going to work without a lot of fixing of data.

So the next thing I try, I paste the data into a blank Notepad file to see what it looks like there.

FPOINTE” Data Pasted Into Notepad

Looking at the data in Notepad, you can easily see there’s columns. A little lower in the file you see the columns tend to shift, bit there’s definitely columns there. A little further experimentation using the cursor and keyboard arrow keys you can see the cursor jump between columns. This is a clear indication that columns are separated with Tab character. If your data doesn’t look like this, try looking at the Word Wrap setting in the Format pull down menu and turning it off.

At this point, you should be guessing what’s next. Save the TXT file and open it in Excel like you did earlier. The only difference is that instead of selecting a comma as a delimiter, you want to make sure that Tab is selected as the delimiter, The resulting data in Excel should look like the following image…

FPOINTE” Data Opened in Excel

Lets keep in mind that some patterns export more than one point so there’s duplicates that aren’t needed. Additionally, we really don’t care about any of the exported data except for the Item Number which was in the Tag column in the Field Point Sort. This column is in Column F of Excel. For this reason, we’ll delete all the columns except for F. When you’re done, Column F will shift to Column A. From here, we can use Excel’s Remove Duplicates function on the Data ribbon to remove the duplicate Item Numbers.

Remove All Columns Except the One With the Tag Data and Run Remove Duplicates

When you’re done, you’ll have a nice short list of each pattern’s Item Number that was able to be exported. Note, this is the sequential Item Number that CAMduct assigned when creating the patterns. This is NOT the CID or Pattern Number.  Keep this Excel file opened for now, we’ll get back to it later.

At this point, it wouldn’t be too hard to look up each item number in CADmep or CAMduct and check it’s properties to find the Pattern Number of the item. But that can still take some time. We’ll go through another step to automatically look up the Item Number and show you the Pattern Number of the Item.

Step 4 – Using The Item Number to Find The Pattern Number (CID)

We need to somehow tie the Item Number to the CID or Pattern Number. To accomplish this, we’ll again turn to our scripts. All of my COD Scripts typically export the CID number. There’s also typically a script for each property. So we’ll be using the following script which will export the CID and Item Number of all the items in our drawing.

.\Scripts\Job Items\WriteAllNumbers (Job).cod

Type “ExecuteScript” in CADmep and browse to the above script file. At the Select Objects prompt, type “ALL” to select all the items in the drawing to run the script on. When the script is finished, it’ll export the data to the root of the Items folder like before. Open it just the same as you did prior making sure you use a Comma for a delimiter and change the column data types to Text.

.\Items\WriteDWGNumbers.Txt

Once you open the Item Number export file, it’ll look like the following image. This is what we’ll use to cross reference the Item Numbers with their corresponding Pattern Number.

Item Numbers Exported from our Job

Because we need to look up the Item Number in Excel, we’ll want to move the Number column in front of the CID column. This is because the function that we’re going to use in Excel needs the number it’s looking up in the first column. When complete, your data should look like the following image…

Number Column Moved To The First Column

Next, we’ll move this entire worksheet to the first Excel file that contains our points. Right-Click on the worksheet tab and select “Move or Copy…“, use the drop down list in the Move or Copy dialog to select the spreadsheet with your exported point data and move it to the end. Click OK when done. This will move the sheet with exported Item Numbers and CID’s to the end of the other workbook.

Move the Exported Item Numbers Worksheet to Your Point Export File

When you’re done, you’ll have two separate tabs in your first Workbook. One lists only the Item Numbers of the successfully exported points, the other contains the Item Numbers and corresponding CID’s.

Go to the first worksheet tab where it lists a single column with the Items Numbers that were exported. Add a blank row at the top and type the text “Item Number” in Column A and “Pattern Number” in Column B. In cell B2, type the following formula…

 =VLOOKUP(A2,WriteDWGNumbers!$A$1:$C$701,2,FALSE)

After typing the formula, copy it to the remaining cells in the column to complete the cross reference.

This formula tells Excel to look at the Item Number, and find it in the first column of the range in the second worksheet that lists all the Item Numbers and CID’s. When it finds that corresponding Item Number, return the value from the second column which contains the CID. The “$” in the cell address of the range tells Excel to NOT increment the range address as the formula is copied down…you want to look at the same range no matter what. Finally, the False flag tells Excel to do a strict match and not try to interpret close results. Your finished data should look like the following…

This completes our final list of Pattern Numbers (CID’s) that support Field Point Exports. You can look at my Excel file by downloading it from here.

Note that when we looked only at Items that contained the Export option, there were 14 patterns. However when we tested against the actual point export in CADmep, we ended up with 25 patterns. Our final list of Pattern Numbers (CID’s) that support field point exports is here…

149321322838972
10491189123812391240
12411242124312441245
12461247124812491250
19722189219021912192

Hopefully you’ve gained an idea on how to use some of the scripts, Excel and other processes to mine and extract data from your Fabrication configuration. It’s using techniques like these that allow me to assemble a lot of the information I have on this site like which versions of software have which CIDs’s and which ones are supported in Revit.