Support functions

Author : Ashley G Truter
Updated: 22-Jun-2016
Purpose: This document describes miscellaneous support functions.

Patches

glob

USAGE:
    GLOB value

DESCRIPTION:
    Creates a PARSE rule matching VALUE expanding * (any characters) and ? (any one character).
    GLOB is a function value.

ARGUMENTS:
    value -- Value to expand (any-string!)

latin1-to-utf8

USAGE:
    LATIN1-TO-UTF8 binary

DESCRIPTION:
    Latin1 binary to UTF-8 string conversion.
    LATIN1-TO-UTF8 is a function value.

ARGUMENTS:
    binary (binary!)

like

USAGE:
    LIKE series search /match

DESCRIPTION:
    Finds a value in a series and returns the series at the start of it.
    LIKE is a function value.

ARGUMENTS:
    series (series!)
    search (series!)

REFINEMENTS:
    /match -- Performs comparison and returns the tail of the match.

read-string

USAGE:
    READ-STRING file /part length /lines

DESCRIPTION:
    Read string(s) from a file.
    READ-STRING is a function value.

ARGUMENTS:
    file (file!)

REFINEMENTS:
    /part -- Partial read a given number of units (source relative)
        length (integer!)
    /lines -- Convert to block of strings

Does latin1-to-utf8 conversion and handles /lines/part correctly.

remove-each

Redefined to return the head of the series (as per R2), instead of an integer.

sort-all

USAGE:
    SORT-ALL series size /reverse

DESCRIPTION:
    (undocumented)
    SORT-ALL is a function value.

ARGUMENTS:
    series (series!)
    size (integer!)

REFINEMENTS:
    /reverse -- Reverse sort order

Fix for sort/skip/all bug in R3.

unique-skip

USAGE:
    UNIQUE-SKIP set size

DESCRIPTION:
    (undocumented)
    UNIQUE-SKIP is a function value.

ARGUMENTS:
    set (block!)
    size (integer!)

Fix for unique/skip bug in R2/R3.

Informational

digit

DIGIT is a bitset of value: make bitset! #{000000000000FFC0}

alpha

ALPHA is a bitset of value: make bitset! #{00000000000000007FFFFFE07FFFFFE0}

alphanum

ALPHANUM is a bitset of value: make bitset! #{000000000000FFC07FFFFFE07FFFFFE0}

alphanums?

USAGE:
    ALPHANUMS? source

DESCRIPTION:
    Returns TRUE if source only contains alphanums.
    ALPHANUMS? is a function value.

ARGUMENTS:
    source (string! binary!)

alphas?

USAGE:
    ALPHAS? source

DESCRIPTION:
    Returns TRUE if source only contains alphas.
    ALPHAS? is a function value.

ARGUMENTS:
    source (string! binary!)

digits?

USAGE:
    DIGITS? source

DESCRIPTION:
    Returns TRUE if source not empty and only contains digits.
    DIGITS? is a function value.

ARGUMENTS:
    source (string! binary!)

binary-file?

USAGE:
    BINARY-FILE? file

DESCRIPTION:
    Returns TRUE if file is compressed, PDF or MS Office.
    BINARY-FILE? is a function value.

ARGUMENTS:
    file (file!)

Example:

>> binary-file? %test.pdf
== true

cols?

USAGE:
    COLS? data /sheet name

DESCRIPTION:
    Number of columns in a delimited file or string.
    COLS? is a function value.

ARGUMENTS:
    data (file! string!)

REFINEMENTS:
    /sheet -- Excel worksheet name (default is "Sheet 1")
        name (string! word!)

Example:

>> cols? %test.csv
== 2

fields?

USAGE:
    FIELDS? data /sheet name

DESCRIPTION:
    Column names in a delimited file or string.
    FIELDS? is a function value.

ARGUMENTS:
    data (file! string!)

REFINEMENTS:
    /sheet -- Excel worksheet name (default is "Sheet 1")
        name (string! word!)

Example:

>> fields? %test.csv
== ["Name" "Age"]

rows?

USAGE:
    ROWS? data /sheet name

