Reference excel data into MathCAD 14?

[dsochor]

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?

[MikeArmstrong]

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.

[dsochor]

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.

[MikeArmstrong]

This should work.

Example in the attached sheet.

[Werner_E]

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.

[MikeArmstrong]

Werner Exinger wrote:

As I personally dislike the excel component .

Why if you don't mind me asking?

[MikeArmstrong]

It seems the bug has not been carried into Prime

[Werner_E]

Mike Armstrong wrote:

It seems the bug has not been carried into Prime

Good to know - probably because tooltips are part of the GUI which was rewritten from scratch.

But this is the wrong thread here

For all readers: We are referring to this thread: http://communities.ptc.com/message/246131#246131

[MikeArmstrong]

oops

[Werner_E]

Mike Armstrong wrote:

Werner Exinger wrote:

As I personally dislike the excel component .

Why if you don't mind me asking?

Its slower, takes up more space in the sheet, it doesn't show at a glance (on printing) which region which output variable is assigned to, requires a third party program (Excel) being installed on every machine (doesn't work with LibreOffice), loads Excel in memory and memory isn't freed

and maybe a couple of other reasons I don't think of at the moment 😉

[MikeArmstrong]

Werner Exinger wrote:

Mike Armstrong wrote:

Werner Exinger wrote:

As I personally dislike the excel component .

Why if you don't mind me asking?

Its slower, takes up more space in the sheet, it doesn't show at a glance (on printing) which region which output variable is assigned to, requires a third party program (Excel) being installed on every machine (doesn't work with LibreOffice), loads Excel in memory and memory isn't freed

and maybe a couple of other reasons I don't think of at the moment 😉

That's a fair few reasons.

I have always used the embedded Excel components as they look muich better than Mathcad tables, having said that they are limited, but have been improved in Prime, and know we can have mixed unit tables in Prime

[Werner_E]

Mike Armstrong wrote:

That's a fair few reasons.

I have always used the embedded Excel components as they look muich better than Mathcad tables, having said that they are limited, but have been improved in Prime, and know we can have mixed unit tables in Prime

One additional benefit of READEXCEL is that is easier to add units right when you create the variable. A further one is that very often there is no need for showing the table which can be avoided using the component.

But of course the component has its merits, too. If you need to display the table, I agree that the components view is the better one. And a great advantage of the component also is that the file is self-contained - no need to operate with two different files. Or am I wrong here? Is the data itself embedded in the Mathcad sheet and can be used even if the original Excel sheet is not present?

[MikeArmstrong]

Werner Exinger wrote:

Mike Armstrong wrote:

That's a fair few reasons.

I have always used the embedded Excel components as they look muich better than Mathcad tables, having said that they are limited, but have been improved in Prime, and know we can have mixed unit tables in Prime

One additional benefit of READEXCEL is that is easier to add units right when you create the variable. A further one is that very often there is no need for showing the table which can be avoided using the component.

Fully agree.

Werner Exinger wrote:

But of course the component has its merits, too. If you need to display the table, I agree that the components view is the better one. And a great advantage of the component also is that the file is self-contained - no need to operate with two different files. Or am I wrong here? Is the data itself embedded in the Mathcad sheet and can be used even if the original Excel sheet is not present?

You are correct. Once the data has been called it is self-contained.

[dsochor]

Unfortunately READEXCEL is not available in my version of MathCAD 14, only READFILE is.

[MikeArmstrong]

It should be avaiable.

[Werner_E]

Daniel Sochor wrote:

Unfortunately READEXCEL is not available in my version of MathCAD 14, only READFILE is.

Sorry, you are right, Those functions were introduced with Mathcad 15 F000.

I think there also was a change in the Excel component when MC15 was introduced. Former versions would only support Excel 2003 xls format and not Excel 2007 xlsx format.

[MikeArmstrong]

Sorry I also forgot that READEXCEL was latter added.

You can use a scripted component.

[dsochor]

Is there a way to read create a single vector in a single line similar to the way you have done this with readexcel but instead with readfile?

[MikeArmstrong]

How about the attached.

I have brought in the data using READFILE and then extracted the VFroce using submatrix.

[Werner_E]

Daniel Sochor wrote:

Is there a way to read create a single vector in a single line similar to the way you have done this with readexcel but instead with readfile?

Not exactly. But you sure can use READFILE to read a complete Excel table, not just a single cell. Behaviour of READFILE is different for xls and xlsx files!

M:=READFILE("Flex_Shear.xls","Excel")

reads the complete Table in the Mathcad matrix M and you have to use submatrix() to access the ranges you want. I think there is no way to do it as comfortable as its possible with READEXCEL(). I am not sure but I think its always the forst sheet in the file which is read and it seems there is no option for chosing a different sheet.

M:=READFILE("Flex_Shear,xls","Excel",2) reads the whole table beginning with row number 2 (e.g. omitting a row header) . If used with a 2007 xlsx file you just get the second row!

M:=READFILE("Flex_Shear,xls","Excel",2,3) reads the whole table omitting the first row and the first two columns. For an xlsx file you jet get element C2 (row 2, column 3).

[Werner_E]

Here is a selfwritten routine ReadExcel() which conveniently does the reading of the whole sheet and subsequent submatrixing and uses a syntax similar to READEXCEL. Its a quick hack without any error checking, but at least it covers the full range of cells Excel offers (also double letter columns) and accepts upper and lower case letters.

Guess this should work in MC14 as well. A possible limit can be if the sheet is that large so Mathcad can't temporarily store it in memory. But I guess Mathcad should be able to cope with most Excel sheets.

[MikeArmstrong]

Nice routine, but not required IMO.

Once he has used READFILE he can use submatrix to extract the data. Also, the worksheet containing a scripted object I posted works like READEXCEL.

[Werner_E]

Mike Armstrong wrote:

Nice routine, but not required IMO.

Once he has used READFILE he can use submatrix to extract the data.

Thats exactly what my routine does. Drawback is that the whole Excel sheet is read for every call, that is for every column to be read-in. Convenience vs. efficiency.

Also, the worksheet containing a scripted object I posted works like READEXCEL.

Yes, its a nice one. Guess it also has to read the whole sheet for every call, while not in a local MC variable.

Anyway, Daniel now has some options to chose from.

[MikeArmstrong]

Werner Exinger wrote:

Mike Armstrong wrote:

Nice routine, but not required IMO.

Once he has used READFILE he can use submatrix to extract the data.

Thats exactly what my routine does. Drawback is that the whole Excel sheet is read for every call, that is for every column to be read-in. Convenience vs. efficiency.

Great selling there . It is a nice routine and defiantly one for my library.

Werner Exinger wrote:

Also, the worksheet containing a scripted object I posted works like READEXCEL.

Yes, its a nice one. Guess it also has to read the whole sheet for every call, while not in a local MC variable.

Anyway, Daniel now has some options to chose from.

He does have options.

I have a few similar sheets which will also access multiple tabs within an Excel worksheet, but they basically became redundant when READEXCEL was introduced in M15.