Author : Ashley G Truter Updated: 22-Jun-2016 Purpose: This document describes miscellaneous support functions.
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!)
USAGE:
LATIN1-TO-UTF8 binary
DESCRIPTION:
Latin1 binary to UTF-8 string conversion.
LATIN1-TO-UTF8 is a function value.
ARGUMENTS:
binary (binary!)
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.
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.
Redefined to return the head of the series (as per R2), instead of an integer.
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.
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.
DIGIT is a bitset of value: make bitset! #{000000000000FFC0}
ALPHA is a bitset of value: make bitset! #{00000000000000007FFFFFE07FFFFFE0}
ALPHANUM is a bitset of value: make bitset! #{000000000000FFC07FFFFFE07FFFFFE0}
USAGE:
ALPHANUMS? source
DESCRIPTION:
Returns TRUE if source only contains alphanums.
ALPHANUMS? is a function value.
ARGUMENTS:
source (string! binary!)
USAGE:
ALPHAS? source
DESCRIPTION:
Returns TRUE if source only contains alphas.
ALPHAS? is a function value.
ARGUMENTS:
source (string! binary!)
USAGE:
DIGITS? source
DESCRIPTION:
Returns TRUE if source not empty and only contains digits.
DIGITS? is a function value.
ARGUMENTS:
source (string! binary!)
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
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
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"]
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
USAGE:
SHEETS? file
DESCRIPTION:
Sheet names.
SHEETS? is a function value.
ARGUMENTS:
file (file!)
Example:
>> sheets? %test.xlsx == ["Sheet 1" "Sheet 2"]
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]
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]
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]
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
USAGE:
FLATTEN block
DESCRIPTION:
Flatten a block.
FLATTEN is a function value.
ARGUMENTS:
block (block!)
Example:
>> flatten [all [max > 3]] == [all max > 3]
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
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
USAGE:
MIXEDCASE string
DESCRIPTION:
Converts string of characters to mixedcase.
MIXEDCASE is a function value.
ARGUMENTS:
string (string!)
Example:
>> mixedcase "JOHN CITIZEN" == "John Citizen"
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! [
]
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
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"
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
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
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"
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"
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]
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]
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"]
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]
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
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]
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]
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"]
>> load-dsv/part %test.csv [1] == ["a" "b" "c"]
>> write/string %test.txt [a:1:b:2:c:3] >> load-dsv/with %test.txt #":" == ["a" "1" "b" "2" "c" "3"]
>> load-dsv/blocks "a,1^/b,2" == [["a" "1"] ["b" "2"]]
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"]
>> load-excel/part %test.xlsx 1 [1 2] == ["a" "1" "b" "2"]
>> load-excel/where %test.xlsx 1 "F1 = '1'" == ["1" "2" "3"]
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.
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"
>> read-xls/lines %test.xlsx == ["Col1 Col2" "a 1" "b 2" "c 3"]
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"]
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.
Example:
>> sqlcmd "server" "database" "select * from table" == ["1" "2" "3" "4"]
Coerces nominated column(s) to integer.
>> sqlcmd/key "server" "database" "select * from table" 1 == [1 "One" 2 "Two"]
>> sqlcmd/headings "server" "database" "select * from table" == ["Column 1" "Column 2" "1" "One" "2" "Two"]
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)
}]
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.
Example:
>> sqlite %test.db "select * from table" == ["1" "2" "3" "4"]
Coerces nominated column(s) to integer.
>> sqlite %test.db "select * from table" 1 == [1 "One" 2 "Two"]
>> sqlite/headings %test.db "select * from table" == ["Column 1" "Column 2" "1" "One" "2" "Two"]
This refinement causes the return buffer (a string) to be returned without any processing.
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
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]
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]
]