2

I've made a good deal of headway by searching this site and learning the ridiculous language that is Windows batch scripting, but frankly I'm stuck. The sample below is from our lab's old radiometer module which regularly logs its data as TXT files. I'm trying to parse them into proper JSON. The idea is to have a .bat file that can periodically run to change all the TXT logs in a certain folder to JSON format.

Some of the reasons I'm having trouble is: the long header, the irrelevant 'PRODUCES' or 'MFRSR' in the header, the irrelevant units in the header, the inconsistent separators between values, the weird '*5' value for null (which can just be zero in this case) and the fact that the data contains 3 more columns then the header (the first 3 values are in fact an old spreadsheet notation for time).

*.txt: 

PRODUCES
    MFRSR Detector Temp ,  degC
    HTR V ,  V
    MFRSR Thermopile Total ,  W/m^2
    MFRSR 414.0nm Total ,  (W/m^2)/nm
    MFRSR 496.6nm Total ,  (W/m^2)/nm
    MFRSR 613.0nm Total ,  (W/m^2)/nm
    MFRSR 670.9nm Total ,  (W/m^2)/nm
    MFRSR 869.3nm Total ,  (W/m^2)/nm
    MFRSR 937.6nm Total ,  (W/m^2)/nm
    MFRSR Thermopile Diffuse ,  W/m^2
    MFRSR 414.0nm Diffuse ,  (W/m^2)/nm
    MFRSR 496.6nm Diffuse ,  (W/m^2)/nm
    MFRSR 613.0nm Diffuse ,  (W/m^2)/nm
    MFRSR 670.9nm Diffuse ,  (W/m^2)/nm
    MFRSR 869.3nm Diffuse ,  (W/m^2)/nm
    MFRSR 937.6nm Diffuse ,  (W/m^2)/nm
    MFRSR Thermopile DirNorm ,  W/m^2
    MFRSR 414.0nm DirNorm ,  (W/m^2)/nm
    MFRSR 496.6nm DirNorm ,  (W/m^2)/nm
    MFRSR 613.0nm DirNorm ,  (W/m^2)/nm
    MFRSR 670.9nm DirNorm ,  (W/m^2)/nm
    MFRSR 869.3nm DirNorm ,  (W/m^2)/nm
    MFRSR 937.6nm DirNorm ,  (W/m^2)/nm

    42099   0.29236 -0.55603     45.20     7.4819          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5          *5
    42099   0.57222 0.01180  45.20     7.8285      0.9378      0.0086      0.0086      0.0032      0.0040      0.0018      0.0005      0.9473      0.0093      0.0086      0.0032      0.0040      0.0018      0.0005     -0.8090      0.0000      0.0000      0.0000      0.0000      0.0000      0.0000
    42099   0.57291 0.01415  45.20     7.8382      1.3587      0.0093      0.0086      0.0032      0.0040      0.0018      0.0005      1.3725      0.0093      0.0086      0.0032      0.0040      0.0023      0.0005     -0.9770      0.0000      0.0000      0.0000      0.0000      0.0000      0.0000
    42099   0.57361 0.01656  45.20     7.8998      2.6372      0.0099      0.0100      0.0032      0.0040      0.0023      0.0005      2.6641      0.0099      0.0100      0.0032      0.0040      0.0023      0.0005     -1.6210      0.0000      0.0000      0.0000      0.0000      0.0000      0.0000

*.json:

PRODUCES1 = {
        Id: 42099029236
        X: -0.55603        
        DetectorTemp: 45.20,
        HTRV: 7.4819,
        ThermopileTotal: 0.0000,
        414Total: 0.0000,
        496Total: 0.0000,
        613Total: 0.0000,
        670Total: 0.0000,
        869Total: 0.0000,
        937Total: 0.0000,
        ThermopileDiffuse: 0.0000,
        414Diffuse: 0.0000,
        496Diffuse: 0.0000,
        613Diffuse: 0.0000,
        670Diffuse: 0.0000,
        869Diffuse: 0.0000,
        937Diffuse: 0.0000,
        ThermopileDirNorm: 0.0000,
        414DirNorm: 0.0000,
        496DirNorm: 0.0000,
        613DirNorm: 0.0000,
        670DirNorm: 0.0000,
        869DirNorm: 0.0000,
        937DirNorm: 0.0000
};
PRODUCES2 = {
        Id: 42099057222 
        X: -0.55603        
        DetectorTemp: 45.20,
        HTRV: 7.8285,
        ThermopileTotal: 0.9378,
        414Total: 0.0086,
        496Total: 0.0086,
        613Total: 0.0032,
        670Total: 0.0040,
        869Total: 0.0018,
        937Total: 0.0005,
        ThermopileDiffuse: 0.9473,
        414Diffuse: 0.0093,
        496Diffuse: 0.0086,
        613Diffuse: 0.0032,
        670Diffuse: 0.0040,
        869Diffuse: 0.0018,
        937Diffuse: 0.0005,
        ThermopileDirNorm: -0.8090,
        414DirNorm: 0.0000,
        496DirNorm: 0.0000,
        613DirNorm: 0.0000,
        670DirNorm: 0.0000,
        869DirNorm: 0.0000,
        937DirNorm: 0.0000
};

Is there anyone here who knows batch and can get me started?

KDC
  • 141
  • 1
  • 5
  • 3
    Having done a bit of text parsing in batch, I strongly suggest choosing a different language for your own sanity. Also, while that example is valid JavaScript, it is not valid JSON. – u1686_grawity Apr 09 '15 at 21:24
  • I'd have to agree. You can trick batch into doing some text processing, but it's never fun. I would suggest AWK as a possible tool for processing this. – GuitarPicker Apr 09 '15 at 21:28
  • Thanks guys. Mm, I'm about as thrilled to look into AWK as Batch. How about a simplified version of the problem where it just deletes the whole header, replaces spaces for tabs, replaces tabs for comma's and finally deletes all double comma's? Can I do something like that in Batch? – KDC Apr 09 '15 at 22:58
  • Edit: Opened up a new question for a (simpler?) csv implementation: http://superuser.com/questions/899834/windows-batch-to-read-txt-and-parse-lines-to-csv – KDC Apr 10 '15 at 00:06
  • 1
    Don't use batch for this, for sanity's sake. – Journeyman Geek Apr 10 '15 at 02:37

1 Answers1

1

powershell V4:

gc .\data.csv | ConvertTo-Json | sc .\data.json

powershell V4,V3:

Using PowerShell for dynamic JSON parsing

ConvertTo-Json

powershell V2:

Read Json Object in Powershell 2.0

But in general, I think that you can get the data in xml with weather station ...

Community
  • 1
STTR
  • 6,767
  • 2
  • 18
  • 20