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