Custom Parsing of file

Anonymous · ‎02-24-2020

Hi all

I'm like experimanting. We work with Reaper, it's an audio software, and we discover that Power BI reads as text de rpp files were the audio session is stored.

I'd be super usefull to be able to track some information in there. But, there's a bit but. The rpp file needs to be parsed and obviously it's not a JSON or XML. it looks like this:

<REAPER_PROJECT 0.1 "6.0/x64" 1582307164
  RIPPLE 0
  GROUPOVERRIDE 0 0 0
  AUTOXFADE 1
  ENVATTACH 1
  POOLEDENVATTACH 0
  MIXERUIFLAGS 11 48
  PEAKGAIN 1
  FEEDBACK 0
  PANLAW 1
  PROJOFFS 0 0 0
  MAXPROJLEN 0 600
  GRID 3199 8 1 8 1 0 0 0
  TIMEMODE 1 5 -1 30 0 0 -1
  CURSOR 256
  ZOOM 0.24379167824074 0 0
  VZOOMEX 8
  USE_REC_CFG 0
  RECMODE 1
  SMPTESYNC 0 30 100 40 1000 300 0 0 1 0 0
  LOOP 0
  LOOPGRAN 0 4
  RECORD_PATH "Audiofiles" ""
  <RECORD_CFG
    ZXZhdxgBAA==
  >
  <APPLYFX_CFG
  >
  RENDER_FILE ""
  RENDER_PATTERN Test_2
  RENDER_FMT 0 1 48000
  RENDER_1X 0
  RENDER_RANGE 1 0 0 2 1000
  RENDER_RESAMPLE 9 0 1
  RENDER_ADDTOPROJ 0
  RENDER_STEMS 32
  RENDER_DITHER 0
  TIMELOCKMODE 1
  TEMPOENVLOCKMODE 1
  ITEMMIX 0
  DEFPITCHMODE 589824 0
  TAKELANE 1
  SAMPLERATE 44100 0 0
  <RENDER_CFG
    ZXZhdxABAA==
  >
  LOCK 1
  <METRONOME 6 2
    VOL 0.25 0.125
    FREQ 800 1600 1
    BEATLEN 4
    SAMPLES "" ""
    PATTERN 2863311530 2863311529
  >
  GLOBAL_AUTO -1
  TEMPO 120 4 4
  PLAYRATE 1 0 0.25 4
  SELECTION 0 0
  SELECTION2 0 0
  MASTERAUTOMODE 0
  MASTERTRACKHEIGHT 0 0
  MASTERPEAKCOL 16576
  MASTERMUTESOLO 0
  MASTERTRACKVIEW 1 0.6667 0.5 0.5 0 0 0 0 0 0
  MASTERHWOUT 0 0 1 0 0 0 0 -1
  MASTER_NCH 2 2
  MASTER_VOLUME 1 0 -1 -1 1
  MASTER_FX 1
  MASTER_SEL 0
  <MASTERFXLIST
    WNDRECT 24 52 655 408
    SHOW 0
    LASTSEL 0
    DOCKED 0
    BYPASS 0 0 0
    <VST "VST3: FabFilter Pro-L 2 (FabFilter)" "FabFilter Pro-L 2.vst3
    >
    FLOATPOS 0 0 0 0
    FXID {E1E532DB-D91D-4C03-85E8-0FC810E1FACF}
    WAK 0 0
  >
  <MASTERPLAYSPEEDENV
    ACT 0 -1
    VIS 0 1 1
    LANEHEIGHT 0 0
    ARM 0
    DEFSHAPE 0 -1 -1
  >
  <TEMPOENVEX
    ACT 1 -1
    VIS 1 0 1
    LANEHEIGHT 0 0
    ARM 0
    DEFSHAPE 1 -1 -1
  >
  MARKER 1 0 Char1 0 0 1 R
  MARKER 2 483.96904166666684 Char 20 0 1 R
  MARKER 3 1096.9550625 Char3 0 0 1 R
  MARKER 4 1237.2328541666661 Char4 0 0 1 R
  MARKER 5 1810.3272708333313 Char5 0 0 1 R
  MARKER 6 4228.4236458333344 Char6 0 0 1 R
  <PROJBAY
  >
  <TRACK {968B67E9-FE2B-4E85-9F24-8FF00F72BBAD}
    NAME Track1
    PEAKCOL 16827216
    BEAT -1
    AUTOMODE 0
    VOLPAN 1 0 -1 -1 1
    MUTESOLO 0 0 0
    IPHASE 0
    ISBUS 0 0
    BUSCOMP 0 0 0 0 0
    SHOWINMIX 1 0.6667 0.5 1 0.5 0 0 0
    FREEMODE 0
    SEL 0
    REC 0 0 0 0 0 0 0
    VU 2
    TRACKHEIGHT 0 0 0
    INQ 0 0 0 0.5 100 0 0 100
    NCHAN 2
    FX 1
    TRACKID {968B67E9-FE2B-4E85-9F24-8FF00F72BBAD}
    PERF 0
    MIDIOUT -1
    MAINSEND 1 0
    <FXCHAIN
      WNDRECT 24 52 1127 653
      SHOW 0
      LASTSEL 0
      DOCKED 0
      BYPASS 0 0 0
      <VST "VST3: FabFilter Pro-Q 3 (FabFilter)" "FabFilter Pro-Q 3.vst3
      >
      FLOATPOS 0 0 0 0
      FXID {49DC65AD-7B65-4D48-A71D-4867CA938008}
      WAK 0 0
    >
    <ITEM
      POSITION 0
      SNAPOFFS 0
      LENGTH 1.54797916666667
      LOOP 1
      ALLTAKES 0
      FADEIN 1 0.01 0 1 0 0
      FADEOUT 1 0.01 0 1 0 0
      MUTE 0
      SEL 0
      IGUID {D937085A-F7DA-4D55-BA98-F6AF5250E7E6}
      IID 1
      NAME filename1.wav
      VOLPAN 1 0 1 -1
      SOFFS 0
      PLAYRATE 1 1 0 -1 0 0.0025
      CHANMODE 0
      GUID {363A22A5-E5C2-4298-B483-C6FA001F28AD}
      <SOURCE WAVE
        FILE "\\path\filename1.wav"
      >
      <TAKEFX
        WNDRECT 24 52 717 766
        SHOW 0
        LASTSEL 0
        DOCKED 0
        BYPASS 0 0 0
        <VST "VST3: RX 7 De-reverb (iZotope, Inc.)" "RX 7 De-reverb.vst3     
        >
        FLOATPOS 0 0 0 0
        FXID {0D7CEF06-50A1-4ED6-9EBF-03DBF4DFAAD2}
        WAK 0 0
      >
    >

