×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Importing Excel Data

Importing Excel Data

Importing Excel Data

(OP)
Is there an easier way to access a large table of values compiled in Excel and use it in Mathcad Prime. For example, I would like to be able to input the name of a wide flange steel shape and then have the different values (section properties) pulled into Mathcad so that I can write calculations in them. The examples I've found use fairly small tables for the Excel Component and Read Excel functions, but to pull in all of the WF shapes you need a very large table or Excel component and it becomes cumbersome. As of now I have a sheet that I manually overwrite the input from the AISC tables. For example if I want to check a W10x45 column I have to manually look up the section properties and enter them in Mathcad. In Excel I've used the VLOOKUP function to do what I'm trying to do now in Mathcad Prime.

RE: Importing Excel Data

(OP)
Thanks IR. I can read in the whole table but it just gets cumbersome with such a large amount of data.

I'm just now realizing that I should have asked a much simpler question. I may not need the whole table in Mathcad. Is there a way to look up one row of an Excel file and import just that row into the Mathcad file? For example, I choose a W10x45 in Mathcad Prime and then I need a function that will go into the Excel table and find the row for W10x45 and import all the section properties. Next time I might need a W12x53 so I need the function tied to the chosen shape. All of the examples/methods I'm finding simply import a fixed range of cells. I've only recently started to use Mathcad so maybe I'm overlooking it. It just seems like a simple task.

RE: Importing Excel Data

(OP)
Readexcel is available and I am able to import the excel table. Currently for each section property I am using a vlookup function. This requires I type in the shape of the beam inside each command every time. There has to be a way to combine a vlookup and extract an entire row based on that lookup item. I assume I could then put the extracted items into a single column matrix and have each passed into the variables. This would allow me to only type the shape into a single lookup function. I'm new to Mathcad so I'm probably overlooking the obvious, but I have searched for a solution and can't find one. Anyone know of a way to do what I am trying to do?

RE: Importing Excel Data

(OP)
Just to follow up, I found a solution for my problem. I can't take credit for it. I found this file somewhere on the Mathcad forums. The author uses an Excel component in Mathcad but does the vlookup inside Excel. So you can input the shape into Mathcad a single time, the vlookup in Excel uses that input to fill the top row of the spreadsheet with the desired values and then Mathcad references that top row for property definitions. Simple and works great. Just sharing in case it helps someone else.

RE: Importing Excel Data

I use Mathcad 2000 still but here is what I do which works pretty well:

I put my steel shape in as an input variable. Say I have a table that is A1:BC2000 I will have the steel shape name dump into A2002 or something, aligning with the shape names column.

I add an index column in excel to use MATCH to find the index of the shape I am looking for, then use a LOOKUP command across the row for all the properties I want

I have an output variable from the excel component that covers the range of section properties for the shape I want - this give me a horizontal vector of all the section properties.

I then have property definitions based upon the index of that horizontal vector.

It takes a little work to set up, but it works great for me. I usually define my section with the global definition and have all the excel and property definitions hidden in an area region above all my work, just so its easy to find and copy/paste as needed.

With this set up, I can also add multiple input variables, and pull out the data for multiple shapes at once.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close