Array Lookup

Questions & Suggestions
1

I have a 3 column array currently in a CSV file. e.g. 10+ rows of:

ID CITY STATE
AQ New York NY
WS Seattle WA
(etc.)

I am accessing records that have a corresponding variable ID_VAR from another file and need to lookup the corresponding CITY_VAR and STATE_VAR and then set 2 VARS in KM with these values.

Is it possible to do this easily? Perhaps using a 2D Array Var of some kind? Thanks.

2

KM does not support true arrays. You will need a script to process this. Should be easy enough to do in AppleScript or JXA. JavaScript has great array handling.

1 Like
3

I can see 2 clumsy workarounds but was hoping for a more straightforward solution:

– replicate the data down in 2 new columns of my spreadsheet and access from there.
– do a 10+ way IF THEN ELSE in KM

Any other ideas would be appreciated.

4

Hey Steve,

The data you show is not csv data.

Are you saying that GIVEN “AQ” you want the lookup to produce:

CITY_VAR  =  New York
STATE_VAR =  NY

If so then it's very simple to do with a regular expression.

But. You need to properly define what the data looks like if you want help.

-Chris

1 Like
5

Chris:

My data is in a simple Excel spreadsheet and can be exported as a CSV.

Your AQ example is correct. Thanks.

6

I'm not sure exactly what you are trying to accomplish, but have you tried using the Excel function VLOOKUP() ?

1 Like
7

Put your data in a tab delimited file.

Use the Execute a Shell Script action:

grep "^$KMVAR_ID_VAR\t" ~/thefile.txt

That will return the matching line of the file. Then use the Search Variable action with search:

.*\t(.*)\t(.*)

and that will break your line into parts and return the CITY_VAR and STATE_VAR.

You can use commas instead of tabs (and use , instead of \t). But make sure there are exactly two tabs on every line and no quotes anywhere.

1 Like
8

Peter – I am unclear how this would fully address the issue as I am trying to “lookup” by starting with the corresponding value in a separate place as mentioned above. Thanks.

9

I am unclear as to what your actual problem is then. My understanding is:

  • You have a CSV or TSV file with your three column data in it.
  • You have a Keyboard Maestro variable ID_VAR that has the ID column (eg “WS”).
  • You want to set the Keyboard Maestro variables CITY_VAR and STATE_VAR based on looking up the ID_VAR in the CSV file.

What I suggested will accomplish that, but if that is not correct, please explain what you actually have and what you actually want.

1 Like
10

@Peter - I had misread it. It does seem to mostly work.
Still having problem with the regex for the SEARCH VARIABLE command for either CSV (which I am trying to actually use) or TSV. In my code the VARs (e.g. aa_1) are not created.

11

In regards to the TSV-data, then @peternlewis helped me solve this in the old forum:

([^\t]*)

[abc] means a single character, either a or b or c.
[a-c] means the same thing
[A-Za-z0-9] would be an ASCII letter or digit.

Add an ^ at the front means "anything except", so

[^A-Za-z] would be anything except an ASCII letter

[^\t] means anything except a tab

So you want something like this:

^([^\t]*)\t([^\t]*)\t([^\t]*)\t([^\t]*)

In this case, the ^ means "match at the start of the search string" - often not required, but sometimes required and often a good idea if that is what you mean.

I think you can use this as a starting point.

1 Like
12

Sorry, I must remember to properly quote my regex, otherwise the asterisks get lost.

.*\t(.*)\t(.*)

or for CSV:

.*,(.*),(.*)
  • The dot-star means zero or more of any character (except line ending characters).
  • The comma means itself.
  • \t means tab.
  • The brackets indicate capture matching groups.
2 Likes
13

Great solution - thanks!