1-Visitor

Solved

Import data from TXT - case study

Forum|Forum|6 years ago
May 25, 2020
4 replies
8391 views

Good afternoon,

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.

2_readTXT.zip

1_infile.txt

Best answer by LucMeekes

You may have to press the F9 function key.

Automatic calculation is switched off.

Edit:

I've modified the file per your correction, and switched automatic calculation on.

This should do it.

Success!
Luc

A

AlanStevens

19-Tanzanite

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!).

Alan

P

PawelDabrowski

Author

1-Visitor

Thank you Alan,

This is just a sample, my data is ~200,000 rows long 🙂

L

LucMeekes

23-Emerald IV

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 "+".

See attached.

Success!
Luc

1_LM_20200525_ReadText.zip

W

Werner_E

25-Diamond I

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

W

Werner_E

25-Diamond I

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.