FlorianLeo

Q: AppleScript - extract values from a table, create a text-file with these values

Hi everyone

 

Lets say I have a table that looks a bit like this

Scripting Table.png

 

And from this table I would like to create 2 text-files (or even more depending on how many switchnames are in there) that look a bit like these

Scripting Text File 1.png

Scripting Text File 2.png

Is this even possible?

I would assume to create a Service (that can be called from within Numbers) with Automator that includes an Applescript - but from there - no idea since scripting is not one of my strong suits.

It doesn't need to be perfect since the tables do not necessarily follow the pattern presented above - so to tweek the script on demand will be necessary. The text-files can be created/saved in the same folder as the Numbers-file is located in.

 

Is there a script-genius out there?

 

Cheers

Florian

Posted on Feb 26, 2016 2:56 PM

Close

Q: AppleScript - extract values from a table, create a text-file with these values

  • All replies
  • Helpful answers

Previous Page 2
  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 27, 2016 3:04 PM in response to Wayne Contello
    Level 6 (19,017 points)
    iWork
    Feb 27, 2016 3:04 PM in response to Wayne Contello

    Here are how the column offsets work:

    Screen Shot 2016-02-27 at 5.03.36 PM.png

  • by FlorianLeo,

    FlorianLeo FlorianLeo Feb 27, 2016 5:26 PM in response to Wayne Contello
    Level 1 (9 points)
    Mac OS X
    Feb 27, 2016 5:26 PM in response to Wayne Contello

    Hello Wayne

     

    I did understand what you meant with offset, and that I had to change it i.e. if the VLAN-ID is in the 4th column I had to change the script so that the corresponding code looking for the VLAN-ID would count further down the row to find the proper cell, and that the corresponding code looking for the description would count further down the row to find the proper cell, too.

     

    Switchname - I screwed that up - obviously. The Switch can have (almost) any name you could think of - with almost any combination of

    • A-Z
    • a-z
    • 0-9
    • except for the question mark <?> I would say any other sign as well

     

    I beg you pardon that I've lead you on the wrong path.

     

    The table is ordered by the name of the Switch though i.e. you won't find corresponding lines (i.e. lines that belong to the same switch in a logical manner) scattered throughout the table i.e. if there are for example 5 lines that belong to 1 and the same Switch they are listed consecutively.

  • by SGIII,

    SGIII SGIII Feb 27, 2016 5:47 PM in response to FlorianLeo
    Level 6 (10,637 points)
    Mac OS X
    Feb 27, 2016 5:47 PM in response to FlorianLeo

    FlorianLeo wrote:

     

    Out of curiosity - what if I'd like to use only parts of column 1?

     

    My script ignores rows where column 1 is blank.  If you need to limit it further, say to the first 20 rows only, you could change this line:

     

    set totalCount to count vv2

     

    to:

     

             set totalCount to 20


    I believe Wayne's script would also require just a simple change to accomplish the same thing.


    I am little like "monkey see, monkey do" - better is learning by doing, I need to play.


    That's a good way to learn. Here you've got two different examples to study. Keep in mind that this is a tough problem for a beginning scripter to solve from scratch.  There's too much going on here. But it should be easy to run either of the scripts and make changes to them.  Post back if you are still getting an error message. Note that if your table looks more like your second screenshot you would change:

     

    -- configure to match your Numbers table: put 1 for col A, 2 for col B, etc.

    set {nameCol, portCol, vlanCol, descCol} to {1, 2, 3, 4}


    to:


    -- configure to match your Numbers table: put 1 for col A, 2 for col B, etc.

    set {nameCol, portCol, vlanCol, descCol} to {1, 2, 4, 5}

     


    SG

  • by FlorianLeo,

    FlorianLeo FlorianLeo Feb 27, 2016 6:34 PM in response to SGIII
    Level 1 (9 points)
    Mac OS X
    Feb 27, 2016 6:34 PM in response to SGIII

    Hello SGIII

     

    BINGO - the language! That's super-stupid - NOT FROM YOU - the other ones

     

    I always thought that any language other than English is kind of "overlayed" - like it happens with the file-system. In the CLI it's all English, but in Finder it's all in (my case) German.

     

    Ok - in the script I've tried to replace "active sheet" with the actual name of the sheet in "my German" version of the table (i.e. "Blatt 1"). It just doesn't work - I just can't always shut down the Mac and come up with different language settings.

     

    I have no problem editing the script, writing the "correct" names of sheets, tables etc. It is what it is

    it is not "sheet 1" -> it is "Blatt 1" (or any other name)

    it is not "table 1" -> it is "Tabelle 1" (or any other name)

     

    Is it possible to "hardcode" these names into the script - I wouldn't mind editing the script so it meets the names for one document, edit it again to meet names for another document, edit it again to meet names for another document, edit it again.......

    I have to edit it anyway since the columns with the data I want, I am interested in, are always different i.e. the column with VLAN might be the 3rd in this document, but might be the 14th in another, and so on.

     

    I am totally fine editing the script i.e.

    give it the proper numbers for the position of the columns

    give it the proper name of the sheet (Blatt 1, Blatt 15, Case 12, Sheet 23, CqDgTZ 4711 ......)

    give it the proper name of the table (Tabelle 1, table 1, Case 34,  UeSs5678 ......)

     

    Only how?

  • by FlorianLeo,

    FlorianLeo FlorianLeo Feb 27, 2016 7:34 PM in response to SGIII
    Level 1 (9 points)
    Mac OS X
    Feb 27, 2016 7:34 PM in response to SGIII

    Hello SGIII

     

    I made it with the name of the sheet 1 i.e. Blatt 1 - it took me a while since I thought placing it between hyphens would suffice - nooooo - placing it in parenthesis would suffice - nooooo.

    Finally - placing it in parenthesis and hyphens

     

    Bildschirmfoto 2016-02-28 um 04.18.52.png

    This is just - strange. I mean look at the picture The name of the sheet is a concern, while neither the file-name nor the table-name are?

     

    Googling the -17something something Error was a Deadend.

     

    I am going to try to implement a dialog, dialogs, asking me for the values/numbers of the columns, the sheets name and then run it. I'll keep you posted!

     

    Cheers Florian

  • by SGIII,

    SGIII SGIII Feb 27, 2016 7:58 PM in response to FlorianLeo
    Level 6 (10,637 points)
    Mac OS X
    Feb 27, 2016 7:58 PM in response to FlorianLeo

    You can of course hardcode document name and sheet name and table name. You would do something like

     

      tell document "MyDocumentName"

     

    instead of

     

       tell front document

     

    and,

     

        tell sheet "MySheetName"

     

    instead of

     

        tell active sheet

     

    and,

     

        tell table "MyTableName"

     

    instead of

     

    tell first table whose selection range's class is range.

     

     

    However, isn't it much easier to just click in the table you mean and then run the script?  That way the script will know exactly what table you mean without you having to code anything. 

     

    My suggestion, if you continue to get errors, to try switching to region of United States was just to check if the error was somehow related to your region, as a way of debugging. There was a case the other day where a user with a region of Brazil discovered that the script would run with error if he switched to the United States and relaunched Numbers (which was pretty easy to do, by the way).

     

    SG

  • by Hiroto,

    Hiroto Hiroto Feb 28, 2016 8:53 AM in response to FlorianLeo
    Level 5 (7,281 points)
    Feb 28, 2016 8:53 AM in response to FlorianLeo

    Hello

     

    Firstly, I don't think locale settings should affect excution of those scripts unless port id and vlan id are decimal values with locale dependent decimal separator which differs from the one initialized in Script Editor, which is not the case here.

     

    Anyway, if the target rows and even target columns are not constant in your scenario, you might simply copy the target data to the clipboard as TSV text and process the text to create output files. This way, you can easily process partial rows and/or partial columns (by hiding unwanted columns before selecting and copying a range of data).

     

    Here're two scripts you might explore. The first is a mere wrapper of Perl script which I'd prefer and the second is written in AppleScript (mainly) which you'd prefer if you're learning AppleScript. To use them, first copy the source range in table to the clipboard, run the script and it will ask you to choose output directory and then create text file for each switch name in the directory.

     

     

    E.g., Given source table:

     

     

    a1.png

     

     

    firstly hide unwanted columns (column C in this example) and select source range (not including header row):

     

     

    a2.png

     

     

    and copy it to the clipboard and run one of the following scripts:

     

     

    --APPLESCRIPT 1
    set d to (choose folder)'s POSIX path
    do shell script "perl -CSDA -w <<'EOF' - " & d's quoted form & "
    # 
    #   ARGV[0] : output directory
    #   
    use strict;
    use POSIX qw(strftime);
    
    # (1) get clipboard contents
    $ENV{LC_CTYPE} = 'UTF-8'; 
    open(PIPEIN, '-|:utf8', 'pbpaste') or die \"$!\";
    my @dd = map {chomp; $_} <PIPEIN>;
    close PIPEIN or warn $! ? \"Error closing pipe-in: $!\" : \"Wait status from pipe-in: $?\";
    
    # (2) set output directory and string constants
    my $outdir = shift @ARGV || '.';
    my $fname = 'Accessport Configuration Script %s.txt';
    my $head = <<'EOT';
    # Script generated with AppleScript for Switch %s
    # at %s
    !
    conf t
    EOT
    
    my $entry = <<'EOT';
    !
    interface gi 1/0/%d
     switch port mode access
     switch port access vlan %d
     description LINK TO %s
    EOT
    
    # (3) process data
    my $sw1 = '';
    for (@dd) {
        my ($sw, $port, $vlan, $desc) = split(/\\t/, $_, -1);
        next if $sw =~ /^\\s*$/o;
        if ($sw1 ne $sw) {
            close OUT if *OUT;
            open(OUT, '>:utf8', $outdir . '/' . sprintf($fname, $sw)) or die \"$!\";
            printf OUT $head, ($sw, POSIX::strftime('%F %T%z', localtime()));
            $sw1 = $sw;
        }
        printf OUT $entry, ($port, $vlan, $desc);
    }
    close OUT if *OUT;
    EOF"
    --END OF APPLESCRIPT 1
    

     

     

     

    --APPLESCRIPT 2
    _main(the clipboard as Unicode text)
    
    on _main(argv)
        (*
            string argv : TSV text of fields as {switch name, port id, vlan id, description}
        *)
        script o
            property aa : _text2array(argv, tab, {return, linefeed})
            property dir : (choose folder)'s POSIX path
            property fname : "Accessport Configuration Script "
            property head : "# Script generated with AppleScript for Switch %s
    # at %s
    !
    conf t
    "
            property entry : "!
    interface gi 1/0/%d
     switch port mode access
     switch port access vlan %d
     description LINK TO %s
    "
            
            set sw1 to ""
            repeat with a in my aa
                repeat 1 times
                    set a to a's contents
                    if (count a) < 4 then exit repeat
                    set {sw, p, vlan, desc} to a
                    if sw = "" then exit repeat
                    if sw ≠ sw1 then
                        set out to dir & fname & sw & ".txt"
                        _printf(out, head, {sw, (current date) as string}, {_append:false})
                        set sw1 to sw
                    end if
                    _printf(out, entry, {p, vlan, desc}, {_append:true})
                end repeat
            end repeat
        end script
        tell o to run
    end _main
    
    on _text2array(t, fs, rs)
        (*
            string t : source text
            string fs : column separator (field separator)
            string rs : row separator (record separator)
            return list : 2d-array represented by t
        *)
        script o
            property pp : _split(t, rs)
            property qq : {}
            repeat with p in my pp
                set end of my qq to _split(p's contents, fs)
            end repeat
            return my qq's contents
        end script
        tell o to run
    end _text2array
    
    on _split(t, d)
        (*
            string t : source string
            string or list d : separator(s)
            return list : t splitted by d
        *)
        local astid0, tt
        try
            set {astid0, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {} & d}
            set tt to t's text items
            set AppleScript's text item delimiters to astid0
        on error errs number errn
            set AppleScript's text item delimiters to astid0
            error errs number errn
        end try
        return tt
    end _split
    
    on _printf(f, fmt, lst, opts)
        (*
            string f or 1 : POSIX path of output file or file descriptor id (1 = stdout)
            string fmt : printf format string
            list lst : list of values (if lst is one item list {x}, lst may be x)
            record opts : {_append:_append}
                boolean _append : true to append data, false to replace data; default false.
        *)
        local args
        set args to ""
        repeat with a in {fmt} & lst
            set args to args & space & (a as string)'s quoted form
        end repeat
        set opts to opts & {_append:false}
        if f's class = integer then
            do shell script "printf " & args & " >&" & f
        else
            if opts's _append then
                do shell script "printf " & args & " >> " & f's quoted form
            else
                do shell script "printf " & args & " > " & f's quoted form
            end if
        end if
    end _printf
    --END OF APPLESCRIPT 2
    

     

     

     

    Briefly tested with Numbers v2.0.5 under OS X 10.6.8.

     

    Good luck,

    H

  • by SGIII,

    SGIII SGIII Feb 28, 2016 3:14 PM in response to Hiroto
    Level 6 (10,637 points)
    Mac OS X
    Feb 28, 2016 3:14 PM in response to Hiroto

    Hiroto wrote:

     

    Hello

     

    Firstly, I don't think locale settings should affect excution of those scripts unless port id and vlan id are decimal values with locale dependent decimal separator which differs from the one initialized in Script Editor, which is not the case here.

     

     

     

    What you say seems plausible in theory, under the assumption of zero bugs and behavior like your old operating system and version of Numbers.  But there may be something going on with Script Editor, El Cap and Numbers 3 under different languages that you and I don't know about.  See this thread, where the op reports that simply changing temporarily to United States cleared up a mysterious error.

     

    SG

  • by Hiroto,

    Hiroto Hiroto Feb 29, 2016 3:10 AM in response to SGIII
    Level 5 (7,281 points)
    Feb 29, 2016 3:10 AM in response to SGIII

    Well, that's one of the reasons why I avoid scripting Numbers where processing TSV or CSV text should suffice.

     

    Anyway, those reported errors imply that the following script should fail with error -1728 (errOSACantAccess, errAENoSuchObject) in German, Brazilian or some otehr locales but English.

     

     

    tell application "Numbers"
        tell document 1
            return name
        end tell
    end tell
    

     

     

     

    If it does fail with document open in Numbers, it is an obvious bug, whether of Numbers or AppleScript, and should be reported to let Apple fix it.

     

    H

  • by Hiroto,

    Hiroto Hiroto Feb 29, 2016 3:14 PM in response to FlorianLeo
    Level 5 (7,281 points)
    Feb 29, 2016 3:14 PM in response to FlorianLeo

    Hello

     

    Regarding the error "Can't get document 1", it might be cache corruption.

     

    Try deleting the following (after Numbers application has been quitted):

     

    ~/Library/Caches/com.apple.iWork.Numbers
    

     

     

    cf.

    "Numbers got an error: Can't get document 1." number -1728 from document 1

    http://macscripter.net/viewtopic.php?id=42428

     

     

    Regards,

    H

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 29, 2016 3:21 PM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 29, 2016 3:21 PM in response to FlorianLeo

    Please email an example file I can try.  my email is in the profile of my bio

  • by FlorianLeo,

    FlorianLeo FlorianLeo Mar 2, 2016 10:18 PM in response to Hiroto
    Level 1 (9 points)
    Mac OS X
    Mar 2, 2016 10:18 PM in response to Hiroto

    Hello Hiroto

     

    I've taken your example and fiddled around a little.

    I've attached the Screenshots

    Scripting tell V1.png

    Scripting tell V2.png

    Scripting tell V3.png

    Scripting tell V4.png

    I just don't understand (yet) the difference..

     

    Cheers

    Florian

  • by Hiroto,

    Hiroto Hiroto Mar 3, 2016 6:50 AM in response to FlorianLeo
    Level 5 (7,281 points)
    Mar 3, 2016 6:50 AM in response to FlorianLeo

    Hello FlorianLeo,

     

    You're last screenshots show the scripts are working as intended. You're not getting error -1728 for unaccessible document 1.

     

    Can I presume you're no longer getting the said error when running scripts provided by others on your original document?

     

    If you're no longer getting the error, I'd think the corrupt cache has been somehow cleared by changing application's language temporarily or such.

     

    If you're still getting the error with your original document, my advice is not changing the application's language but deleting the cache file as explained earlier.

     

    Note that ~/Library is hidden by default under recent versions of OS X. You may open it by, e.g., using command + option + g shortcut in Finder and specifying ~/Library in the resulting dialogue.

     

    Good luck,

    H

Previous Page 2