I've been looking to customize some how the JSON parse function without succes.

Each <....> is an object that contains objects inside

The biggest object is the first one wich is the audio project.

<REAPER_PROJECT 0.1 "6.0/x64" 1582307164

in side we can find lots of parameters with their values a some parameters are object themself that woudl contain parameters but in tab gradient like:

there should be a function like the JSON to do this right??

Cheers

ImkeF · ‎02-24-2020

Hi @Anonymous

please check if the enclosed file gets you going. It uses a dynamic split-function I've created a while ago: https://www.thebiccountant.com/2018/10/26/return-all-occurrences-of-text-between-delimiters-in-power-bi-and-power-query/

It flattens the hierarchy, but that doesn't look too bad for your example.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎02-27-2020

Hi @ImkeF

That's definitely very good approach it think. i got to problems with it.

We aim to get several Reaper files from a folder, so the Source will be a folder query looking like this:

I'm not sure how change the source to this type. As you pasted the text directly to the input text.

In the other hand. How it is split we lose the relation when there's a nested object in other. I mean, in the sample I sent for example, in side the "TRACK" object ther's other object "FXCHAIN" and in side this one there's other object "VST".

There are many nested levels, as I understand your function could be applied many times generating coulmns each time from in to out.

level 1 delimiters are "<",">" (this is the project level)

level 2 delimiters are " <"," >" (here there are project parameters (can be objects too like the Metronome or FXLIST) and tracks)

level 3 delimiters are " <"," >" (here there are track parameters, parameter objects (e.g. FXCHAIN) and ITEMS (this would be audio in the tracks)

level 4 delimiters are " <"," >" (here we got FXCHAIN parameters, VST objects. Or ITEM parameters and obj param like SOURCE WAVE and TAKE FX)

level 5 delimiters are " <"," >" this I think would be the most deep level that would be a VST in side the TAKEFX in side an ITEM

Not sure if this would be posible as your function just allows 2 delimiters and this would need like 10 😥. Do you think may be customizing the json parsing fuction would make it??

Thanks a lot for your help, this has been really enlighting.

ImkeF · ‎02-27-2020

Hi @Anonymous

These delimiters look good.

Will try to develop something later.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎09-14-2020

Hi @ImkeF

Could you have a look at this??

cheers

mwegener · ‎09-15-2020

Hi @Anonymous ,

What information do you want to extract?

Have you already tried "Column by Example"?

Can you provide 2-3 sample files?

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials