Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations SDETERS on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing Excel Data 1

Status
Not open for further replies.

bootlegend

Structural
Mar 1, 2005
289
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.

 
Replies continue below

Recommended for you

Tabular data could be read in using the file input functions, which would put the data into matrices or vectors, depending on how you did it. M15 also had a READEXCEL function.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529
 
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.

 
Is READEXCEL available in Mathcad Prime? It's supposed to be able to read a defined range, so you could read in only the indexes and pull in the rest under program control.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529
 
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?

 
 http://files.engineering.com/getfile.aspx?folder=745e5246-1533-4b99-9737-8934a2991c1e&file=matrix.png
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.

 
 http://files.engineering.com/getfile.aspx?folder=340e2880-b71a-4ed2-90f7-c31eadd55ca6&file=Imperical_Steel.mctx
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor