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

Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Highlighted

Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hello,

I wonder if anybody can suggest a means to programmatically create variable names and assign values to them, using data from an Excel spreadsheet? I have attached a very simple example to illustrate what I want to do. I have electronic component identifiers (Reference Designators) in column A, and the corresponding values for those components in Column B. I would like the values in column A to become variable names in Mathcad, and the values in column B to become the values of these variables. In the example below, I have manually created the variables in Mathcad, but I would like to automate that.  Seems pretty simple, but I haven't figured out how to do it. Any suggestions would be greatly appreciated! Thank you.Excel_to_Mathcad_Illustration.jpg

16 REPLIES 16
Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

As far as I'm aware, Tom, there is no way of programmatically creating names in a straightforward fashion. 

 

I've asked for such a feature several times over the years, but it's not appeared yet.  There have been other user requests for this feature, eg.

 

https://community.ptc.com/t5/PTC-Mathcad/Create-variable-names-automatically/m-p/574404

 

Coincidentally, I recently (Apr 2020) followed up an Alfasoft article on Unicode character creation by asking Alfasoft about this capability.   They said that they had had similar requests from other customers, didn't know whether it was on PTC's development path for Mathcad, but would forward my request to PTC.

 

https://support.alfasoft.com/hc/en-us/articles/360003722078-How-to-add-things-on-top-of-characters-u...

 

With a createName type function, it would be straightforward to vectorize it over a list of names imported from Excel.

 

Stuart

 


From my letter to Alfasoft:

 

Thank you for posting the referenced article - it was interesting and solves a particular problem that several have had over the years. However (there’s always a ‘however’, isn’t there?), the process of adding a constructed character or name by copy & paste is somewhat mandraulic, not to mention “copy&paste” errors being a somewhat notorious and common phenomenon. On the face of it, the process would seem to be better suited to automation.

 

Could a feature request please be raised to add a function to Mathcad Prime 6 that takes a string and creates a new name (variable/function) on the left-hand side of a definition and which is then available for use 'later on' in the worksheet?

 

Eg, make a name ...

 

newName:=concat("var",num2str(1))
newName = "var1"

or

newName := stack(97,0x0333)
newName = "a̅0̳"

 

followed by creating the name, 

 

createName(newName) := <expression>

 

This would have the effect of manually typing/pasting a name and be fully equivalent to var1 := <expression>.

 

Used on the rhs, or as a function parameter, createName would not create a name but would stand in place of the name var1.

 

Other options might be to prefix 'newName' with an operator (eg, @ or 🄋 (or even🗃or 🗄!) - available via the ribbon) to identify a name indirection, eg

 

( 3 + 🄋newName == 3 _ var1)

 

(Suspicions that I might be subtly asking for a macro capability are, I am sure, completely unwarranted. Indeed, the thought has never so much as passed through my head …)

 


 

From a subsequent worksheet I was playing around with to create complex names, and a candidate for a function like createName,

 

2020 05 15 A.png

 

 

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hi,

 

You can sort of do it like this:

Capture.JPG

Cheers

Terry

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hi,

Forgot to show you can get myVariables direct from an Excel component.

Capture.JPG

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

A similar solution using a lookup function variant - I've used this type of thing before when dealing with spreadsheet or other tabular data.

 

2020 05 15 B.png

 

2020 05 15 C.png

 

 

Cheers,

 

Stuart

 

 

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Ah, apologies ,.. that wasn't the version I intended to post.  Forgot to copy across another couple of functions  Mea culpa.

 

This version is slightly more general.

 

2020 05 15 D.png

 

2020 05 15 E.png

 

Stuart

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

