How to Get Excel Spreadsheet Into Prompt With List Action?

[Macs_Productivity]

I have an Excel spreadsheet (though I can convert it to CSV or another format if needed). It has hundreds of rows and 5 columns.

My goal is to create a macro that prompts with a list where each item in the list contains columns 1–4 separated by hyphens or some other character.

What's the best way to do this?

Here is some example data:

Abbreviation,Full Name,Description,Code,Comment
A,"Example, Name 1","The description goes here, it could be pretty long",A1.1,This is a comment
AA,"Example, Name 2","The description goes here, it could be pretty long 2",A2.1,
AB,"Example, Name 3","The description goes here, it could be pretty long 3",A1.2,Here's another comment
ABC,"Example, Name 4","The description goes here, it could be pretty long 4",B2.1,

This would be an example row in the prompt with list action:

A - Example, Name 1 - The description goes here, it could be pretty long - A1.1

[Airy]

Is this solution ok?

[Macs_Productivity]

Some of the fields have commas in them, so I don't think this will work

[griffman]

You don't need the last field, "This is a comment" for line one, for example?

-rob.

[Airy]

@Macs_Productivity

Some of the fields have commas in them, so I don't think this will work

To my knowledge, there is no simple solution built-in to macOS that will discriminate between delimiters (eg, commas) and delimiters that are in quoted fields. So either you need a more complex solution than mine above, perhaps using awk or regex, or you can change the delimiter between fields to something unique. Remember, you did say you can create a CSV file. And if you can do that, you can create a unique delimiter to separate fields. I usually use the "~" character for cases like this, since it's rarely used in human-generated text. In that case, my code above would work, if you replaced the comma in my code with a tilde.

[Macs_Productivity]

Thanks, I was hoping for a simpler solution, but I ended up resorting to a Python script in a shell script action:

python3 -c "import csv, os
with open(os.path.expanduser(os.environ['KMVAR_Local_CSV'])) as f:
    for row in csv.reader(f):
        print(' - '.join(row[:4]))"

[ALYB]

If you use Python, you could also use a library for Excel to skip the conversion to CSV.

[Nige_S]

@Macs_Productivity

Thanks, I was hoping for a simpler solution

I don't know if it's any simpler, given you have to get your data into CSV somehow, but you could use sqlite instead of python:

SQL CSV Conversion Test.kmmacros (2.6 KB)

[roosterboy]

If you have a sufficiently recent version of Excel, you could do this:

Although I guess, technically, you'd want to use this instead:

set xlRng to get address of (current region of range "A2") with column absolute and row absolute

Sorry, I didn't include a header row in my test data.