DESCRIPTION:
    Number of rows in a delimited file or string.
    ROWS? is a function value.

ARGUMENTS:
    data (file! string!)

REFINEMENTS:
    /sheet -- Excel worksheet name (default is "Sheet 1")
        name (string! word!)

Example:

>> rows? %test.csv
== 3

sheets?

USAGE:
    SHEETS? file

DESCRIPTION:
    Sheet names.
    SHEETS? is a function value.

ARGUMENTS:
    file (file!)

Example:

>> sheets? %test.xlsx
== ["Sheet 1" "Sheet 2"]

spec?

USAGE:
    SPEC? fields /as-is

DESCRIPTION:
    Unique alphanumeric column words in a block.
    SPEC? is a function value.

ARGUMENTS:
    fields (block!)

REFINEMENTS:
    /as-is -- Do not strip non-alphanum or prepend with &

Example:

>> spec? ["Col A" "Col B"]
== [&ColA &ColB]

General

append-column

USAGE:
    APPEND-COLUMN block size value /dup count

DESCRIPTION:
    Append a column of values to a block.
    APPEND-COLUMN is a function value.

ARGUMENTS:
    block (block!)
    size (integer!)
    value

REFINEMENTS:
    /dup -- Duplicates the append a specified number of times
        count (integer!)

Example:

>> append-column [a 1 b 2] 2 0
== [a 1 0 b 2 0]

remove-column

USAGE:
    REMOVE-COLUMN block size index

DESCRIPTION:
    Remove a column of values from a block.
    REMOVE-COLUMN is a function value.

ARGUMENTS:
    block (block!)
    size (integer!)
    index (integer!)

Example:

>> remove-column [a 1 0 b 2 0] 3 2 
== [a 0 b 0]

average-of

USAGE:
    AVERAGE-OF block

DESCRIPTION:
    Average of values in a block.
    AVERAGE-OF is a function value.

ARGUMENTS:
    block (block!)

Example:

>> average-of [1 2]
== 1.5

flatten

USAGE:
    FLATTEN block

DESCRIPTION:
    Flatten a block.
    FLATTEN is a function value.

ARGUMENTS:
    block (block!)

Example:

>> flatten [all [max > 3]]
== [all max > 3]

max-of

USAGE:
    MAX-OF series

DESCRIPTION:
    Returns the largest value in a series.
    MAX-OF is a function value.

ARGUMENTS:
    series -- Series to search (series!)

Example:

>> max-of [1 2]
== 2

min-of

USAGE:
    MIN-OF series

DESCRIPTION:
    Returns the smallest value in a series.
    MIN-OF is a function value.

ARGUMENTS:
    series -- Series to search (series!)

Example:

>> min-of [1 2]
== 1

mixedcase

USAGE:
    MIXEDCASE string

DESCRIPTION:
    Converts string of characters to mixedcase.
    MIXEDCASE is a function value.

ARGUMENTS:
    string (string!)

Example:

>> mixedcase "JOHN CITIZEN"
== "John Citizen"

put

USAGE:
    PUT map key value

DESCRIPTION:
    Replaces the value following a key, and returns the new value.
    PUT is a function value.

ARGUMENTS:
    map (map!)
    key
    value

Example:

>> map: to-map []
== make map! [
]

>> put map 1 0    ; add
== 0
>> map
== make map! [
    1 0
]

>> put map 1 1    ; update
== 1
>> map
== make map! [
    1 1
]

>> put map 1 none ; delete
== none
>> map
== make map! [
]

sum-of

USAGE:
    SUM-OF block

DESCRIPTION:
    Sum of values in a block.
    SUM-OF is a function value.

ARGUMENTS:
    block (block!)

Example:

>> sum-of [1 2 3]
== 6

to-column-alpha

USAGE:
    TO-COLUMN-ALPHA number

DESCRIPTION:
    Convert numeric column reference into an alpha column reference.
    TO-COLUMN-ALPHA is a function value.

ARGUMENTS:
    number -- Column number between 1 and 702 (integer!)

Example:

>> to-column-alpha 1
== "A"
>> to-column-alpha 50
== "AX"

to-column-number

USAGE:
    TO-COLUMN-NUMBER alpha

DESCRIPTION:
    Convert alpha column reference into a numeric column reference.
    TO-COLUMN-NUMBER is a function value.

ARGUMENTS:
    alpha (word! string! char!)

Example:

>> to-column-number 'a
== 1
>> to-column-number 'A
== 1
>> to-column-number 'a
== 1
>> to-column-number "A"
== 1

to-rebol-date

USAGE:
    TO-REBOL-DATE date /mdy /ydm /day

DESCRIPTION:
    Converts a string date to a REBOL date.
    TO-REBOL-DATE is a function value.

ARGUMENTS:
    date (string!)

REFINEMENTS:
    /mdy -- Month/Day/Year format
    /ydm -- Year/Day/Month format
    /day -- Day precededs date

Example:

>> to-date "2016-03-01"
== 1-Mar-2016

to-string-date

USAGE:
    TO-STRING-DATE date /mdy /ydm

DESCRIPTION:
    Converts a string or REBOL date to a YYYY-MM-DD string.
    TO-STRING-DATE is a function value.

ARGUMENTS:
    date (string! date!)

REFINEMENTS:
    /mdy -- Month/Day/Year format
    /ydm -- Year/Day/Month format

Example:

>> to-string-date 1-Mar-2016
== "2016-03-01"

to-string-time

USAGE:
    TO-STRING-TIME time

DESCRIPTION:
    Converts a string or REBOL time to a HH.MM.SS string.
    TO-STRING-TIME is a function value.

ARGUMENTS:
    time (string! date! time!)

Example:

>> to-string-time 1:30 
== "01.30.00"

Basic

copy-row

USAGE:
    COPY-ROW block width row

DESCRIPTION:
    Returns a block of values at the specified row in a block.
    COPY-ROW is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    row (integer!)

Example:

>> copy-row [a 1 b 2 c 3] 2 2
== [b 2]

fetch

USAGE:
    FETCH block size key /nosort

DESCRIPTION:
    Retrieve block of values based on primary key.
    FETCH is a function value.

ARGUMENTS:
    block (block!)
    size (integer!)
    key

REFINEMENTS:
    /nosort

Example:

>> fetch [a 1 b 2 a 2] 2 'a
== [a 1 a 2]

index

USAGE:
    INDEX block width part /tight

DESCRIPTION:
    Create a rowid index on a block.
    INDEX is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    part (integer! block!)

REFINEMENTS:
    /tight -- Use ajoin if key is a block

Example:

>> index [a 1 b 2 c 3] 2 1
== make hash! [a b c]
>> index [a 1 b 2 c 3] 2 [1 2]
== make hash! ["a 1" "b 2" "c 3"]

make-map

USAGE:
    MAKE-MAP block width key value /tight /key-type key-datatype /val-type val-datatype

DESCRIPTION:
    Converts a block of values into key and value pairs.
    MAKE-MAP is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    key (integer! block!)
    value (integer!)

REFINEMENTS:
    /tight -- Use ajoin if key is a block
    /key-type
        key-datatype -- Datatype of key (datatype!)
    /val-type
        val-datatype -- Datatype of value (datatype!)

Example:

>> make-map [a 1 b 2 c 3] 2 1 2
== make hash! [a 1 b 2 c 3]
>> make-map [a "a" 1 b "b" 2 c "c" 3] 3 [1 2] 3
== make hash! ["a a" 1 "b b" 2 "c c" 3]

pick-cell

USAGE:
    PICK-CELL block width position

DESCRIPTION:
    Returns the value at the specified position in a block.
    PICK-CELL is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    position (pair!)

Example:

>> pick-cell [a 1 b 2 c 3] 2 1x2
== b

poke-cell

USAGE:
    POKE-CELL block width position value

DESCRIPTION:
    Changes a value at the given position.
    POKE-CELL is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    position (pair!)
    value

Example:

>> poke-cell blk: [a 1 b 2 c 3] 2 1x2 'x
== x
>> blk
== [a 1 x 2 c 3]

remove-row

USAGE:
    REMOVE-ROW block width row

