Trimble Business Center

Is there a way to export points (N,E, ele, D) as a .csv from TBC and sort the points by date captured (not by name)?

I'm looking for a work around from using the SCS Report Utility.xlsm via SCS Data Manager. Using the Report Utility on work orders that have a large amount of data will break the Utility. I've watched it run for several minutes (25,000+ lines of data) and then only to end with a Microsoft Visual Basic - Run-time error '1004': - PasteSpecial method of Range class failed. See below:

Although the WO only has a few hundred captured topo points that I want to extract - the SCS Report Utility goes and reads every single line of data that's in the TaskLog.txt! Seeing the residuals and timestamps are great - but having to recreate WO's that are limited by the amount of data the ReportUtility can handle is a real PITA. This has been an issue for us for a long time BTW. So, my only work around using the ReportUtility/SCS Data Manager Vs. TBC and drop in the .spj is to keep the WO's small and keep recreating new ones often. 

Attached is one of many TaskLog.txt that has this data size issue. It would be great to create this same Report via TBC from the .spj file. We're using the same WO for layout of blast holes & then come back post blast to capture as-built hole locations. (This is what I'd like to do - keep all the blast data to a single WO) I've since created separate WO's for layout & topo, and keep for just a single blast & then have to create another WO for each and every blast (We're blasting rock a few times a week for a long time).  So now I'm going to have over a 100 WO's to sort through over the next 3 years of the project. (This is what I did on my last project - but was too much busy work IMHO)#scsreportutility#.#spj

Suggestions? Thanks Allan (Ahead of time - )

Hi Ryan,

In Home / Export / Export Format Editor

Select a CSV Type you want

"Copy"

Highlight the copied Definition

"Next"

Change Description to suit

Check "Include Header"

"Next"

Expand Total Station Observation

Select "Timestamp"

Right Click and "Add to record"

Expand  GNSS Position Solution

Select "Start Time"

Right Click  and "Add to record"

Finish

Then Export / Custom 

Select your new style and points etc

John,

Thanks for the detailed reply. Although I was able to create a new export style as you outlined, the timestamps are not exported into the .csv.

I'm dropping in a .spj in TBC (not a .job - if that matters). The header of my new custom .csv point list does have the new columns, but there is not any data that was carried over. 

When exporting, I'm using "Options" - "Select All". I'm not sure if the .spj contain Timestamp data (as the Tasklog.txt from SCS900 does).

Ryan

Ryan,

Sorry I hadn't looked at your data files before setting up Export format - I agree there are NO timestamps associated with points from the 1-BLAST.spj  (your text file does have all the details but would be too hard to extract - BTW pretty impressive set out intervals  - 20 seconds per station!).

I don't use the Construction Data formats so I can't help further - but maybe there'll be a setting in your Import Results to include Time Stamps (or Raw observations) - or in Work Records outputs

If I import my Total Station Observations through Access under "Properties Tab" if you select a point there'll either be a GPS Timestamp (or if Total Station select associated data and look at Raw Observation data)

Just thinking aloud - could you just change your point naming convention to be:

Year/Month/Day ID - this is what I do - so points are 190717001 etc

(be careful with characters as Excel sometimes won't like them)

John,

In a perfect world where I would trust the grade setters to properly name points every time as suggested, maybe. I've tried that before with little success. 

The problem starts when SCS900 names points in different ways, depending on what is being captured, or layout. They have a calced point list they layout (nail & whisker) locations. SCS900 names these points automatically - Calced point is AG15 - SCS900 names that point AG15_stk when it's staked out to and recorded. The Report Utility shows if the point is within tolerance. So, the date ID in the name would be lost. 

Doesn't seem there's a work around for SCS Datamanager using the report utility - nor a way for TBC to drop in the TaskLog.txt. I just don't think the .spj contains timestamp data. 

Ryan

Hey Ryan,

I think you may need to look outside TBC for your answer here. 

In your case I exported a CSV file as John Mill suggested. I then took your txt file and brought it into Excel. All the data is in the third column upon importing.  I made a little lookup table where I grabbed the date and time from 2 and three cells above the Point name. 

I filtered out all of Column B showing only the rows with Point Name. So My lookup table looked like this. 

Then Ran a v lookup formula to pull the data into the CSV exported from TBC. 

Sort your data by date and you are off to the races. 

FYI none of yout Topo### points were found in in your Txt file. 

Hope this helps. 

SL

Appreciate the response Scott. I was hoping not to spend time editing excel spreadsheets, but hoped (hoping) this would be part of TBC. Hopefully in a future build.

- Ryan

In SCS900 and Siteworks if you "Export Measured Data" as a CSV with "Include QA Data" checked, you'll get ALL the information that is recorded about all the points in the WO, and then be able to sort the points in Excel based on whatever fields you wish.  In that CSV it indicates the time the points were recorded (and a ton of additional information that is stored in the SPJ).  And in your case it also has a column if the STK points are in/out of tolerance, so this might give you what you need?  Albeit without the color coded formatting present in the RU.

But can you explain a bit more about what you're trying to accomplish with the export and using the Report Utility? You are trying to see if the as-built shot locations are in tolerance or how far they were from the original layout spots?  If the RU worked for large datasets does that give you the information you are after? 

I also forwarded this on to the owner of the Report Utility to see if he can fix whatever is causing the crash as well. . . That seems like it might be the "best" solution, but I'm still trying to understand exactly what problem you are trying to solve, so that perhaps a different or better solution can be provided.  

As Albert Einstein said ~ "If I had an hour to solve a problem, I'd spend 55 minutes thinking about the problem, and 5 on the solution"  

Steve,

Thanks for your reply & suggestions. The problem with the Report Utility is that it just can't handle large data sets. I believe it's just a limitation on how many lines of data that excel can handle. I've had issues with the report utility giving my that error for years. The only way I've come to "fix" it - is just create many many WO's. This method does work, but it's a real PITA having 100's of WO's over the length of long (several year) project.

_Ryan

Here is a slimmed down version of the Report utility that cuts down on the icons used.  It seems this can open you data.  Please report back if it works for you! 

Thanks!! This beta version (With Out the Icons) was able to quickly load with crashing (giving me the run-time error). Looks like I'll be using this version for now.

-Ryan

Just a FYI - I get an error (twice) when using the beta version. Not a big deal, as it will still work after hitting "OK" after each pop up.