Showing results for 
Search instead for 
Did you mean: 
Showing results for 
Search instead for 
Did you mean: 

Improve data import to read values with attached unit labels

Improve data import to read values with attached unit labels

1. Describe your environment: What is your industry? What is your role in your organization? Describe your stakeholders.

I am a structural engineer of 20 years working in the bridge and heavy construction field.  I create designs for platforms, shoring towers, lifting devices, formwork, crane foundations, etc.  Pretty much anything the field needs to do complex structural work.  My design calculations need to be simple and easy to follow by other engineers but I do not need to take months to get them to a level of pristine publishable quality.  Although I create drawings that regular field crews must be able to understand for their own safety, calculations only get circulated amongst other engineers and on rare occasions to non-technical managers and staff.  


2. Prime


3. What is your excellent idea?


Thanks for asking 😉


Well currently, READEXCEL or reading a data file will either import numbers as numbers if they can be evaluated to a number.  Otherwise it is imported as a string.  So if I have a units tag on a number "3mm", Prime will read it as a string.  So if I leave out the unit it will read it as a number '3'. 


As an example I have a table in Excel that has header names as well as data.  But there is no way of Mathcad knowing that a particular column, let's say, has units.  I would have to remember what column had units and multiply the column by the particular unit.  In this case I have mixed numbers and strings in a single column and I would have to test each element to see if I can multiply it by a unit.


The solution is simple.  when reading an element, if the element can be split into a valid number (includes E## notation) and a string, the string should be checked to see if it can be evaluated into a valid unit.  If not, the number can be read and the string stripped, or the whole element is taken as a sting (user's option).


Alternately, a simple function (maybe "str2unit") that will try to convert a string to a united value.  The input string can have a leading value or not and there can be a parameter to ignore errors and take any value, keep errors as a string or raise an error (among other useful option combinations).  That way I can simply run "str2unit(ImportedTable, [option_switch])" to clean up the info. 


[option_switches] could be "keep_value_on_conversion_error", "stop_on_conversion_error", "keep_sting_on_conversion_error", etc.


An additional parameter is that the user can optionally specify a row in the table that has unit labels for the entire column of values.  So for the following table, I could run the command like this "str2unit(ImportedTable, keep_string_on_conversion_error, 2)".  It is easier create and maintain a table in Excel when the values are numerical and the not tagged with a unit, so having a row of unit labels is best.



So my table import will look like this in Prime, and I can lookup values easily and the units are already taken care of:


As a structural engineer, some very universal tables that we would use across our discipline would be

  • Structural steel shapes
  • Structural bolt tables (incl, edge distances, common slot dimensions, ASD/LSD capacities, bolt part dimensions, etc.)
  • Timber design values
  • Concrete reinforcing bar data
  • Rigging data tables (shackles, slings, turnbuckles, etc.)
  • Crane charts
  • Material properties tables (plastics, steel grades, friction data, etc.)

And all I have to do is import the specific Excel sheet into a variable.  My 'more robust' lookup function is this:  ("result" refers to the column headers)




4. What is the use case for your organization?


It will allow us to build shared data tables of most commonly used material and product specs.  

5. What business value would your suggestion represent for your organization?

Huge.  We would not need to constantly look up values and copy them into our sheets, increasing sources of error.  

Community Manager
Status changed to: Acknowledged

Hello @SPauliszyn 
Thank you for your ** Excellent** idea 😁. Based on the information you provided, we are acknowledging it as the Community management team. This is not a commitment from the Product team. Other users may comment and vote your idea up.


Oops.. I misspelled.  it should be "unitted" as in 'has a unit'


Splendid idea! I was hoping for something similar all along.



I agree with this idea as well, but there is another variation of this that could be considered. It is possible to have custom formats for numbers in Excel that have the unit added to the value, without having a row that includes the unit. The development team may wish to consider this Excel number format as well during import.


@ChrisKaswer I agree.  And instead of trying to understand a number format code, Mathcad could simply read the "range.text" property of each cell and simply try to parse the text that follows the number.  But both methods should be considered because both can be checked simultaneously for each cell and Mathcad could use the cell's text as priority over the unit label in the specified unit-label row.  I do not generally prefer putting units with the value in Excel tables because of the visual impact of having repeated labels that take up space.  But I don't mind this in certain tables that I do not need to print/present.