JSON match string parse

Hi folks,

I have a simple string received via IMAP from an email and containing a var {{ $json["text"] }} which looks like:

...from xxx LTD for x,xxx.xxCURRENCY issued 08/06/2020 and due by 01/07/2021 see...

I’d like to extract given substrings like x,xxx.xxCURRENCY which could be something like 1,000.00€ or 300.00USD and the relative dates preceded by issued ... and due by ....

I started to use something like: {{ $json["text"].match(/by \s*(\d+)/).toString() }} but with not much success, I get stuff like 01,01 for the dates and similar. Any ideas how I could achieve this and is {{ $json["text"].match(<whatever>).toString() }} the best way to do it?

Thanks for hints guys;)

Not as elegant as a regular expression, but the function node below should do it.

const results = []

for (const item of items) {
  const issue = item.json.text.split('issued')
  results.push({
    json: {
        money: issue[0].split(' ')[issue[0].split(' ').length - 2],
        date: issue[1].split(' ')[1],
        dueBy: issue[1].split('by')[1].split(' ')[1]
    }
  })
}

return results
Example workflow
1 Like

Top, thanks, will try this out asap;)

@RicardoE105 any idea how to “format” x,xxx.xxCURRENCY into xxxx.xx which could be compatible with the Airtable currency field? Thanks;)

I found this Converting a number to a string which could be useful

Try it like this:

const results = []

for (const item of items) {
  const issue = item.json.text.split('issued')
  results.push({
    json: {
        money: issue[0].split(' ')[issue[0].split(' ').length - 2],
        date: issue[1].split(' ')[1],
        dueBy: issue[1].split('by')[1].split(' ')[1],
        moneyParsed: parseFloat(issue[0].split(' ')[issue[0].split(' ').length - 2].replace('USD', '').replace('EUR').replace('€', '').replace(/,/g, ''))
    }
  })
}

return results

Will try, thanks;)

works, thanks;)

1 Like