I have a multi-column txt file. I want to perform an operation on two fields. Each of them has the following layout: number/number (eg. 4/5 or 4/5). I need to extract the first character from both of them and then add (eg 4/5 and 4/6 ==> 4 + 5 = 9) The problem is that MathCAD 15 reads the field as mathematical operation and gives me 1.0 or some fraction. How could I import it as a text, pick two characters, convert to INT and then get the result?
The second issue with the data is that some parameters are given only every 4th row, not each of them (eg. needed PDOP). I need to perform a simple statistic analysis (mean, stdev, min, max, med). As the columns with data (eg. PDOP) have empty rows I cannot do it.
I would be very grateful for your help. Perhaps @WernerE or @Werner_E would have some advice for me. 🙂
Please find attached MathCAD worksheet and a sample of my datafile.
Solved! Go to Solution.
The second part of your question is easy enough:
(though since all the values are the same it's not clear why you want to perform any stats on them!).
The first part is also not too bad. Essentially it means to read the file as a ";" delimited file and replace entries containing "/" with the result of calculation where "/" is replaced with "+".
In the message the OP wrote "I need to extract the first character from both of them and then add" but the example he gave did differently! In the file once again he wrote
So maybe he is looking for something along the lines of
Here is an alternative solution to your second problem. The "on error" statement is necessary only in case that PDOP contains not a single entry with "-" as match would throw an error in that case.
Your first problem is more difficult. I could only think of two ways which both are not single liners
1) preprocess the input file by a self written filter program or maybe even a batch file to replace every "/" character by something else, probably a semicolon would be best if you are sure that every line of data really contains an expression in the form "a/b"
2) Read the csv file into Mathcad byte by byte (using READBIN e.g.) and write a parser in Mathcad to extract the data in the way you need. Seems to be the more complex but maybe also the more flexible way.
Pfff.... you did it just in the second? I am impressed. I did a mistake in the description. @Werner_E was right.
I pasted the modifed file in my directory but unfortunately the worksheet doesn't provide results. What is wrong?
Thank you, it works now. One final minor correction needed is to skip the first two lines in file. I tried to change the range to ORIGIN+2 but I was unsuccessful. With this, all would work perfectly.
I tried to change the range to ORIGIN+2 but I was unsuccessful.
It would work if you additionally replace every R[i by R[i-2.
But I think using "submatrix" either on the result or on the input vectors col20 and col21 is a cleaner solution.
You could simply replace the last R by
Pre-process the file.
remove first line sep=; with a text editor. I use Edit Pad Classic. it is free and handles large files easily.
headers are now on top row.
Pre-process the file again
Open in excel in the resulting wizard screens use delimited and use "semicolon" and "/" as special delimiter.
Now save this excel file as comma separated text file.
This pre-processing takes little time.
Now open in Mathcad using READCSV
On opening the 4/6 etc are seperated into 4 and 6 as seperate columns.
Doing the pre-processing I also note the first line of actual data has one extra blank column?
This extra had to be taken out manually.