topic Re: Import data from TXT - case study in Mathcad

Import data from TXT - case study

PawelDabrowski — Mon, 25 May 2020 15:18:46 GMT

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.

Re: Import data from TXT - case study

AlanStevens — Mon, 25 May 2020 16:12:11 GMT

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

Re: Import data from TXT - case study

PawelDabrowski — Mon, 25 May 2020 17:35:09 GMT

Thank you Alan,

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

Re: Import data from TXT - case study

LucMeekes — Mon, 25 May 2020 17:38:29 GMT

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

Re: Import data from TXT - case study

Werner_E — Mon, 25 May 2020 18:00:05 GMT

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.

Re: Import data from TXT - case study

PawelDabrowski — Mon, 25 May 2020 18:11:17 GMT

Thank you @Werner_E

Re: Import data from TXT - case study

Werner_E — Mon, 25 May 2020 18:18:05 GMT

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

Re: Import data from TXT - case study

PawelDabrowski — Mon, 25 May 2020 18:42:13 GMT

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?

Re: Import data from TXT - case study

LucMeekes — Mon, 25 May 2020 19:06:01 GMT

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

Re: Import data from TXT - case study

PawelDabrowski — Mon, 25 May 2020 19:57:27 GMT

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.

Re: Import data from TXT - case study

LucMeekes — Mon, 25 May 2020 22:15:19 GMT

Yes, that doesn't work. Try using the submatrix function on the result.

Success!

Luc

Re: Import data from TXT - case study

Werner_E — Tue, 26 May 2020 02:53:54 GMT

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

Re: Import data from TXT - case study

terryhendicott — Tue, 26 May 2020 14:04:50 GMT

Hi,

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.

Easy

Cheers

Terry

Re: Import data from TXT - case study

terryhendicott — Tue, 26 May 2020 14:40:05 GMT

Hi,

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.

Cheers Terry

Re: Import data from TXT - case study

PawelDabrowski — Tue, 26 May 2020 21:08:36 GMT

Thank you for your help

Re: Import data from TXT - case study

PawelDabrowski — Tue, 26 May 2020 21:09:26 GMT

Thank you @Werner_E , as always your help is priceless