# Munge A set of functions that enable you to get blocks of tabular (row and column) data from a variety of sources, manipulate the data in various ways, then store the result as a Rebol/Red block or even save back as a CSV, tab-delimited or Excel file! **Red does not currently support `compress` and `decompress` so `archive`, `load-xml` and `unarchive` will not work on Red.** ## Getting started Download [munge3.r](http://dobeash.com/files/munge3.r), then from a Rebol or Red console: <pre> >> do %munge3.r >> ctx-munge/list [1 2 3] == [ 1 2 3 ] </pre> Alternatively, you can selectively export functions to the global context: <pre> >> ctx-munge/export [list munge] </pre> Or export all functions with: <pre> >> ctx-munge/export bind words-of ctx-munge 'self </pre> ## Data Structure Rows are represented as blocks of data within a "table" block. <pre> >> load-dsv "a,1^/b,2" == [["a" "1"] ["b" "2"]] </pre> ## Settings ### Working in the console By default `trace` and `list` are enabled. They can be disabled with `settings/console: false`. <pre> >> load-xml %abc.xlsx 00:00.000 0 Call load-xml 00:00.001 0 Call unarchive on sheet1.xml 00:00.001 0 Call read-binary on abc.xlsx 00:00.053 2 Exit read-binary 00:00.277 33 Exit unarchive 00:00.280 35 Call unarchive on sharedStrings.xml 00:00.280 35 Call read-binary on abc.xlsx 00:00.290 30 Exit read-binary 00:00.335 35 Exit unarchive 00:00.335 35 Call read-string 00:00.412 40 Exit read-string 00:00.412 40 Call parse 00:00.512 38 Exit parse 00:00.513 38 Call cols? 00:00.555 38 Exit cols? 00:00.556 38 Call read-string 00:00.860 52 Exit read-string 00:00.861 52 Call parse 00:03.064 77 Exit parse 00:03.097 77 Exit load-xml == [ ["Cardholder Name" "Lan ID" "Entry Clock" "Entry Card" "Entry Date/Time"] ["John Citizen" "jcitizen" "L10 West Foyer Entry Door" "632306" "2/10/2018 10:10:16 AM"] ["John Citizen" "jcitizen" "L10 West Foyer Entry Door" "632306" "2/10/2018 12:55:17 PM"] ["John Citizen" "jcitizen" "L10 East Foyer Entry Door" "632306" "2/10/2018 2:26:28 PM"] ["John Citizen" "jcitizen" "L3 East Foyer Entry Door" "632306" "2/10/2018 2:59:30 PM"] ["John Citizen" "jcitizen" "L10 West ... >> </pre> `trace` mode shows three columns: 1. **mm:ss:mmm** since start of initial function call. 2. **MB** used since start of initial function call. 3. **Function** called/exited (indentation indicates nesting level). `list` mode is the equivalent of `new-line/all block true` ... it ensures each row within a block is displayed on a new line. ### Whitespace handling By default `as-is` is enabled. It can be disabled with `settings/as-is: false`. <pre> >> load-dsv " a b " == [ ["a b"] ] >> settings/as-is: false >> load-dsv " a b " == [ ["a b"] ] </pre> Leading and trailing whitespace is always trimmed, but `as-is` preserves embedded whitespace. ## Reading files ### Delimiter Seperated Values (DSV) <pre> >> load-dsv %file.csv >> load-dsv/part %file.csv 1 >> load-dsv/part %file.csv [1 2] >> load-dsv/where %file.csv [row/1 = 1] </pre> ### Excel <pre> >> load-xml %file.xlsx >> load-xml/sheet %file.xlsx 'Sheet1 >> load-xml/part %file.xlsx [1 2] >> load-xml/where %file.xlsx [row/1 = 1] </pre> `load-xml` can return different results from `oledb`. Here is some code to check for differences: <pre> >> a: oledb file "SELECT * FROM Sheet1" >> b: load-xml file >> repeat i length? a [all [a/:i <> b/:i print [i difference form a/:i form b/:i] halt]] </pre> Common differences include: * Accented characters (oledb doesn't handle them). * Row length. * Dates (`oledb` returns the styled date, `load-xml` may return a 5-digit date). * Times (`oledb` returns the styled time, `load-xml` may return a decimal time). Note that `to-string-date` and `to-string-time` handle Excel raw date and time formats. ## Writing files ### DSV <pre> >> write-dsv %file.csv block >> write-dsv %file.txt block </pre> ### Excel <pre> >> write-excel %file.xlsx ["Sheet1" [[a b][1 2]] [5 10]] >> write-excel/filter %file.xlsx ["Sheet1" [[a b][1 2]] [5 10]] </pre> ## Querying databases ### OLEDB <pre> >> oledb %file.xlsx "SELECT * FROM Sheet1" >> oledb %file.xlsx "SELECT * FROM Sheet2" >> oledb %file.xlsx "SELECT F1, F2 FROM Sheet1" >> oledb %file.xlsx "SELECT * FROM Sheet1 WHERE F1 = 1" </pre> This only works on Windows and requires the [Microsoft Access Database Engine 2010 Redistributable](https://www.microsoft.com/en-au/download/details.aspx?id=13255) to first be installed. ### SQL Server <pre> >> sqlcmd sn db "SELECT * FROM TABLE" >> sqlcmd/headings sn db "SELECT * FROM TABLE" </pre> This will only work on Windows and requires the SQLCMD utility to be installed. ### SQLite <pre> >> sqlite %file.db "SELECT * FROM TABLE" >> sqlite/headings %file.db "SELECT * FROM TABLE" </pre> This requires [sqlite](http://sqlite.org/download.html) to be in the same folder as `munge3.r`. ## Munging blocks The main function, `munge`, is typically used to manipulate blocks of data retrieved from files and databases. ### /where The `/where` refinement lets you specify a block of Rebol conditions (wrapped within an `all` block) that can reference row values as `row/1`, `row/2`, etc). <pre> >> munge/where data [row/1 = 1] >> munge/delete/where data [row/1 > 1 row/1 < 10] >> munge/update/where data [1 row/2] [any [row/1 < 5 row/1 > 10]] </pre> Alternatively, if the data is sorted a key value may be specified. The key value does not need to be unique, but it must be the first value of each row retrieved. <pre> >> sort data >> munge/where data 1 >> munge/delete/where data 1 >> munge/update/where data [1 row/2] 1 </pre> This initiates a [binary search](https://en.wikipedia.org/wiki/Binary_search_algorithm) that can be thousands of times faster than a block predicate. # ctx-munge ##archive <pre>USAGE: ARCHIVE source DESCRIPTION: Compress block of file and data pairs. ARCHIVE is a function! value. ARGUMENTS: source [series!] </pre> ##call-out <pre>USAGE: CALL-OUT cmd DESCRIPTION: Call OS command returning STDOUT. CALL-OUT is a function! value. ARGUMENTS: cmd [string!] </pre> ##check <pre>USAGE: CHECK data DESCRIPTION: Verify data structure. CHECK is a function! value. ARGUMENTS: data [block!] </pre> ##cols? <pre>USAGE: COLS? data DESCRIPTION: Number of columns in a delimited file or string. COLS? is a function! value. ARGUMENTS: data [file! url! binary! string!] REFINEMENTS: /with => delimiter [char!] /sheet => number [integer!] </pre> ##crc32 <pre>USAGE: CRC32 data DESCRIPTION: Returns a CRC32 checksum. CRC32 is a function! value. ARGUMENTS: data [binary! string!] </pre> ##delimiter? <pre>USAGE: DELIMITER? data DESCRIPTION: Probable delimiter, with priority given to comma, tab, bar, tilde then semi-colon. DELIMITER? is a function! value. ARGUMENTS: data [file! url! string!] </pre> ##dezero <pre>USAGE: DEZERO string DESCRIPTION: Remove leading zeroes from string. DEZERO is a function! value. ARGUMENTS: string [string!] </pre> ##digit <pre>DIGIT is a bitset! value: make bitset! #{000000000000FFC0} </pre> ##digits? <pre>USAGE: DIGITS? data DESCRIPTION: Returns TRUE if data not empty and only contains digits. DIGITS? is a function! value. ARGUMENTS: data [string! binary!] </pre> ##distinct <pre>USAGE: DISTINCT data DESCRIPTION: Remove duplicate and empty rows. DISTINCT is a function! value. ARGUMENTS: data [block!] </pre> ##enblock <pre>USAGE: ENBLOCK data cols DESCRIPTION: Convert a block of values to a block of row blocks. ENBLOCK is a function! value. ARGUMENTS: data [block!] cols [integer!] </pre> ##enzero <pre>USAGE: ENZERO string length DESCRIPTION: Add leading zeroes to a string. ENZERO is a function! value. ARGUMENTS: string [string!] length [integer!] </pre> ##excel? <pre>USAGE: EXCEL? data DESCRIPTION: Returns TRUE if file is Excel or worksheet is XML. EXCEL? is a function! value. ARGUMENTS: data [file! url! binary! string!] </pre> ##export <pre>USAGE: EXPORT words DESCRIPTION: Export words to global context. EXPORT is a function! value. ARGUMENTS: words [block!] "Words to export." </pre> ##fields? <pre>USAGE: FIELDS? data DESCRIPTION: Column names in a delimited file. FIELDS? is a function! value. ARGUMENTS: data [file! url! binary! string!] REFINEMENTS: /with => delimiter [char!] /sheet => number [integer!] </pre> ##first-line <pre>USAGE: FIRST-LINE data DESCRIPTION: Returns the first non-empty line of a file. FIRST-LINE is a function! value. ARGUMENTS: data [file! url! string!] </pre> ##flatten <pre>USAGE: FLATTEN data DESCRIPTION: Flatten nested block(s). FLATTEN is a function! value. ARGUMENTS: data [block!] </pre> ##latin1-to-utf8 <pre>USAGE: LATIN1-TO-UTF8 data DESCRIPTION: Latin1 binary to UTF-8 string conversion. LATIN1-TO-UTF8 is a function! value. ARGUMENTS: data [binary!] </pre> ##letter <pre>LETTER is a bitset! value: make bitset! #{00000000000000007FFFFFE07FFFFFE0} </pre> ##letters? <pre>USAGE: LETTERS? data DESCRIPTION: Returns TRUE if data only contains letters. LETTERS? is a function! value. ARGUMENTS: data [string! binary!] </pre> ##like <pre>USAGE: LIKE series value DESCRIPTION: Finds a value in a series, expanding * (any characters) and ? (any one character), and returns TRUE if found. LIKE is a function! value. ARGUMENTS: series [any-string!] "Series to search." value [any-string!] "Value to find." </pre> ##list <pre>USAGE: LIST data DESCRIPTION: Uses settings to optionally trim strings and set the new-line marker. LIST is a function! value. ARGUMENTS: data [block!] </pre> ##load-dsv <pre>USAGE: LOAD-DSV source DESCRIPTION: Parses delimiter-separated values into row blocks. LOAD-DSV is a function! value. ARGUMENTS: source [file! url! binary! string!] REFINEMENTS: /part => Offset position(s) to retrieve. columns [block! integer! word!] /where => Expression that can reference columns as row/1, row/2, etc. condition [block!] /with => Alternate delimiter (default is tab, bar then comma). delimiter [char!] /ignore => Ignore truncated row errors. /csv => Parse as CSV even though not comma-delimited. </pre> ##load-fixed <pre>USAGE: LOAD-FIXED file DESCRIPTION: Loads fixed-width values from a file. LOAD-FIXED is a function! value. ARGUMENTS: file [file! url!] REFINEMENTS: /spec => widths [block!] /part => columns [integer! block!] </pre> ##load-xml <pre>USAGE: LOAD-XML file DESCRIPTION: Loads an Office XML sheet. LOAD-XML is a function! value. ARGUMENTS: file [file!] REFINEMENTS: /part => Offset position(s) to retrieve. columns [block! integer! word!] /where => Expression that can reference columns as row/1, row/2, etc. condition [block!] /sheet => number [integer!] /fields => </pre> ##max-of <pre>USAGE: MAX-OF series DESCRIPTION: Returns the largest value in a series. MAX-OF is a function! value. ARGUMENTS: series [series!] "Series to search." </pre> ##merge <pre>USAGE: MERGE outer key1 inner key2 columns DESCRIPTION: Join outer block to inner block on primary key. MERGE is a function! value. ARGUMENTS: outer [block!] "Outer block." key1 [integer!] inner [block!] "Inner block to index." key2 [integer!] columns [block!] "Offset position(s) to retrieve in merged block." REFINEMENTS: /default => Use none on inner block misses. </pre> ##min-of <pre>USAGE: MIN-OF series DESCRIPTION: Returns the smallest value in a series. MIN-OF is a function! value. ARGUMENTS: series [series!] "Series to search." </pre> ##mixedcase <pre>USAGE: MIXEDCASE string DESCRIPTION: Converts string of characters to mixedcase. MIXEDCASE is a function! value. ARGUMENTS: string [string!] </pre> ##munge <pre>USAGE: MUNGE data DESCRIPTION: Load and/or manipulate a block of tabular (column and row) values. MUNGE is a function! value. ARGUMENTS: data [block!] REFINEMENTS: /delete => Delete matching rows (returns original block). clause /part => Offset position(s) and/or values to retrieve. columns [block! integer! word! none!] /where => Expression that can reference columns as row/1, row/2, etc. condition /group => One of count, max, min or sum. having [word! block!] {Word or expression that can reference the initial result set column as count, max, etc.} /spec => Return coulmns and condition with field substitutions. </pre> ##oledb <pre>OLEDB is a none! value: none </pre> ##parse-series <pre>USAGE: PARSE-SERIES series rules DESCRIPTION: Parses a series according to grammar rules. PARSE-SERIES is a function! value. ARGUMENTS: series [series!] rules [block!] </pre> ##penult <pre>USAGE: PENULT string DESCRIPTION: Returns the second last value of a series. PENULT is a function! value. ARGUMENTS: string [series!] </pre> ##read-binary <pre>USAGE: READ-BINARY source DESCRIPTION: Read bytes from a file. READ-BINARY is a function! value. ARGUMENTS: source [file! url!] REFINEMENTS: /part => Reads a specified number of bytes. length [integer!] </pre> ##read-string <pre>USAGE: READ-STRING source DESCRIPTION: Read string from a text file. READ-STRING is a function! value. ARGUMENTS: source [file! url! binary!] </pre> ##replace-deep <pre>USAGE: REPLACE-DEEP data map DESCRIPTION: Replaces all occurences of search values with new values in a block or nested block. REPLACE-DEEP is a function! value. ARGUMENTS: data [block!] "Block to replace within (modified)." map [map! block!] "Map of values to replace." </pre> ##rows? <pre>USAGE: ROWS? data DESCRIPTION: Number of rows in a delimited file or string. ROWS? is a function! value. ARGUMENTS: data [file! url! binary! string!] REFINEMENTS: /sheet => number [integer!] </pre> ##second-last <pre>USAGE: SECOND-LAST string DESCRIPTION: Returns the second last value of a series. SECOND-LAST is a function! value. ARGUMENTS: string [series!] </pre> ##settings <pre>SETTINGS is an object! with the following words and values: build word! red os word! macOS target integer! 32 stack block! length: 0 [] start-time none! none start-used none! none called function! [name [word! none!] /file path [file! url! b... exited function! [] error function! [message [string!]] as-is logic! true console logic! true trace logic! true </pre> ##sheets? <pre>USAGE: SHEETS? file DESCRIPTION: Excel sheet names. SHEETS? is a function! value. ARGUMENTS: file [file! url!] </pre> ##sqlcmd <pre>SQLCMD is a none! value: none </pre> ##sqlite <pre>USAGE: SQLITE database statement DESCRIPTION: Execute a SQLite statement. SQLITE is a function! value. ARGUMENTS: database [file! url!] statement [string!] </pre> ##to-column-alpha <pre>USAGE: TO-COLUMN-ALPHA number DESCRIPTION: Convert numeric column reference to an alpha column reference. TO-COLUMN-ALPHA is a function! value. ARGUMENTS: number [integer!] "Column number between 1 and 702." </pre> ##to-column-number <pre>USAGE: TO-COLUMN-NUMBER alpha DESCRIPTION: Convert alpha column reference to a numeric column reference. TO-COLUMN-NUMBER is a function! value. ARGUMENTS: alpha [word! string! char!] </pre> ##to-string-date <pre>USAGE: TO-STRING-DATE date DESCRIPTION: Convert 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. /day => Day precedes date. </pre> ##to-string-time <pre>USAGE: TO-STRING-TIME time DESCRIPTION: Convert a string or Rebol time to a HH:MM:SS string. TO-STRING-TIME is a function! value. ARGUMENTS: time [string! date! time!] REFINEMENTS: /precise => </pre> ##unarchive <pre>USAGE: UNARCHIVE source DESCRIPTION: Decompresses archive (only works with compression methods 'store and 'deflate). UNARCHIVE is a function! value. ARGUMENTS: source [file! url! binary!] REFINEMENTS: /only => file [file!] /info => File name/sizes only (size only for gzip). </pre> ##write-dsv <pre>USAGE: WRITE-DSV file data DESCRIPTION: Write block(s) of values to a delimited text file. WRITE-DSV is a function! value. ARGUMENTS: file [file! url!] "csv or tab-delimited text file." data [block!] </pre> ##write-excel <pre>USAGE: WRITE-EXCEL file data DESCRIPTION: Write block(s) of values to an Excel file. WRITE-EXCEL is a function! value. ARGUMENTS: file [file! url!] data [block!] {Name [string!] Data [block!] Widths [block!] records.} REFINEMENTS: /filter => Add auto filter. </pre> # Tests |#|Test|Result| |-:|-|-| |1|settings|make object! [build: 'red os: 'Windows target: 32 ...]| |2|archive ""|none| |3|archive "a"|#{1F8B08000000000002FF4B040043BEB7E801000000}| |4|archive [%a ""]|#{ 504B030414000000000000000000000000000000000000000000010000006150 4B01021400140000000000000000000000000000000000000000000100000000 0000000000000000000000000061504B050600000000010001002F0000001F00 00000000 }| |5|archive [%a "x"]|#{ 504B0304140000000000000000008316DC8C0100000001000000010000006178 504B01021400140000000000000000008316DC8C010000000100000001000000 000000000000000000000000000061504B050600000000010001002F00000020 0000000000 }| |6|archive [%a/ none]|#{ 504B03041400000000000000000000000000000000000000000002000000612F 504B010214001400000000000000000000000000000000000000000002000000 0000000000000000000000000000612F504B0506000000000100010030000000 200000000000 }| |7|archive [%a "1" %b "2"]|#{ 504B030414000000000000000000B7EFDC830100000001000000010000006131 504B0304140000000000000000000DBED51A0100000001000000010000006232 504B0102140014000000000000000000B7EFDC83010000000100000001000000 000000000000000000000000000061504B01021400140000000000000000000D BED51A010000000100000001000000000000000000000000002000000062504B 050600000000020002005E000000400000000000 }| |8|archive [%a "12341234123412341234"]|#{ 504B030414000000080000000000DD14A53C0800000014000000010000006133 3432363144C300504B0102140014000000080000000000DD14A53C0800000014 00000001000000000000000000000000000000000061504B0506000000000100 01002F000000270000000000 }| |9|call-out {sqlite3.exe a.db ""}|""| |10|check []|true| |11|check [[1]]|true| |12|cols? ""|0| |13|cols? ","|2| |14|cols? "a,b"|2| |15|cols? "a b"|2| |16|cols? "^/a b"|2| |17|cols? "a&#124;b"|2| |18|cols? "a~b"|2| |19|cols? "a;b"|2| |20|cols?/with ":" #":"|2| |21|cols? %test.xlsx|2| |22|cols?/sheet %test.xlsx 1|2| |23|cols?/sheet %test.xlsx 2|2| |24|cols?/sheet %test.xlsx 3|2| |25|crc32 ""|0| |26|crc32 "a"|-390611389| |27|crc32 #{00}|-771559539| |28|delimiter? ""|#","| |29|delimiter? "a"|#","| |30|delimiter? " "|#" "| |31|delimiter? "&#124;"|#"|"| |32|delimiter? "~"|#"~"| |33|delimiter? ";"|#";"| |34|dezero ""|""| |35|dezero "0"|""| |36|dezero "10"|"10"| |37|dezero "01"|"1"| |38|dezero "001"|"1"| |39|digit|make bitset! #{000000000000FFC0}| |40|digits? ""|none| |41|digits? "a"|none| |42|digits? "1"|true| |43|distinct [["" ""] [a 1]]|[[a 1]]| |44|distinct [[none none] [a 1]]|[[a 1]]| |45|distinct [[a] [a]]|[[a]]| |46|distinct [[a 1] [a 1]]|[[a 1]]| |47|enblock [1 2 3 4] 1|[[1] [2] [3] [4]]| |48|enblock [1 2 3 4] 2|[[1 2] [3 4]]| |49|enzero "" 1|"0"| |50|enzero "10" 1|"10"| |51|enzero "10" 2|"10"| |52|enzero "10" 3|"010"| |53|excel? %test.xlsx|true| |54|excel? %a.txt|none| |55|excel? to-binary "<?xml"|true| |56|excel? "Text"|false| |57|export []|[]| |58|export [distinct]|[distinct]| |59|export [cols? rows?]|[cols? rows?]| |60|fields? ""|[]| |61|fields? ","|["" ""]| |62|fields? "a,b"|["a" "b"]| |63|fields? "^/a,b"|["a" "b"]| |64|fields? "a:b"|["a:b"]| |65|fields? {"a","b"}|["a" "b"]| |66|fields? {"a" ,"b"}|["a" "b"]| |67|fields? {"a", "b"}|["a" "b"]| |68|fields?/with "a:b" #":"|["a" "b"]| |69|fields? %test.xlsx|["A B" "S1"]| |70|fields?/sheet %test.xlsx 1|["A B" "S1"]| |71|fields?/sheet %test.xlsx 2|["AB" "S2"]| |72|fields?/sheet %test.xlsx 3|["A-B" "S3"]| |73|first-line ""|""| |74|first-line "a,1"|"a,1"| |75|first-line "^/a,1"|"a,1"| |76|first-line "a,1^/"|"a,1"| |77|flatten []|[]| |78|flatten [[a] [b]]|[a b]| |79|flatten [[a 1] [b 2]]|[a 1 b 2]| |80|latin1-to-utf8 #{}|""| |81|latin1-to-utf8 #{00}|""| |82|latin1-to-utf8 #{C2A0}|" "| |83|latin1-to-utf8 #{FC}|"ΓΌ"| |84|letter|make bitset! #{00000000000000007FFFFFE07FFFFFE0}| |85|letters? ""|true| |86|letters? "a"|true| |87|letters? "1"|false| |88|like "" ""|none| |89|like %abc %a|true| |90|like %abc %*a|true| |91|like %abc %?a|false| |92|like %abc %b|false| |93|like %abc %*b|true| |94|like %abc %?b|true| |95|like %abc %a?c|true| |96|like %abc %a?b|false| |97|like %abc %a*c|true| |98|like %abc %a*b|true| |99|list []|[]| |100|list [[a] [b]]|[[a] [b]]| |101|load-dsv ""|[]| |102|load-dsv ","|[["" ""]]| |103|load-dsv {" a ^/ b "}|[[" a ^/ b "]]| |104|load-dsv "a,"|[["a" ""]]| |105|load-dsv {"a" ,"b"}|[["a" "b"]]| |106|load-dsv {"a", "b"}|[["a" "b"]]| |107|load-dsv/part "a,b,c" 1|[["a"]]| |108|load-dsv/part "a,b,c" [1]|[["a"]]| |109|load-dsv/part "a,b,c" [3 1]|[["c" "a"]]| |110|load-dsv/part "a,b,c" [1 "Y"]|[["a" "Y"]]| |111|load-dsv "a,1^/b,2"|[["a" "1"] ["b" "2"]]| |112|load-dsv/where "a,1^/b,2" [row/1 = "a"]|[["a" "1"]]| |113|load-dsv "a:b"|[["a:b"]]| |114|load-dsv/with "a:b" #":"|[["a" "b"]]| |115|load-dsv/part %a.csv '&F1|[["F1"] ["1"]]| |116|load-dsv/where %a.csv [&F1 = "1"]|[["1" "2"]]| |117|load-dsv/where "A^/0" [digits? &A &A: to-integer &A]|[[0]]| |118|load-fixed %a.txt|[["1" "2"] ["33" "44"]]| |119|load-fixed/spec %a.txt [3 2]|[["1" "2"] ["33" "44"]]| |120|load-fixed/part %a.txt 1|[["1"] ["33"]]| |121|load-xml %test.xlsx|[["A B" "S1"] ["1" "2"]]| |122|load-xml/sheet %test.xlsx 2|[["AB" "S2"] ["1" "2"]]| |123|load-xml/part %test.xlsx '&S1|[["S1"] ["2"]]| |124|load-xml/where %test.xlsx [&S1 = "2"]|[["1" "2"]]| |125|max-of []|none| |126|max-of [1 2]|2| |127|merge [] 1 [] 1 [1]|[]| |128|merge [[a 1] [b 2]] 2 [[1 "A"]] 1 [1 4]|[[a "A"]]| |129|merge [[a 1]] 2 [[2 1] [1 "A"]] 1 [1 4]|[[a "A"]]| |130|merge/default [[a 1] [b 2]] 2 [[1 "A"]] 1 [1 4]|[[a "A"] [b none]]| |131|min-of []|none| |132|min-of [1 2]|1| |133|mixedcase ""|""| |134|mixedcase "aa"|"Aa"| |135|mixedcase "aa bb"|"Aa Bb"| |136|munge []|[]| |137|munge/where [[a] [a] [b]] 'a|[[a] [a]]| |138|munge [[a 1] [b 2]]|[[a 1] [b 2]]| |139|munge/part [[a 1]] 1|[[a]]| |140|munge/part [[a 1]] [2 1]|[[1 a]]| |141|munge/part [[a 1]] [1 "Y"]|[[a "Y"]]| |142|munge/where [[a 1]] [even? row/2]|[]| |143|munge/where [[a 1]] [odd? row/2]|[[a 1]]| |144|munge/where [[a 1] [a 2] [b 3]] 'a|[[a 1] [a 2]]| |145|munge/where [[a 1]] [row/1: 0]|[[0 1]]| |146|munge/delete [[a 1] [b 2]] [true]|[]| |147|munge/delete [[a 1] [b 2]] 'a|[[b 2]]| |148|munge/delete [[a 1] [b 2]] [row/1 = 'a]|[[b 2]]| |149|munge/group [[1] [2]] 'count|[[1 1] [2 1]]| |150|munge/group [[1] [2]] 'avg|1.5| |151|munge/group [[1] [2]] 'sum|3| |152|munge/group [[1] [2]] 'min|1| |153|munge/group [[1] [2]] 'max|2| |154|munge/group [[a] [b] [a]] [count > 1]|[[a 2]]| |155|munge/group [[a 1] [a 2] [b 3]] 'avg|[[a 1.5] [b 3]]| |156|munge/group [[a 1] [a 2] [b 3]] 'sum|[[a 3] [b 3]]| |157|munge/group [[a 1] [a 2] [b 3]] 'min|[[a 1] [b 3]]| |158|munge/group [[a 1] [a 2] [b 3]] 'max|[[a 2] [b 3]]| |159|munge/part [[A B] [1 2]] '&A|[[A] [1]]| |160|munge/part next [[A B] [1 2]] '&A|[[1]]| |161|munge/part [[A B] [1 2]] [&A]|[[A] [1]]| |162|munge/part next [[A B] [1 2]] [&A]|[[1]]| |163|munge/where next [[A B] [1 2]] [even? &B]|[[1 2]]| |164|munge/where [[A] [0]] [&A = 0 &A: 1]|[[1]]| |165|oledb %test.xlsx "SELECT * FROM Sheet1"|[["A B" "S1"] ["1" "2"]]| |166|oledb %test.xlsx "SELECT F1 FROM Sheet1"|[["A B"] ["1"]]| |167|oledb %test.accdb "SELECT * FROM Table1"|[["1" "2"] ["3" "4"] ["5" "6"]]| |168|oledb %test.accdb "SELECT A FROM Table1"|[["1"] ["3"] ["5"]]| |169|parse-series "" []|true| |170|write-dsv %a.txt [[1] [2]] read-string %a.txt|"1^/2^/"| |171|read-binary %sqlite3.exe true|true| |172|read-binary/part %sqlite3.exe 2|#{4D5A}| |173|read-string %a.txt|"1^/2^/"| |174|replace-deep [a [a]] make map! [a b]|[b [b]]| |175|rows? ""|0| |176|rows? "a"|1| |177|rows? "a^/"|2| |178|rows? %test.xlsx|2| |179|rows?/sheet %test.xlsx 2|2| |180|penult []|none| |181|penult [1]|none| |182|penult [1 2]|1| |183|penult [1 2 3]|2| |184|second-last ""|none| |185|second-last "1"|none| |186|second-last "12"|#"1"| |187|second-last "123"|#"2"| |188|sheets? %test.xlsx|["A B" "AB" "A-B"]| |189|sqlcmd sn db "select ''"|[]| |190|sqlcmd sn db "select NULL"|[[""]]| |191|sqlcmd sn db "select 1 where 0 = 1"|[]| |192|sqlcmd sn db "select NULL,NULL"|[["" ""]]| |193|sqlcmd sn db "select 1,NULL"|[["1" ""]]| |194|sqlcmd sn db "select NULL,1"|[["" "1"]]| |195|sqlcmd sn db "select 0"|[["0"]]| |196|sqlcmd sn db "select 0,1"|[["0" "1"]]| |197|sqlcmd sn db "select 0,''"|[["0" ""]]| |198|sqlcmd/key sn db "select 0" 1|[[0]]| |199|sqlcmd/headings sn db "select 1 A"|[["A"] ["1"]]| |200|sqlite %a.db "select ''"|[]| |201|sqlite %a.db "select NULL"|[]| |202|sqlite %a.db "select 1 where 0 = 1"|[]| |203|sqlite %a.db "select NULL,NULL"|[["" ""]]| |204|sqlite %a.db "select 1,NULL"|[["1" ""]]| |205|sqlite %a.db "select NULL,1"|[["" "1"]]| |206|sqlite %a.db "select 0"|[["0"]]| |207|sqlite %a.db "select 0,1"|[["0" "1"]]| |208|sqlite %a.db "select 0,''"|[["0" ""]]| |209|to-column-alpha 1|"A"| |210|to-column-alpha 27|"AA"| |211|to-column-number 'A|1| |212|to-column-number "aa"|27| |213|to-string-date "01-02-68"|"2068-02-01"| |214|to-string-date "01-02-70"|"1970-02-01"| |215|to-string-date 1-Feb-2015|"2015-02-01"| |216|to-string-date "20150201"|"2015-02-01"| |217|to-string-date "01/02/2015"|"2015-02-01"| |218|to-string-date/mdy "01/02/2015"|"2015-01-02"| |219|to-string-date/ydm "15/02/01"|"2015-01-02"| |220|to-string-date/day "Mon 01-02-2015"|"2015-02-01"| |221|to-string-date "01-02-2015"|"2015-02-01"| |222|to-string-date/mdy "01-02-2015"|"2015-01-02"| |223|to-string-date/ydm "15-02-01"|"2015-01-02"| |224|to-string-date "41506"|"2013-08-20"| |225|to-string-time 1:00:00|"01:00:00"| |226|to-string-time "013000000"|"01:30:00"| |227|to-string-time "1:00AM"|"01:00:00"| |228|to-string-time "1:00 AM"|"01:00:00"| |229|to-string-time "1:00PM"|"13:00:00"| |230|to-string-time "1:00 PM"|"13:00:00"| |231|to-string-time "0.75"|"18:00:00"| |232|unarchive r0|#{61}| |233|unarchive r1|[%a #{}]| |234|unarchive r2|[%a #{78}]| |235|unarchive/info r2|[%a 1]| |236|unarchive/only r2 %a|#{78}| |237|unarchive/only r2 %b|none| |238|unarchive r3|[%a/ none]| |239|unarchive r4|[%a #{31} %b #{32}]| |240|unarchive r5|[%a #{3132333431323334313233343132333431323334}]| |241|write-dsv %a.txt [] read-string %a.txt|""| |242|write-dsv %a.txt [[]] read-string %a.txt|""| |243|write-dsv %a.csv [[a 1] [b 2]] read-string %a.csv|"a,1^/b,2^/"| |244|write-dsv %a.txt [[a 1] [b 2]] read-string %a.txt|"a 1^/b 2^/"| |245|write-excel %a.xlsx ["A" [[A B] [1 2]] [5 10]]|%a.xlsx| |246|write-excel/filter %a.xlsx ["A" [[A] [B]] [5]]|%a.xlsx|