3
$\begingroup$

I am quite new to Mathematica. I have a text file that upon importing has a repetitive structure with characters included. I need to extract only the following in numbers only (no letters, dash lines, or commas) and store them in consecutive five columns (date, time, measurement value 1, measurement value 2, measurement value 3).

My imported data file looks like this:

Date and Time: 2024-04-01 08:42:47
Measurement: 22.10,924.89,43.43
-------------------------
Date and Time: 2024-04-01 08:42:51
Measurement: 22.09,924.89,43.42
-------------------------
Date and Time: 2024-04-01 08:42:54
Measurement: 22.09,924.90,43.44
-------------------------
Date and Time: 2024-04-01 08:42:57
Measurement: 22.09,924.90,43.48
-------------------------

I've tried splitting but I just keep getting a null output {} Any help is really appreciated.

Improve this question
$\endgroup$

5 Answers 5

Reset to default
2
$\begingroup$

Given text file

enter image description here

Then one way could be

SetDirectory[NotebookDirectory[]]
data = Import["input.txt", "Table"]);
p = Pick[Flatten@data, 
   Map[Not, 
    StringContainsQ[
     Flatten@data, {"---", "Date", "and", "Time", "Measurement"}]]];
p = Partition[p, {3}]; (*3 because fields values: date, time, measurement*)
c = Map[StringSplit[#, ","] &, p[[All, 3]]];
Map[Flatten, Transpose[{p[[All, 1 ;; 2]], c}]];
Grid[%, Frame -> All]

enter image description here

Improve this answer
$\endgroup$
1
  • $\begingroup$ Thank you All. Thank you Nasser, it worked. $\endgroup$
    – LollyPop
    Commented Apr 26 at 9:08
2
$\begingroup$

Assuming the provided text is in a file:

data = ReadList["C:/data.txt", "String"]; 

list = data //
        StringSplit //
       Flatten //
      Map[
       StringDelete[
        LetterCharacter .. | Repeated["-", {2, ∞}]]] //
     Replace[#, "" | ":" :> Nothing, 1] & //
    Sequence @@@ StringSplit[#, ","] & //
   SemanticInterpretation //
  SequenceCases[#,
    k : {a_?DateObjectQ
       , Except[_?DateObjectQ]
       , b__?NumericQ} :> k
    ] &

enter image description here

The number of measurements can vary as long as these are detectable with NumericQ.

Improve this answer
$\endgroup$
2
$\begingroup$ 
text =    
    "Date and Time: 2024-04-01 08:42:47
    Measurement: 22.10,924.89,43.43
    -------------------------
    Date and Time: 2024-04-01 08:42:51
    Measurement: 22.09,924.89,43.42
    -------------------------
    Date and Time: 2024-04-01 08:42:54
    Measurement: 22.09,924.90,43.44
    -------------------------
    Date and Time: 2024-04-01 08:42:57
    Measurement: 22.09,924.90,43.48
    -------------------------"


Query[All, {4, 5, 7, 8, 9}] @
 Partition[StringSplit[text, {","," ","\n"}], 10]

{{"2024-04-01", "08:42:47", "22.10", "924.89", "43.43"},
{"2024-04-01", "08:42:51", "22.09", "924.89", "43.42"},
{"2024-04-01", "08:42:54", "22.09", "924.90", "43.44"},
{"2024-04-01", "08:42:57", "22.09", "924.90", "43.48"}}

Improve this answer
$\endgroup$
1
$\begingroup$ 
text = "Date and Time: 2024-04-01 08:42:47
Measurement: 22.10,924.89,43.43
-------------------------
Date and Time: 2024-04-01 08:42:51
Measurement: 22.09,924.89,43.42
-------------------------
Date and Time: 2024-04-01 08:42:54
Measurement: 22.09,924.90,43.44
-------------------------
Date and Time: 2024-04-01 08:42:57
Measurement: 22.09,924.90,43.48
------------------------- ";

With[{
    trim = RegularExpression@"[\n-]*$",
    delimiter = {"Date and Time: ", "Measurement: ", " ", ","}
},
    Partition[
        StringReplace[#, trim -> ""] & /@ StringSplit[text, delimiter],
    5]
]
Improve this answer
$\endgroup$
0
$\begingroup$

Given a string like this:

text = "Date and Time: 2024-04-01 08:42:47
Measurement: 22.10,924.89,43.43
-------------------------
Date and Time: 2024-04-01 08:42:51
Measurement: 22.09,924.89,43.42
-------------------------
Date and Time: 2024-04-01 08:42:54
Measurement: 22.09,924.90,43.44
-------------------------
Date and Time: 2024-04-01 08:42:57
Measurement: 22.09,924.90,43.48
-------------------------"

Here's how you can split the string into a 2D array:

StringSplit[
  StringSplit[
    text, {RegularExpression["\n-+\n"], RegularExpression["\n-+"]}],
    {"\nMeasurement: ", "Date and Time: ", " ", ","}]

This assumes that the formatting is consistent for all your possible inputs.

The entries are all strings. Do you need to convert to actual numbers, dates, and times?

I don't know what you mean by "store them in consecutive five columns". Do you need to export this in some specific format?

Improve this answer
$\endgroup$
1
  • $\begingroup$ Thank you lericr. I exported the processed document. $\endgroup$
    – LollyPop
    Commented Apr 26 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.