Reference excel data into MathCAD 14?

Forum|Forum|12 years ago
June 14, 2014
23 replies
10641 views

I have a MathCAD 14 worksheet that I'd like to reference data from an excel 2003 file into. I'd like to create multiple 21 and 23 row single column vectors with data from various areas of the single excel worksheet. I know where in the data is in the worksheet so I can speciifiy specifcally which specific cells to reference instead of searching for the data from the MathCAD worksheet. I've used the command "readfile" in the past but that seems to only be useful when referencing a single value from an excel sheet. I tried using the excel component but the examples in the help don't seem to be helpful for setting a vector equal to a specific set of cells. My question is how do I set a vector in MathCAD to equal specific range of cells in my excel file?

Statistics_Analysis

M

MikeArmstrong

1-Visitor

There are multiple ways this could be achieved and in my opinion the best way would depend on the data you have.

Is the data consistent or does the Excel worksheet run a calculation itself?

You could use READEXCEL and bring the whole dataset in and then use submatrix to select the data you want. Again this method could be improved, but I would need to see the format of data.

If the data does not changes you could use an embedded Excel component within Mathcad and select the ranges you want to output.

D

dsochor

Author

1-Visitor

If it is helpful, the excel worksheet is attached. It's organized output from finite element analysis program. Few calculations are done within the excel file, most will be done in MathCAD after the data is pulled in.

My goal is to create multiple vectors of the data between each set of thick black lines in the M column.

1_Attachment-D-Flex-Shear.xls

M

MikeArmstrong

1-Visitor

This should work.

Example in the attached sheet.

2_MA_01-xmcd.zip

1_Attachment-D-Flex-Shear.xlsx

Werner_E

25-Diamond I

As I personally dislike the excel component my suggestion is to use READEXCEL() (and not READFILE()) as Mike alreadey suggested. You can "carve" the data in Mathcad using submatrix, as Mike suggested, but from what you wrote it seems to me you want to avoid is. Fortunately you can avoid this as you can specify the range which is read by using additional arguments of the function READEXCEL().

DataVector:=READEXCEL("D:\Data\Testdata03.xlsx", "rawData1!C12:C55", NaN)

Reads the range C12:C55 (default is: all Data) from the sheet named "rawData1" (default is: first sheet) in the file "Testdata.xlsx" (default path is worksheet directory) and assigns the resulting 44x1 vector to the Mathcadvariable DataVector. Empty cells are replaced by NaN (NotANumber) which is the default anyway.