Find and cut lines from list to new list based on name

Questions & Suggestions
1

Hello KM !

I'm trying to create new lists from one master list. So for example in this list

Christi,19946752,20194048
Brandon,20194048,20369664
Christi,20369664,20520192
Christi,20520192,20631296
Brandon,20745984,20980736
Joe-010,28729383,28733432
Brandon,22341211,22443280
Joe-010,23488449,23499482
Christi,20123984,20124432

I would like to group like so...

list1:
Christi,19946752,20194048
Christi,20369664,20520192
Christi,20520192,20631296
Christi,20123984,20124432

list2:
Brandon,20194048,20369664
Brandon,20745984,20980736
Brandon,22341211,22443280

list3:
Joe-010,28729383,28733432
Joe-010,23488449,23499482

The names of that first parameter (like Christi, Brandon) would always be different so the macro would have to account for that. The number of lists found would also be different each run also of course.

3

split list.kmmacros (6.5 KB)

Here is what I have tried before realizing I wasnt grasping the workflow.

4

I have just posted a macro which should do what you asked:

MACRO: Break List into Groups [Example]

Does this work for you?

Please feel free to post any questions, issues, and/or suggestions about this macro.

1 Like
5

Hey @BillytheHicks,

You did a good job of showing us a proper data-sample with before and after examples.

That makes it sooo much easier to help you.  :smile:

It turns out that splitting the list takes a whole one line of awk in the shell.

But – since the data is split into files it takes a little doing to get it back into Keyboard Maestro variables.

If I was doing this for myself I'd have used AppleScript to return the data to Keyboard Maestro variables, but I've used Keyboard Maestro actions instead to demonstrate how that's done.

(If anyone wants to see what the AppleScript looks like speak up.)

I've left the split files in place – just in case they're useful. (They're easy enough to delete in the macro if you don't want to keep them.)

The directory where the files are written is opened in the Finder. If you don't want that to happen you can delete (or comment-out) this line in the shell script:

open "$testDir"

This sort of thing is exactly why I decided to learn more about shell scripting 10 years ago.

-Chris

Split CSV Data by Field v1.0.kmmacros (7.8 KB)

Keyboard Maestro Export.png585×2224 186 KB

1 Like
6

wow guys, really amazing, thank you so much. I tried both and they work flawlessly. I cant thank you enough. I can see how both can be useful depending on my situations. So for @JMichaelTX example its great when I need these lists for myself. But with @ccstone example I can pass these lists to others that would be able to use/import them.

Thanks again!

7

If by that you mean you need a file to send to other, that is easily added to my macro, at the end of the For Each Action loop:

image

I'll update my macro in a bit to include these Actions.

1 Like
8

@BillytheHicks, as promised:

Just posted an update to my OP.

1 Like
9

hey @JMichaelTX thanks works like a charm! Taking this one step further would I be able to process every other group? The odd numbered lists I am not concerned about for this specific question but the even numbered lists I am (Local__List 2, 4, 6, etc) I am looking to take those even numbered lists and extract each of the bold numbers in the example below.

So in Local__List2 I want to idenitfy that first number 179082914, run some actions based on that number, identify the last number in that group 179792677 and run some actions with that number. then continue to Local__List4 and do the same thing until the end of the list.

Also as you might have noticed I turned off the alphabetize action to keep the list in the order it was created.

Variables Created

Local__List1
SK BDC ,178554794,178614633
SK BDC ,178614633,178616725
SK BDC ,178616725,178663132
SK BDC ,178663132,178684327
SK BDC ,178684327,178964282

Local__List2
BDC JON,179082914,179150077
BDC JON,179150077,179152304
BDC JON,179152304,179160843
BDC JON,179160843,179161281
BDC JON,179161281,179346029
BDC JON,179346029,179360678
BDC JON,179399339,179415014
BDC JON,179415014,179477513
BDC JON,179477513,179535232
BDC JON,179535232,179585654
BDC JON,179585654,179653306
BDC JON,179653306,179655601
BDC JON,179655601,179669322
BDC JON,179669322,179792677