DESCRIPTION:
    Deletes a row from a block.
    REMOVE-ROW is a function value.

ARGUMENTS:
    block (block!)
    width (integer!)
    row (integer!)

Example:

>> remove-row blk: [a 1 b 2 c 3] 2 2
== [c 3]
>> blk
== [a 1 c 3]

Advanced

load-dsv

USAGE:
    LOAD-DSV source /part columns /with delimiter /blocks

DESCRIPTION:
    Parses complex delimiter-separated values from a file or string.
    LOAD-DSV is a function value.

ARGUMENTS:
    source (file! string! binary!)

REFINEMENTS:
    /part -- Offset position(s) to retrieve
        columns (block! integer!)
    /with -- Alternate delimiter (default is comma)
        delimiter (char!)
    /blocks -- Rows as blocks

If /delimit is not specified it will default to tab if a tab is present in the first row, otherwise comma.

>> load-dsv %test.csv
== ["a" "1" "b" "2" "c" "3"]
>> load-dsv "a,1^/b,2"
== ["a" "1" "b" "2"]

/part

>> load-dsv/part %test.csv [1]
== ["a" "b" "c"]

/with

>> write/string %test.txt [a:1:b:2:c:3]
>> load-dsv/with %test.txt #":"
== ["a" "1" "b" "2" "c" "3"]

/blocks

>> load-dsv/blocks "a,1^/b,2"
== [["a" "1"] ["b" "2"]]

load-excel

USAGE:
    LOAD-EXCEL file sheet /part columns /where condition /string /distinct /hdr

DESCRIPTION:
    Loads an Excel file.
    LOAD-EXCEL is a function value.

ARGUMENTS:
    file (file!)
    sheet (string! word! integer!)

REFINEMENTS:
    /part -- Offset position(s) / columns(s) to retrieve
        columns (block! integer! string! word!)
    /where -- Expression that can reference columns as F1, F2, etc
        condition (string!)
    /string -- Return string
    /distinct
    /hdr -- First row contains columnnames not data (not compatible with /part or /where)

This function requires Microsoft Excel or Microsoft Access Database Engine 2010 Redistributable.

>> load-excel %test.xlsx 'sheet1
== ["a" "1" "b" "2" "c" "3"]

/part

>> load-excel/part %test.xlsx 1 [1 2]
== ["a" "1" "b" "2"]

/where

>> load-excel/where %test.xlsx 1 "F1 = '1'"
== ["1" "2" "3"]

merge

USAGE:
    MERGE block1 width1 block2 width2 cols keys /default

DESCRIPTION:
    Join outer block to inner block on keys.
    MERGE is a function value.

ARGUMENTS:
    block1 -- Outer block (block!)
    width1 (integer!)
    block2 -- Inner block to index (block!)
    width2 (integer!)
    cols -- Offset position(s) to retrieve in merged block (block!)
    keys -- Outer/inner join column pairs (block!)

REFINEMENTS:
    /default -- Use spaces on inner block misses

Example:

>> staff: ["Joe" 0 "Bob" 1 "Ben" 0]
>> type: [0 "Permanent" 1 "Contractor"]
>> munge/merge staff 2 type 2 [1 4] [2 1]
== ["Joe" "Permanent" "Bob" "Contractor" "Ben" "Permanent"]

The cols block specifies the column offsets in the merged block to retrieve. In the example above, both staff and type have two columns each so [1 4] will retrieve the first column of the outer block and the last column of the inner block.

The keys are pairs of outer/inner column offset positions to join on. For example, [1 2 3 4] will merge by matching the first column of the outer block with the second column of the inner block AND the third column of the outer block with the fourth column of the inner block.

read-pdf

USAGE:
    READ-PDF file /lines

DESCRIPTION:
    Reads from a PDF file.
    READ-PDF is a function value.

ARGUMENTS:
    file (file!)

REFINEMENTS:
    /lines -- Handles data as lines

This function requires pdftotext to be located in the same folder as munge.r.

>> read-pdf %test.xlsx
== "Col1 Col2^/a    1^/b    2^/c    3"

/lines

>> read-xls/lines %test.xlsx
== ["Col1 Col2" "a    1" "b    2" "c    3"]

