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

Page 1 Next
  • by Wayne Contello,Helpful

    Wayne Contello Wayne Contello Feb 27, 2016 11:07 AM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 27, 2016 11:07 AM in response to FlorianLeo

    select the fist column of the data, then run this script by copying from the forum and pasting into the script editor.  Files will appear on the desktop

     

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      set selectionRange to column 1 of selection range

      set cnt to 0

      set destRange to range

     

      set currentList to {}

      -- get the list of switches

      repeat with aCell in selectionRange's cells

      -- tell aCell to set its value to cnt

      set switch to the value of aCell

      if ((cnt > 0) and (switch is not missing value) and (currentList does not contain switch)) then

     

     

      set currentList to currentList & switch

      end if

     

      set cnt to cnt + 1

      end repeat

     

      -- display dialog "The List of items is: " & currentList & " the list has " & (count of currentList)

     

      repeat with aswitch in currentList

      set textOut to "# Script generated with Applescript for Switch " & aswitch & "

    # on " & (current date) & "

    !

    conf t

    "

      set cnt to 0

     

      repeat with aCell in selectionRange's cells

      -- tell aCell to set its value to cnt

      set switch to the value of aCell

      -- display dialog "aswitch is [" & aswitch & "] and switch is [" & switch & "]"

      if (aswitch contains switch) then

     

     

      set cellCol to address of column of first cell of aCell

      set cellRow to address of row of first cell of aCell

     

      set aport to the (value of cell cellRow of column (cellCol + 1)) as integer

      set vlan to the (value of cell cellRow of column (cellCol + 2)) as integer

      set desc to the (value of cell cellRow of column (cellCol + 3))

      set textOut to textOut & "

    !

    interface gi 1/0/" & aport & "

    switchport mode access

    switchport access vlan " & vlan & "

    description LINK TO " & desc & "

    "

      else

      -- display dialog "Did not match: [" & aswitch & "] and [" & switch & "]"

      end if

     

      set cnt to cnt + 1

      end repeat

     

      set myFile to open for access (path to desktop as text) & aswitch & "_output.txt" with write permission

      write textOut to myFile

      close access myFile

      end repeat

     

      end tell

      end tell

    end tell

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 26, 2016 5:29 PM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 26, 2016 5:29 PM in response to FlorianLeo
  • by FlorianLeo,

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

    Hello Wayne

     

    thank you very much for this script! How did you do it so fast?

     

    RIght now I'm trying to think through it - unbelievable - I am baffled!

     

    I will let you know if it worked and (of course) rate your answer!

     

    till later

    Florian

     

    P.S. I have to learn that scripting-stuff a lot better! Is there lecture you recommend for someone who isn't completely new to that?

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 27, 2016 6:16 AM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 27, 2016 6:16 AM in response to FlorianLeo

    I've learned a lot by watching SGIII in these forums.  Also google can help you by searching Applescript plus what ever specific thing you want.  these a lot in the script I posted.  It started from a previous script which I am certain started from and SG example.

     

    Post questions here or in the developer forum

     

    BTW... use the link I provided, as I fixed a couple small things since I posted my first answer

  • by SGIII,Helpful

    SGIII SGIII Feb 27, 2016 11:07 AM in response to FlorianLeo
    Level 6 (10,637 points)
    Mac OS X
    Feb 27, 2016 11:07 AM in response to FlorianLeo

    Here's a script that should do the same thing as Wayne's.  It's more modular, so it may be a littler easier to tweak if you need to do that.

     

    To use:

     

    In Finder create a folder to receive the configuration scripts if you don't have one already.

    1. Copy-paste into Script Editor (in Applications > Utilities).
    2. Click anywhere in the table in Numbers that contains the data.
    3. Click the triangle 'run' button.
    4. Respond to the prompt to choose a folder.

     

    Note that this will overwrite any configuration scripts you already have in your chosen destination folder so backup first if you want to keep existing ones you have there.

     

    SG

     

    -- 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}

     

    set myFolder to choose folder with prompt "Choose folder to place configuration scripts"

     

    -- places values from Numbers table in AppleScript list of lists (2D array)

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range) to ¬

      set vv to rows's cells's value

      end tell

    end tell

     

    -- removes any "blank rows"

    set vv2 to {}

    repeat with i from 2 to count vv -- the 2 assumes 1 header row

      if vv's item i's item nameCol is not missing value then ¬

      set vv2 to vv2 & {vv's item i}

    end repeat

     

    -- initializes variables

    set {prevName, theTxt} to {"", ""}

    set totalCount to count vv2

     

    repeat with i from 1 to totalCount

     

      -- read a row's values

      tell vv2's item i

      set thisName to item nameCol

      set thePort to item portCol as integer

      set theVLAN to item vlanCol as integer

      set theDesc to item descCol

      end tell

     

      -- if not a new switch then append row block

      if thisName is prevName then ¬

      set theTxt to theTxt ¬

      & makeRowBlock(thePort, theVLAN, theDesc)

     

      -- if new switch and no text then make header and append row block

      if thisName is not prevName and theTxt is "" then

      set theTxt to makeHeader(thisName) ¬

      & makeRowBlock(thePort, theVLAN, theDesc)

     

      else -- if new switch write existing text to file, make header and append row block

      if thisName is not prevName and theTxt is not "" then

      writeFile(myFolder, theTxt, prevName)

      set theTxt to makeHeader(thisName) ¬

      & makeRowBlock(thePort, theVLAN, theDesc)

      end if

      end if

     

      -- if last row write the file

      if i = totalCount then writeFile(myFolder, theTxt, thisName)

     

      set prevName to thisName

     

    end repeat

     

    to makeHeader(switchName)

      set s to "# Script generated by AppleScript for Switch " & switchName & return

      set s to s & "# on " & (current date) & return

      set s to s & "!" & return & "conf t" & return

    end makeHeader

     

    to makeRowBlock(aPort, aVlan, aDescr)

      set s to "!" & return

      set s to s & "interface gi 1/0/" & aPort & return

      set s to s & " switchport mode access" & return

      set s to s & " switchport access vlan " & aVlan & return

      set s to s & " description LINK TO " & aDescr & return

    end makeRowBlock

     

    to writeFile(myFolder, theText, thisSwitch)

      set f to open for access (myFolder as text) & ¬

      "Accessport Configuration Script " & thisSwitch & ".txt" with write permission

      write theText to f

      close access f

    end writeFile

  • by SGIII,

    SGIII SGIII Feb 27, 2016 8:19 AM in response to FlorianLeo
    Level 6 (10,637 points)
    Mac OS X
    Feb 27, 2016 8:19 AM in response to FlorianLeo

    P.S. I have to learn that scripting-stuff a lot better! Is there lecture you recommend for someone who isn't completely new to that?

     

    If you're interested in learning about scripting you may find these two sites particularly useful:

     

    http://macosxautomation.com/

     

    http://macscripter.net/

     

    And of course this one if you really need to get into it:

     

    https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/App leScriptLangGuide/introduction/ASLR_intr…

     

    SG

  • by FlorianLeo,

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

    Hello Wayne

     

    I've tried your Script from this post - there's an error, but it is pointing me absolutely in the right direction. I need to figure out the if/when/why/what - I need to understand it.

     

    Out of curiosity - what if I'd like to use only parts of column 1? I've understood your script in the way that it

    • takes the whole 1st column as input -> to derive
    • the number of files to create
    • the names of the files
    • the first line of text in the file itself
    • and then fill the file with more text -> with values which are derived from other cells through counting forward within the same row the name came from in the first place,

    am I right?

     

    Of course I can make a copy of the original table, delete the rows I don't need and let the script do it's work. I don't fully understand the code, yet - it is possible to use a selection of column 1, right? Lets say the table has 400 rows, and you're good with the first 250 together with the last 100. 251 through 300 experience a change, and you want only the .txt-file(s) for these 50 to be newly created.

     

    I'll try the one via Dropbox as well.

     

    Thank you once again for your help! I appreciate it!

     

    Cheers

    Florian

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 27, 2016 11:37 AM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 27, 2016 11:37 AM in response to FlorianLeo

    Out of curiosity - what if I'd like to use only parts of column 1? I've understood your script in the way that it

    • takes the whole 1st column as input -> to derive
    • the number of files to create
    • the names of the files
    • the first line of text in the file itself
    • and then fill the file with more text -> with values which are derived from other cells through counting forward within the same row the name came from in the first place,

    am I right?

    YES

     

    Of course I can make a copy of the original table, delete the rows I don't need and let the script do it's work. I don't fully understand the code, yet - it is possible to use a selection of column 1, right? Lets say the table has 400 rows, and you're good with the first 250 together with the last 100. 251 through 300 experience a change, and you want only the .txt-file(s) for these 50 to be newly created.

    You can pretty much make the script to what ever you want.  All those "what-ever-you-want"s add to the architecture of the script.  Knowing those ahead of time helps in properly designing the script.

  • by FlorianLeo,

    FlorianLeo FlorianLeo Feb 27, 2016 11:58 AM in response to Wayne Contello
    Level 1 (9 points)
    Mac OS X
    Feb 27, 2016 11:58 AM in response to Wayne Contello

    Hello Wayne

     

    I understand -> I have to learn, learn, learn

     

    I've tried the one via Dropbox as well. It has the same error - I believe in line 4? What am I missing? I doing something wrong, right? I highlighted column 1 and started the Script as provided.

     

    I've created a sample-table that looks much like the "messed" up ones I usually get. I know I have to "clean" certain columns to extract only needed values/clean them from unnecessary blanks/spaces -> that's not a problem at all. Is there an issue with

    • the name of the numbers-file?
    • the name of the sheet in that file?
    • the name of the table in that sheet?

    Did I get it right with the hierarchy? Top: File-name, 1. beneath: sheet-name, 2. beneath: table-name? That is critical? Is it critical for the script to work?

     

    If you don't mind - I've made a Screenshot from the result.

    Bildschirmfoto 2016-02-27 um 20.41.32.png

  • by Drew Reece,

    Drew Reece Drew Reece Feb 27, 2016 12:02 PM in response to Wayne Contello
    Level 5 (7,485 points)
    Notebooks
    Feb 27, 2016 12:02 PM in response to Wayne Contello

    Wayne Contello,

    This might help you post Applescripts here without the dreadful negative padding…

    Bookmarklet to fix AppleScript pasting on discussions.apple.com

     

    So this…

    tell application "Finder"

      beep

    end tell

     

    looks like…

    tell application "Finder"

      beep

    end tell

  • by FlorianLeo,

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

    Hello SGIII

     

    I am obviously doing something wrong - since I get the same error as with the script provided by Wayne.

     

    Thank you for your effort as well! Nice touch is the dialog that asks for the location to safe the text-files to - wow!

     

    Thank you for your links to learning-materials. I've actually bought a beginners book and started reading through it. I have a basic understanding, from C++ I learned once upon a time but never needed it - it's just to get the terms/functions straight - in my head.

     

    Cheers

    Florian

  • by FlorianLeo,

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

    Hello SGIII

     

    thank you for your hints!

     

    The last one is definitely the "most containing" one - yet, terrible to read.

     

    I am little like "monkey see, monkey do" - better is learning by doing, I need to play. I believe this method to be the best to get into something. With my post here I do know that I've set the bar to high for me, too. Thus - I am graceful that you guys walk me through it.

     

    Cheers

    Florian

  • by SGIII,

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

    FlorianLeo wrote:

     

    Hello SGIII

     

    I am obviously doing something wrong - since I get the same error as with the script provided by Wayne.

     

    That might be something to do with your localization.  In System Preferences > Language & Region you might try temporarily switching to United States to see if you still get the error.  Another possibility is you skipped step 2 of my instructions. You need to click in the table before running the script. 

     

    Here are the results it gives here with your example table:

     

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

     

    I forget to mention that the script assumes the table is sorted by switch name, as in your example.

     

    SG

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 27, 2016 2:08 PM in response to FlorianLeo
    Level 6 (19,017 points)
    iWork
    Feb 27, 2016 2:08 PM in response to FlorianLeo

    looks to me like the columns in the data are different than the data you originally posted.

     

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

     

    I was expecting:

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

     

    If they are different, then change the column offset in the lines:

    set aport to the (value of cell cellRow of column (cellCol + 1)) as integer

    set vlan to the (value of cell cellRow of column (cellCol + 2)) as integer

    set desc to the (value of cell cellRow of column (cellCol + 3))

     

    specifically change the "1" which offset from the first column

    (cellCol + 1)


    or the "2" or the "3"



Page 1 Next