I suppose, for completeness sake, that I'd better add that if all you're interested in is the name in the leading column or row, then hlookup and vlookup are sufficient if multicolumn tables are the target (and easily specialized for a 2-column table.

 

2020 05 15 H.png

 

Stuart

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Thank you Terry. Yes, this approach "sort" of does what I want. Unfortunately, when I then go to use these variables in subsequent calculations, it gets a bit "ugly". Consider a very simple example of a voltage divider using my two resistors, what I want to do would look like this:

Voltage_Divider_Example1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Whereas your approach would look like this: Voltage_Divider_Example2.jpg

 

Although not exactly what I was looking for, your suggestion is much appreciated none-the-less.

 

Thanks again,

Tom

 

 

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hi,

 

You could make it a little less ugly by renaming "MyVar" as just "v"

Capture.JPG

Appreciate your appreciation

Terry

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Apart from the fact that it is desirable to have a function that can define a worksheet variable, in order to drive that variable name from e.g. Excel, you must have a reference. At some point you need to decide which one of the many variable names that are defined in the Excel table will be used at which position in a Mathcad expression. So why not let the row index do that....

Then it might look like this:

LucMeekes_2-1590074781699.png

 Also bear in mind that it's almost as difficult to bring units over from Excel to Mathcad

 

Success!

Luc

 

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?


@LucMeekes wrote:

Apart from the fact that it is desirable to have a function that can define a worksheet variable, in order to drive that variable name from e.g. Excel, you must have a reference. At some point you need to decide which one of the many variable names that are defined in the Excel table will be used at which position in a Mathcad expression. So why not let the row index do that....


 

I may have grasped the wrong end of the stick here, Luc, but I was under the impression that the absolute row number of a component value might change, or a different component could be selected at a different time.  In which case, a lookup-type solution is possibly a better way to go.

 

In the example below, I've created a tailored version of one of my lookup functions to make it easier to use.

 

 

2020 05 22 A.png

 


 Also bear in mind that it's almost as difficult to bring units over from Excel to Mathcad

How true.  I'm surprised that no maintenance subscriber has asked for a modified version of str2num that converts quantified numeric strings (eg, "3.14159 mp" or 2.7Fb" (where mp = millipotrzebie and Fb = furschlugginer blintz )), so that we'd have:

str2num("3.14159 mp") = 7.111 μm

str2num("2.71828mp") = 99.014 t

 

I suppose some enterprising individual could probably write one ... (I think I've may have one hidden deep in my M15 archive)

 

In the meantime, here's one way of tackling the problem:

 

2020 05 22 B.png

 

2020 05 22 C.png

 

Cheers,

 

Stuart

 

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

I guess instead of

str2num("2.71828mp") = 99.014 t

you meant

str2num("2.71828 Fb") = 99.014 t

 

Luc

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hah!  That I did, Luc!  😅

 

How apt that I recently changed my avatar to this ...

 

scream - copy paste 01 [2].png

 

By way of contrition, I present a "simplified from a user perspective" use of my lookup-by-name function.  I've put all the lookup functions into an Area and the data generation into another Area (as it's not needed by the general user).   

 

2020 05 22 D.png

 

This, hopefully, shows how easy it could be to use the functions: 

  • copy and paste (Argggghhh!) the first Area into a worksheet,
  • load and convert the Excel data,
  • create a tailored read function (if the user wants to, otherwise use one of the lookup functions directly or create a shorter alias for the needed one)
  • lookup the required variables

 

 

Cheers,

 

Stuart

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

"(Argggghhh!)"

Yes, apart from the crazy situation that mean() is a Premium function, you unfortunately cannot link to reference sheets in Prime Express.

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?


@LucMeekes wrote:

"(Argggghhh!)"

Yes, apart from the crazy situation that mean() is a Premium function,

That's just ... mean.

 


you unfortunately cannot link to reference sheets in Prime Express.

A nuisance, to be sure, but in M15 I used to include the (named) Areas in my template.  That way, I wouldn't worry about changes to my Reference worksheets changing values in an already existing worksheet.   I know I should have a better config control system for keeping versions separate, but I'm not that organized.

 


 

Got bored whilst working on something else, so threw together a val2num function in Mathcad Express.   It converts quantified strings to standard numeric quantities (for quantities that it knows about).  It's quite moody, though, and sulks if the user doesn't leave a space between the numeric value and the unit.

 

 2020 05 22 F.png

2020 05 22 E.png

 

I'm working on making the function Potrzebie-aware.   Apparently, Proxima Centauri is 17739573709665 furshlugginer potrzebie away (plus or minus a page or two).

 

Cheers,

 

Stuart

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Thank you all again for your excellent suggestions. Stuart, you interpreted correctly that in  my case, the order of variables, and the names themselves may change, when a design is updated, or a new one created, so your search functions will be very helpful (after I port them to Mathcad 15). Still one step short of what I was hoping for, but way better than where I started. You also correctly anticipated my next problem.... that of unit handling. Thank you again! I had planned to manage units outside Mathcad, but the functions you provided will certainly make me re-think that.

 

Finally, I want to share that I have had some success changing variable names outside Mathcad in a text editor. I have done that manually so far, but I think that if I can do it manually in a text editor, I should be able to automate that with something like Python.   Unfortunately, my attempts to create a new variable in a text editor have failed.

 

Thanks again,

Tom

Highlighted

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Hi,

But, by chance, you are looking for something like this (it can be developed to make it more automatic):

TVH answer.jpg

Announcements