Set variables by column position instead of json key/value in excel import

What’s the best way to achieve this…

I am trying to read from a spreadsheet put out by our finance team daily with the aim of importing this data into our MySQL DB.

Complicating matters is that the column headers are dynamic - i.e. change by date. For example here’s a sample header.

"CURRENT CASH BALANCE as of report run date 06/29/2023"

These get translated as keys when I try to set variables which results in

{{ $json['CURRENT CASH BALANCE as of report run date 06/29/2023'] }}

Clearly this won’t work tomorrow unless I change the keys.

Is there any way I can refer by column position? {{$json[5]}}? Failing that, can I use the fact that the first part of the column name doesn’t change - the CURRENT CASH BALANCE part above?

Hey @timothevs,

So originally I was thinking maybe a code node would be the option here, Do a loop over the keys and it it matches your pattern do a rename. I then remembered that we have a “Rename Keys” node which I have not used before.

Turns out it has an expression option in it so you can just pop in CURRENT CASH BALANCE as of report run date \d\d\/\d\d\/\d\d\d\d as your regex option and then a new value under it like CurrentBalance and problem solved.

Little workflow example below for you.

This is ingenious - n8n manages to surprise continually. Thank you @Jon !

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.