Local__List3
2016030,197931059,197999924
2016030,197999924,198012017
2016030,198012017,198084926
2016030,198127426,198205076
2016030,198205076,198267580

Local__List4
2016030,198284024,198360509
2016030,198360509,198363614
2016030,198363614,198372018
2016030,198372018,198374667
2016030,198374667,198387037
2016030,198387037,198391762
2016030,198391762,198423722
2016030,198423722,198608639

Local__List5
2016030,198284024,198360509
2016030,198360509,198363614
2016030,198363614,198372018
2016030,198372018,198374667
2016030,198374667,198387037
2016030,198387037,198391762
2016030,198391762,198423722
2016030,198423722,198608639

Local__List6
2016030,198634897,198648920
2016030,198648920,198649468
2016030,198649468,198657442
2016030,198657442,198670199
2016030,198670199,198713872
2016030,198749107,198792090
2016030,198792090,198829904

10

I'm glad it worked for you.

Let's look at your data in spreadsheet terms.
Please confirm my understanding of your requirements:

  1. For a given list, you want to extract B1 and Cn, where "n" is the last row.
    • This would require that the source data always be pre-sorted in the proper order
  2. The question is how to identify the lists of interest
    • You stated "even numbered" lists
    • May I suggest that is not a very reliable method
    • It requires that the data always be entered in a precise order
    • It would be better to identify the list by name, like the value of A1.
  3. Do you have any need for the lists other than to extract these data?
    • If not, then we don't need to build the lists to get the data

My macro won't work without sorting the data first, unless the source data is already sorted to put all rows of data together for a given list. The source data you uprovided here was not pre-sorted:

So, is it possible that you can provide, as source data, two things:

  1. A list of the names of the lists (like "Christi", "Brandon", "Joe-010")
  2. The source data for the lists

Finally, we always ask for the user (you) to provide us with real-world source data, and then desired output data. So please provide us with an actual example of source data as you receive it to start your workflow, and your complete workflow steps. It is better for us to know in the beginning everything that you plan to do with the data, rather than for you to piece-meal it to us.

11

My apologies. The data I first provided was not actually specifically correct in that the numbers of column B/C should have numerically increased or stayed the same row by row. Further explained below. Here is an accurate data sample.

Bill Hicks source data - its yuge!.kmmacros (33.4 KB)

This is exactly right. The reason I disabled the alphabetize was because the list was already going to be in what I would consider the proper order, namely numerical. In other words the macro I use to log this list will always be logging B and C in a numerical order, so the numbers in each next row will either stay the same or increase. They are in/out timecodes in a timeline and my macro always starts at the beginning and moves forward logging the original list. Again sorry my original data sample didnt reflect this. So the lines go like this...

name of file,timecode in, timecode out.
BDC JON,179082914,179150077

So for my example 179082914 is the lowest number (timecode in) for Local__List2 and 179792677 (timecode out) is the highest.

I am not sure how else to call it but I am interested in extracting just those numbers B1/Cn in every other list.

yes I foresee building other macros based on the original list you created. So one thing I had in mind was being able to search column A for reoccurring names. So for example the name in column A of Local__List2 is BDC JON. I would be able to search the entire list for that name which may occur many times in the list.

12

Actually kind of stepping through that idea of building a search maco I would need the B1 Cn of each group so having a list of just group,B1,Cn would be useful. And I imagine faster than searching through the hundreds of lines of the original 'master' list. So a list that looked like this ?

Filename(group),B1,Cn

SK BDC,178554794,178964282
BDC JON,179082914,179792677
2016030,197931059,198267580
Etc..

13

OK, so using an abbreviated set (4 groups, limited rows) of data from your last Source Data, I have a revised Macro that will produce this:

Example Output

image

I have changed most KM Variables from Local to Global variables (meaning that they were persist permanently until deleted).
All of these Global Variables have a prefix of:
DND_ETC__
which means "Do Not Delete" and then "Extract Time Codes".
Hopefully this will allow you to create/keep these variables without accidental interaction with other Macros. Meanwhile, they are available to other Macros that you want to use the data.

However, do note that these Variables are cleared/overwritten whenever this Macro is run:

DND_ETC__GroupTimes
DND_ETC__List1
DND_ETC__List2
DND_ETC__List3
DND_ETC__List4
. . .
DND_ETC__Listn (where n is the number of lists)
DND_ETC__OuputFolder
DND_ETC__OutputToFile
DND_ETC__SourceList
DND_ETC__VarList

You can view all of these DND_ETC variables at any time using the KM Editor > Preferences > Variables Tab:

image

Please test this macro and let me know if it works for you and/or if you have questions.

MACRO:   Extract TimeCodes in Group Lists [Example] @BillyTheHick

~~~ VER: 1.2    2018-05-16 ~~~

DOWNLOAD:

Extract TimeCodes in Group Lists [Example] @BillyTheHick.kmmacros (67 KB)
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.

ReleaseNotes

Author.@JMichaelTX

PURPOSE:

  • Separate a List into Variables Based on String at Beginning of Line
    • Provide option to Output to Files
    • Create Variable for Group Times of Even-Numbered Groups
      • Group Name (A1),TimeCode In (B1),TimeCode Out (Cn)
        (where n is the number of Rows in the Group)

REQUIRES:

  1. KM 8.2+
  • But it can be written in KM 7.3.1+
  • It is KM8 specific just because some of the Actions have changed to make things simpler, but equivalent Actions are available in KM 7.3.1.
    .
  1. macOS 10.11.6 (El Capitan)
  • KM 8 Requires Yosemite or later, so this macro will probably run on Yosemite, but I make no guarantees. :wink:

NOTICE: This macro/script is just an Example

  • It has had very limited testing.
  • You need to test further before using in a production environment.
  • It does not have extensive error checking/handling.
  • It may not be complete. It is provided as an example to show you one approach to solving a problem.

How To Use

  1. Enable the Action you wish to use to set the Source Data:
    • Set Variable (default, and enabled)
    • Copy (disabled)
      • If you use this, then first select the text to be used as Source
    • Read file (disabled)
  2. Trigger this macro.
  • It will then sort the data so that lines that begin with the same string are grouped to together.
  • A RegEx is performed to extract the Groups into separate Variables, named as follows: Local_List<N>
    • where <N> is the sequential integer based on Group position in the sorted Source List.

MACRO SETUP

  • Carefully review the Release Notes and the Macro Actions
    • Make sure you understand what the Macro will do.
    • You are responsible for running the Macro, not me. ??
      .
  1. Assign a Trigger to this maro..
  2. Move this macro to a Macro Group that is only Active when you need this Macro.
  3. ENABLE this Macro.
    .
  • REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:
    (all shown in the magenta color)
    • Enable ONE of the first 3 Actions to choose your method of setting the Source Data.
    • Be sure to DISABLE the other two Actions.
    • IF you choose Read File, enter full POSIX path to file
    • IF you choose Set SourceList to Text, Enter the list in the Action's text box
    • Prompt User for Output Options
      • Change defaults as desired

TAGS: @List @Variables @RegEx

USER SETTINGS:

  • Any Action in magenta color is designed to be changed by end-user

ACTION COLOR CODES

  • To facilitate the reading, customizing, and maintenance of this macro,
    key Actions are colored as follows:
  • GREEN -- Key Comments designed to highlight main sections of macro
  • MAGENTA -- Actions designed to be customized by user
  • YELLOW -- Primary Actions (usually the main purpose of the macro)
  • ORANGE -- Actions that permanently destroy Variables or Clipboards,
    OR IF/THEN and PAUSE Actions

USE AT YOUR OWN RISK

  • While I have given this limited testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
  • If you have any doubts or questions:
    • Ask first
    • Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.

image
image.png1162×6964 1.29 MB

14

Hey @JMichaelTX I was able to take it for a test spin this morning. Thanks man, this is a huge help and is doing exactly what I am looking for. I did notice one snafu when I got to the 6th line in DND_ETC__GroupTimes which would correlate to DND_ETC__List12.

List 12 of my data set is only this single line....

DND_ETC__List12
AA01800,222883717,222888307

And for some reason the last digit is cut from B1

Even-Numbered Group Times

DND_ETC__GroupTimes
Group Name (A1),TimeCode In (B1),TimeCode Out (Cn)
BDC JON,179082914,179792677
AA01810,213695710,215268068
AA01800,216478686,216925439
C104680,222115649,222154462
AA01800,222209339,222605901
AA01800,22288371,222888307

15

Actually after going through the entire list I noticed a few spots where the last digit of B1 is missing. They all seem to be connected to single line lists.

DND_ETC__List12
AA01800,222883717,222888307

DND_ETC__List14
AA01800,222934005,222939824

DND_ETC__List18
BDC JON,233787614,234023459

DND_ETC__List54
BDC 020,295442644,295714679

DND_ETC__List56
BDC 020,295752951,295880392

Even-Numbered Group Times

DND_ETC__GroupTimes
Group Name (A1),TimeCode In (B1),TimeCode Out (Cn)
BDC JON,179082914,179792677
AA01810,213695710,215268068
AA01800,216478686,216925439
C104680,222115649,222154462
AA01800,222209339,222605901
AA01800,22288371,222888307
AA01800,22293400,222939824
BDC JON,232058568,233192534
BDC JON,23378761,234023459
BDC JON,235817404,236090039
YWCAS07,243859390,244536314
BDCR110,257040554,257579407
BDCR126,260173410,263300234
BDC 043,264513812,265393274
AA01770,274436722,275100747
AA01770,275158634,275481690
C104700,278957095,279218436
BDC 033,279447729,279533849
BDC 033,280780034,280893708
BDC 033,280929277,281113552
Take22.,283510679,284537750
BDC 030,286104893,286210730
BDC 030,286829505,288842800
BDC 030,292856257,293249984
BDC 030,294184049,294402614
BDC 030,294603806,294872279
BDC 020,29544264,295714679
BDC 020,29575295,295880392
BDC 020,295894094,296434634
BDC JON,302450369,302725920
LYDIATE,317744283,319444304
BDC 031,326036692,329079930
C104690,364603072,365398425
BDCR125,396201341,397212086
AA01780,397570106,397648001
AA01750,398356279,399141371
AA01790,401027996,401515616
bdc 006,401896181,402090986
BDC JON,404335428,404820146

16

OK, easy fix. I didn't design for a single line group. But now I do.

Use this RegEx:
(?is)^([^,]+),([^,]+).*,(.+)\Z

in this Action:
image

You can see the RegEx explanation here:

Example Results with Fix

image

Please review/test and confirm that this is now working correctly for you.

17

Great @JMichaelTX that did it!

Last question, if I wanted to also make a separate list with all the groups (not just every other group) would that be easy?

18

Wait I think I got it @JMichaelTX. I changed

(Local__ListNum MOD 2) = 0
to
(Local__ListNum)

and that seemed to do it

19

That will work since the IF/THEN Action is looking for a non-zero result.
So you could just as easily enter a 1, OR, if this is a permanent change, just drag the actions outside of the IF/THEN.

image

1 Like
20

If one of the above posts solves your problem/question as originally stated, please check the "Solved" checkbox (click for details) at the bottom of that post.

Otherwise, please post your remaining questions/issues about this problem.
If you have other questions, please start a new topic.

1 Like
21

@BillytheHicks,

The RegEx in this Action:

image
image.jpg695×243 45.3 KB

needs to be replaced with:
(?sm)^([^,]+?),.+?(?:(\R(?!\1))|\Z)

The change is from \n to \R in order to allow for all end-of-line characters.

I encountered this in your latest topic:
Find number of occurrences in list and organize from most to least