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
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.
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.
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.
My data is in a simple Excel spreadsheet and can be exported as a CSV.
Your AQ example is correct. Thanks.
I’m not sure exactly what you are trying to accomplish, but have you tried using the Excel function
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:
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.
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.
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.
@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.
In regards to the TSV-data, then @peternlewis helped me solve this in the old forum:
[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:
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.
Sorry, I must remember to properly quote my regex, otherwise the asterisks get lost.
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.