split-dsv

USAGE:
    SPLIT-DSV source /with delimiter

DESCRIPTION:
    Parses simple delimiter-separated values from a file or string.
    SPLIT-DSV is a function value.

ARGUMENTS:
    source (file! string!)

REFINEMENTS:
    /with -- Alternate delimiter (default is tab, bar then comma)
        delimiter (char!)

Example:

>> split-dsv "a,b,c"
== ["a" "b" "c"]

sqlcmd

USAGE:
    SQLCMD server database statement /key columns /headings /raw /affected

DESCRIPTION:
    Execute a SQL Server statement.
    SQLCMD is a function value.

ARGUMENTS:
    server (string!)
    database (string!)
    statement (string!)

REFINEMENTS:
    /key -- Columns to convert to integer
        columns (integer! block!)
    /headings -- Keep column headings
    /raw -- Do not process return buffer
    /affected -- Return rows affected instead of empty block

This function calls the sqlcmd command line utility and captures output in a string buffer so no temporary files are created.

database

Example:

>> sqlcmd "server" "database" "select * from table"
== ["1" "2" "3" "4"]

/key

Coerces nominated column(s) to integer.

>> sqlcmd/key "server" "database" "select * from table" 1
== [1 "One" 2 "Two"]

/headings

>> sqlcmd/headings "server" "database" "select * from table"
== ["Column 1" "Column 2" "1" "One" "2" "Two"]

/raw

This refinement causes the return buffer (a string) to be returned without any processing.

>> sqlcmd/raw "server" "database" "select * from table"
== [{Column1^-Column2
a^-NULL

(1 rows affected)
}]

sqlite

USAGE:
    SQLITE database statement /key columns /headings /raw

DESCRIPTION:
    Execute a SQLite statement.
    SQLITE is a function value.

ARGUMENTS:
    database (file!)
    statement (string!)

REFINEMENTS:
    /key -- Columns to convert to integer
        columns (integer! block!)
    /headings -- Keep column headings
    /raw -- Do not process return buffer

This function calls the sqlite command line utility and captures output in a string buffer so no temporary files are created.

database

Example:

>> sqlite %test.db "select * from table"
== ["1" "2" "3" "4"]

/key

Coerces nominated column(s) to integer.

>> sqlite %test.db "select * from table" 1
== [1 "One" 2 "Two"]

/headings

>> sqlite/headings %test.db "select * from table"
== ["Column 1" "Column 2" "1" "One" "2" "Two"]

/raw

This refinement causes the return buffer (a string) to be returned without any processing.

unarchive

USAGE:
    UNARCHIVE source /info

DESCRIPTION:
    Decompress archive (only works with compression methods 'store and 'deflate).
    UNARCHIVE is a function value.

ARGUMENTS:
    source (file! url!)

REFINEMENTS:
    /info -- File names only

unzip

USAGE:
    UNZIP file /folder /only

DESCRIPTION:
    Uncompress file(s).
    UNZIP is a function value.

ARGUMENTS:
    file (file!)

REFINEMENTS:
    /folder -- Create ZIP folder
    /only -- Do not create sub-folders

This function requires c:\Program Files\7-Zip\7z.exe.

Example:

>> unzip %test.zip
== [%test.csv %test.txt]

write-excel

USAGE:
    WRITE-EXCEL file data /filter

DESCRIPTION:
    Write block(s) of values to an Excel file.
    WRITE-EXCEL is a function value.

ARGUMENTS:
    file (file!)
    data -- Name [string!] Data [block!] Width [integer! block!] records (block!)

REFINEMENTS:
    /filter -- Add auto filter

This function requires c:\Program Files\7-Zip\7z.exe.

Example:

>> write-excel %test.xlsx [
    "Sheet 1" ["Col1" "Col2" A 1 B 2] 2
    "Sheet 2" ["Name" "Age" "John Citizen" 30] [20 10]
]

Formula:

>> write-excel/filter %test.xlsx [
    "Test" ["Response" "Count" "Yes" 10 "No" 5 "" "=SUM(B2:B3)"] [20 10]
]