Thursday, October 07, 2010

"Text to Columns"-like functionality in iWork 09

It took me a few hours to work this out today, so I thought it was worth sharing in case other people are trying to do the same thing.

The following is AppleScript code that will take the current selection (currently only a single column range) in Numbers and fill the cells to the right of the selection with the contents of the selection split on commas.

on theSplit(theString, theDelimiter)
 -- save delimiters to restore old settings
 set oldDelimiters to AppleScript's text item delimiters
 -- set delimiters to delimiter to be used
 set AppleScript's text item delimiters to theDelimiter
 -- create the array
  set theArray to every text item of theString
  -- restore the old setting
  set AppleScript's text item delimiters to oldDelimiters
  -- return the result
  return theArray
 end try
 return {}
end theSplit

tell application "Numbers"
 tell document 1
  set {current_sheet_index, current_table_index} to {0, 0}
  repeat with i from 1 to the count of sheets
   tell sheet i
    set x to the count of tables
    if x > 0 then
     repeat with j from 1 to x
       set theRange to (selection range of table j) as text
      on error errMsg number errNum
       set {current_sheet_index, current_table_index} to {i, j}
       exit repeat
      end try
     end repeat --table
    end if
   end tell
   if current_sheet_index > 0 then exit repeat
  end repeat -- sheet
  if the current_sheet_index > 0 then
   tell sheet current_sheet_index to tell table current_table_index
    set the range_values to the value of cells of the selection range
    set the current_column to the address of column of the selection range
    set the current_row to the address of row of the selection range
    set currentRow to first item of current_row
    repeat with value in range_values
     set valsArray to my theSplit(value, ",")
     set currentCount to current_column
     repeat with val in valsArray
      set currentCount to currentCount + 1
      set currentCell to cell
      set value of cell currentCount of row currentRow to val
     end repeat
     set currentRow to currentRow + 1
    end repeat
   end tell
  end if
 end tell
end tell

To use it, select the cells containing the comma-delimited values you want to split, then run the script. It's not fast, and there's a few glaring omissions (mainly that you'll need to already have enough columns in the table to fit the new data), but it's going to make my life much easier.

And if you're wondering why I don't just save the values as a CSV and then open that file, this was actually written so I could paste the output from MySQL queries into a spreadsheet, then split the values out for analysis. By replacing theSplit(value, ",") with theSplit(value, "|") it will work with text like this:

| count(*) | sum(value) |
|        2 |         12 |
|     1885 |       9127 |
|      355 |        962 |
|       12 |         21 |
|        1 |          1 |
|        4 |          5 |
|        1 |          1 |
|      768 |        973 |
|        2 |          2 |
|     6604 |      13093 |
|       41 |         57 |

1 comment: said...
This comment has been removed by the author.