QTO Manager- Workaround to Update CSV Files

The QTO Manager and Takeoff tool in Civil 3D are two tools within Civil 3D proved to be more exciting in theory than it has been in fact.  The biggest complaint about the tools is the fact that you cannot edit the pay items and have them automatically update the same items in the Excel CSV file.  This has forced some users to just bypass the tools and continue using older methods for calculating pay items.
My friend, Marco Garza, has devised a way to work around this issue.  He wrote this and, with his permission, I’m passing this along to you. . .
After doing some research into this subject I’ve found that Civil 3D has some flaws in the QTO Manager due to the fact that the “Pay Item ID” values don’t update automatically once modified in the pay item list “CSV” file. In addition, the drawing objects that had the outdated Pay Item ID’s attached to them will no longer be calculated with the quantity “Takeoff” command. This requires having to reattach the new Pay Item ID to all the drawing objects that share the new pay item number.
However, one interesting observation is that the “Description” updates from the CSV into the drawing QTO manager and drawing objects with no problem. This led me to wonder…Why can’t we use this component from the pay item list CSV file that does work and update automatically to partially solve our pay item ID update issue? After some brainstorming and testing I found a solution that is working for me. The following is a brief explanation of my findings.

STEP‐1

Create your pay item csv file with one slight format modification. Use a generic numbering system for the “Pay Item ID” values. Example,… Use 0001, 0002, 0003…etc. Don’t use the actual pay item numbers or codes. In the future, when you have to insert a new pay item in between existing rows just give the “Pay Item ID” an additional alpha character,…0003,0003A,0004…etc.
Don’t ever change the values of the “Pay Item ID” once added. You will be able to change the actual pay item numbers but that occurs in the “Description” portion of the csv file. Don’t forget, in notepad open the .csv file and make sure the top row is empty, if not the first row of data will not appear in your QTO manager.

STEP‐2

Enter the rest of the data for the “Pay Item ID” values in the CSV file with one addition. Add the actual pay item number as a prefix to the “Description” value with a “~” separator. Example… “152‐12~Remove Existing Concrete”. At any time you can edit the pay item numbering or coding in the CSV file and the drawing/QTO report output will automatically update as long as the edits are only done to the “Description” portion of the CSV file.

STEP‐3

Once you load the CSV file in your QTO Manager then you can compute your quantity takeoffs as needed.  Set the “Quantity Takeoff Report” output to show as  “Summary (CSV).xls”

STEP‐4

Open blank Notepad file, copy and paste the contents of the QTO Reprort output into the blank Notepad file.  Add a comma in front of the “PAY_ITEM_ID” at very top left of the file.  Save as a .txt file’

STEP‐5

Open your Excel file that will contain your quantity report or cost estimate. Go to the “Data” tab and select the “Get Data From Text” button.

When you get to step 2 of the import wizard,
select the “Comma” and “Other” with the character “ ~ “ as the two Delimiters. You will notice that the Data Preview will show the “PAY_ITEM_ID” over the correct column.

Once imported you can delete the first column that contains the generic numbering values that were only used for sorting purposes.

Update:  I’d like to thank Lisa Pohlmeyer, an “Expert Elite” contributor on the Autodesk discussion forum, for passing this along in the forum.  I’d also like to thank Joe Bouza, also an “Expert Elite” contributor, for discovering that instead of using arbitrary numbers for the item number, the entire process could be run using the unit cost.  Here is the link to that discussion: QTO Discussion on the Autodesk Forum.