- Publish a SQL table as a JSON web service
- Import a CSV from SFTP into a database, every morning
- Mail a PDF report built from live data
- Turn a partner CSV text into JSON objects
- Sort JSON orders and export them as CSV
- Incremental sync between two databases with a watermark
- One connection per environment and engine, checked without connecting
- Apply a partner's CSV delta to a price table with MERGE
- Nightly export pack: CSV, Excel and PDF in an outbox connection
- Nightly copy of an Oracle table into MySQL, committed by batches
- Check a job schedule
- Load a partner CSV file into a table: detection, typing, upsert and rejected rows
- From a REST API answer to an Excel report: nested orders flattened into lines
- Read a mainframe fixed width export with header, trailer and two record types
- Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
- Generate a Word document from a template: fields, a repeated table row, a condition
- One Markdown report published in HTML, in Word and in plain text
- Send an appointment and a contact that open in every calendar and phone
- Import an EDIFACT purchase order and answer with an acknowledgement
- Read a laboratory result in HL7 v2 and answer the sender
- Import a JSON Lines export and report the bad lines
- Write an order confirmation from a template, with the customer data escaped
- Read a GS1 logistic label and write the next one
- Turn the registers of an energy meter into numbers, and prepare a setpoint
- Export orders to an Avro file for a data pipeline, then check the file
- Build and read a Protocol Buffers message of a sensor, from the text of its .proto
- Merge the news feeds of suppliers into one feed for the intranet
- Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
- Review a configuration change as a diff, apply it elsewhere and merge two edits
- Turn a country typed by a user into its ISO codes, calling code, currency and EU status
- Run an import job once at a time and process each message only once
- Detect the encoding of a legacy export and convert it to UTF-8 before loading it
- Import a stock count typed in LibreOffice Calc, with a title line and bad quantities
- Send the report of a nightly import to Telegram and Discord without breaking the message
- Give GPS points to a GIS team as a Shapefile in the local projection
- Read an application log of your own format with a grok pattern
- Publish the sales of the month as a LibreOffice document, then read its table back
Publish a SQL table as a JSON web service
A partner needs your product list. One script declares a typed GET service, queries the database through a stored connection and returns JSON. MentDB handles the URL, the authentication and the parameter checks.
script create get "api.products" false 1 (param (var "[type]" {type is_char [type] 1} "Product type (A, B ...)" is_null:false is_empty:false "A") ) "Return the products of one type as JSON" { #1. Open the database connection; sql connect "session1" {cm get "demo_cm_mysql";}; try { #2. Query and convert to JSON in one call; -> "[json]" (sql to json "session1" "products" (concat "select * from products where type=" (sql encode [type]))); } { sql disconnect "session1"; exception (1) [err]; } "[err]"; #3. Always release the connection; sql disconnect "session1"; [json]; } "JSON with the products";
- Get the HTTP URL —
script generate url "api.products.get" - Restrict access — grant the script to a group with the Group functions
- Return XML or CSV — swap for
sql to xmlorsql to csv - Version it — Versioning keeps every change of the script
Import a CSV from SFTP into a database, every morning
A partner drops orders.csv on its SFTP server. The script downloads it, inserts each line in one transaction, writes a trace in the log, and a job runs it every day at 6:00.
script create exe "etl.import.orders" false 1 (param ;) "Import the daily orders file from the partner SFTP" { #1. Download the file; -> "[local]" (concat "tmp/orders_" (date sysdate) ".csv"); sftp connect "sftp1" {cm get "demo_cm_sftp";}; sftp get "sftp1" "/out/orders.csv" [local]; sftp disconnect "sftp1"; #2. Insert each line in one transaction; sql connect "db1" {cm get "demo_cm_mysql";}; -> "[nb]" 0; csv parse (mql "O") [local] (mql ",") (mql "'") (mql "id,name,quantity") { sql dml "db1" (concat "insert into orders (id, name, quantity) values (" (sql encode [O_id]) ", " (sql encode [O_name]) ", " (sql encode [O_quantity]) ")"); ++ "[nb]"; }; sql commit "db1"; sql disconnect "db1"; #3. Trace the run; log write (concat [nb] " orders imported") OK "file" [local]; } "";
- FTP or Windows share — same chain with FTP, FTPS or CIFS
- Excel instead of CSV —
excelx loadthenexcelx cell get - On file arrival — replace the job with
file_watcher start - Rollback on error — wrap the loop in
tryand callsql rollback
Mail a PDF report built from live data
The SQL result becomes an HTML table, the HTML becomes a PDF, and the PDF leaves as an attachment. The mail goes through the MentDB spooler, so a failed sending is kept and can be replayed.
script create exe "report.products.daily" false 1 (param ;) "Send the daily products report" { #1. Build the HTML table from SQL, then the PDF; -> "[pdf]" (concat "tmp/products_" (date sysdate) ".pdf"); sql connect "db1" {cm get "demo_cm_mysql";}; pdf from html (sql to html "db1" "products" (concat "select * from products")) [pdf]; sql disconnect "db1"; #2. Prepare the attachment list; json load "att" "[]"; json iarray "att" / [pdf] STR; #3. Queue the mail (sent by the spooler, kept on error); mail send "demo_cm_smtp" "sales@example.com" "" "" "Daily products report" "Please find the report attached." (json doc "att"); json unload "att"; } "";
- Excel instead of PDF —
sql to excelx - Protect the PDF —
pdf protect, or add apdf watermark add - Replay failed mails —
mail show_errorthenmail replay_error_all - Every Monday —
job addwith"0 0 8 ? * MON"
Turn a partner CSV text into JSON objects
A partner sends a semicolon-separated text with a header line and a missing quantity. The script splits the lines, extracts each field with a regex, replaces the empty quantity by 0, computes the line amount and returns a JSON array ready for an API.
#1. CSV text received from a partner, separator semicolon; -> "[csv]" (concat "sku;label;qty;price" [_n_] "BLT-M8;Bolt M8;250;0.12" [_n_] "NUT-M8;Nut M8;300;0.05" [_n_] "WSH-M8;Washer M8;;0.02"); #2. One JSON object per data line, empty quantity set to 0; json load "rx_lines" (string split [csv] "\n" -1); json load "rx_out" "[]"; json parse_array "rx_lines" "/" "[line]" { if (not (string starts_with [line] "sku;")) { -> "[qty]" (string regexp_extract [line] "^[^;]*;[^;]*;([^;]*);" 1); if (string is_blank [qty]) {-> "[qty]" 0;}; json iarray "rx_out" "/" (concat "{\"sku\": \"" (string regexp_extract [line] "^([^;]*)" 1) "\", \"label\": \"" (string json_escape (string regexp_extract [line] "^[^;]*;([^;]*)" 1)) "\", \"qty\": " [qty] ", \"amount\": " (math round (* [qty] (string regexp_extract [line] "([^;]*)$" 1)) 2) "}") OBJ; }; }; #3. The JSON document ready for the API; json doc "rx_out";
[
{
"amount": 30,
"qty": 250,
"label": "Bolt M8",
"sku": "BLT-M8"
},
{
"amount": 15,
"qty": 300,
"label": "Nut M8",
"sku": "NUT-M8"
},
{
"amount": 0,
"qty": 0,
"label": "Washer M8",
"sku": "WSH-M8"
}
]- For a CSV file on disk,
csv parsegives one variable per column directly - Reject bad lines: check the price with
type is_decimalbefore inserting the object
Sort JSON orders and export them as CSV
An API returns orders as JSON; accounting wants a CSV sorted by amount with a total line. The script sorts the array, loops on the orders, quotes the values that contain the separator and adds the total.
#1. Orders as JSON from an API; json load "rx_ord" "[{\"id\": \"A-102\", \"customer\": \"Dupont, SARL\", \"amount\": 1250.5}, {\"id\": \"A-103\", \"customer\": \"Martin\", \"amount\": 89.9}, {\"id\": \"A-104\", \"customer\": \"L'Atelier du Bois\", \"amount\": 640}]"; #2. Sort by amount, biggest first; json sort "rx_ord" "/" "amount" "DESC"; #3. Write a CSV with a header and safely quoted values; -> "[csv]" "id,customer,amount"; -> "[total]" 0; json parse_array "rx_ord" "/" "[o]" { json load "rx_o" [o]; -> "[csv]" (concat [csv] [_n_] (json select "rx_o" "/id") "," (string csv_value (json select "rx_o" "/customer") "," "\"") "," (json select "rx_o" "/amount")); -> "[total]" (+ [total] (json select "rx_o" "/amount")); }; concat [csv] [_n_] "TOTAL,," [total];
id,customer,amount A-102,"Dupont, SARL",1250.5 A-104,L'Atelier du Bois,640 A-103,Martin,89.9 TOTAL,,1980.4
- Semicolon CSV for a French Excel: change the separator in
string csv_valueand in the concat - Format the amounts with
math decimal_format
Incremental sync between two databases with a watermark
Copy only the rows changed since the last run from an HSQL source to an H2 target. The last updated_at seen is kept in a parameter (the watermark); the target is upserted with MERGE, so a second run only moves the new and modified rows.
#1. Source and target; cm set "tuto_p4i_src" {execute "db.hsql.embedded.config.get" "[database]" "tmp/rx_p4i_sync/src" "[user]" "SA" "[password]" "";}; cm set "tuto_p4i_dst" {execute "db.h2.embedded.config.get" "[database]" "./tmp/rx_p4i_sync/dst" "[user]" "sa" "[password]" "sa";}; sql connect "rx_p4i_s" {cm get "tuto_p4i_src"}; sql connect "rx_p4i_d" {cm get "tuto_p4i_dst"}; sql dml "rx_p4i_s" (concat "create table customer (id int primary key, name varchar(40), city varchar(30), updated_at timestamp)"); sql dml "rx_p4i_s" (concat "insert into customer values (1, 'Garage Hoarau', 'Saint-Denis', '2026-10-02 09:00:00'), (2, 'Pharmacie du Port', 'Le Port', '2026-10-05 14:30:00'), (3, 'Boulangerie Payet', 'Saint-Pierre', '2026-10-07 08:15:00')"); sql dml "rx_p4i_d" (concat "create table customer (id int primary key, name varchar(40), city varchar(30), updated_at timestamp)"); #2. The watermark, empty at the first run; parameter add "tuto_p4i_wm" "1970-01-01 00:00:00" "Last updated_at copied to the target" false; -> "[log]" ""; for (-> "[run]" 1) (<= [run] 2) (++ "[run]") { -> "[wm]" (parameter get value "tuto_p4i_wm"); -> "[n]" 0; #3. Delta since the watermark, 4. upsert; sql parse "rx_p4i_s" "C" (concat "select id, name, city, updated_at from customer where updated_at > " (sql encode [wm]) " order by updated_at") { sql dml "rx_p4i_d" (concat "merge into customer key(id) values (" [C_ID] ", " (sql encode [C_NAME]) ", " (sql encode [C_CITY]) ", " (sql encode [C_UPDATED_AT]) ")"); -> "[last]" [C_UPDATED_AT]; ++ "[n]"; }; #5. Advance the watermark; if (> [n] 0) {parameter update "tuto_p4i_wm" (string substring [last] 0 19);}; -> "[log]" (concat [log] "run " [run] ": watermark " [wm] ", rows moved " [n] [_n_]); #Between the runs, the source changes: one update, one new customer; if (== [run] 1) { sql dml "rx_p4i_s" (concat "update customer set city = 'Saint-Paul', updated_at = '2026-10-20 10:00:00' where id = 2"); sql dml "rx_p4i_s" (concat "insert into customer values (4, 'Fleurs du Lagon', 'Saint-Gilles', '2026-10-21 16:45:00')"); }; }; -> "[target]" (sql to csv "rx_p4i_d" "customer" (concat "select id, name, city from customer order by id") ";" "\""); -> "[final_wm]" (parameter get value "tuto_p4i_wm"); #Cleanup of the demo; sql dml "rx_p4i_s" "SHUTDOWN"; sql disconnect "rx_p4i_s"; sql disconnect "rx_p4i_d"; cm remove "tuto_p4i_src"; cm remove "tuto_p4i_dst"; parameter remove "tuto_p4i_wm"; file delete "tmp/rx_p4i_sync"; concat [log] "watermark now: " [final_wm] [_n_] [target];
run 1: watermark 1970-01-01 00:00:00, rows moved 3 run 2: watermark 2026-10-07 08:15:00, rows moved 2 watermark now: 2026-10-21 16:45:00 id;name;city 1;Garage Hoarau;Saint-Denis 2;Pharmacie du Port;Saint-Paul 3;Boulangerie Payet;Saint-Pierre 4;Fleurs du Lagon;Saint-Gilles
- Schedule it every 5 minutes with
job add, the watermark makes each run short. - Handle the deletions with a soft-delete flag in the source, or a nightly full compare.
- Run the two connections inside a transaction with
sql auto_commitfalse andsql commit.
One connection per environment and engine, checked without connecting
The integration platform targets several database servers: H2 and HSQL in server mode for the test benches, Derby (server and embedded) for an old Java application, MySQL in production. Declare them all, print the JDBC URL generated for each one, check they are all of type db, then remove them.
#1. The password of the test benches comes from a private parameter; parameter add "tuto_p4i_bench_pwd" "Bench-2026!" "Password of the test bench databases" true; -> "[pwd]" (parameter get value "tuto_p4i_bench_pwd"); cm set "tuto_p4i_h2srv" {execute "db.h2.server.config.get" "[hostname]" "bench1.lan.example.com" "[port]" "9092" "[database]" "orders" "[user]" "sa" "[password]" [pwd];}; cm set "tuto_p4i_hsqlsrv" {execute "db.hsql.server.config.get" "[hostname]" "bench2.lan.example.com" "[port]" "9001" "[database]" "stock" "[user]" "SA" "[password]" [pwd];}; cm set "tuto_p4i_derbysrv" {execute "db.derby.server.config.get" "[hostname]" "legacy.lan.example.com" "[port]" "1527" "[database]" "payroll" "[user]" "app" "[password]" [pwd];}; #2. The old Java application keeps its Derby database in a local folder; cm set "tuto_p4i_derbyemb" {execute "db.derby.embedded.config.get" "[database]" "data/legacy/payroll" "[user]" "app" "[password]" [pwd];}; cm set "tuto_p4i_mysqlprd" {execute "db.mysql.config.get" "[hostname]" "mysql-prod.example.com" "[port]" "3306" "[database]" "erp" "[user]" "integration" "[password]" [pwd];}; -> "[pwd]" ""; #3. Check each one; -> "[rep]" ""; json load "rx_p4i_ids" "[\"tuto_p4i_h2srv\",\"tuto_p4i_hsqlsrv\",\"tuto_p4i_derbysrv\",\"tuto_p4i_derbyemb\",\"tuto_p4i_mysqlprd\"]"; json parse_array "rx_p4i_ids" "/" "[id]" { json load "rx_p4i_cm" (cm get [id]); -> "[rep]" (concat [rep] (string rpad (string replace [id] "tuto_p4i_" "") " " 10) " db=" (cm exist [id] "db") " " (json select "rx_p4i_cm" "/jdbc") [_n_]); }; #4. Inventory and cleanup; -> "[count]" (string regexp_count (cm show "db") "tuto_p4i_"); json parse_array "rx_p4i_ids" "/" "[id]" {cm remove [id];}; parameter remove "tuto_p4i_bench_pwd"; json unload "rx_p4i_ids"; json unload "rx_p4i_cm"; concat [rep] "Declared db connections: " [count] ", left after cleanup: " (string regexp_count (cm show "db") "tuto_p4i_");
h2srv db=1 jdbc:h2:tcp://bench1.lan.example.com:9092/orders hsqlsrv db=1 jdbc:hsqldb:hsql:bench2.lan.example.com:9001/stock derbysrv db=1 jdbc:derby://legacy.lan.example.com:1527/payroll derbyemb db=1 jdbc:derby:data/legacy/payroll mysqlprd db=1 jdbc:mysql://mysql-prod.example.com:3306/erp Declared db connections: 5, left after cleanup: 0
- Generate the update script of one connection with
cm generate_updateto change its host. - Open the H2 bench with
sql connectonce the server is started. - Use
cm show_scrudto see which connections can serve a SCRUD API.
Apply a partner's CSV delta to a price table with MERGE
Each night a supplier sends a CSV with new and changed prices. Apply it to the local price table with an upsert, count the inserts and the updates, reject the lines with an invalid price and give the variation of each changed price.
#1. The local table; cm set "tuto_p4i_price_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/rx_p4i_prices/db" "[user]" "sa" "[password]" "sa";}; sql connect "rx_p4i_p" {cm get "tuto_p4i_price_db"}; sql dml "rx_p4i_p" (concat "create table price (sku varchar(10) primary key, label varchar(40), price decimal(10,2), old_price decimal(10,2))"); sql dml "rx_p4i_p" (concat "insert into price values ('A-100', 'Pallet wrap', 18.50, null), ('A-205', 'Carton 40x30', 0.62, null), ('B-310', 'Label roll', 7.90, null)"); #2. The delta of the night; file create "tmp/rx_p4i_delta.csv" (concat "sku;label;price" [_n_] "A-205;Carton 40x30;0.66" [_n_] "B-310;Label roll;7.50" [_n_] "C-020;Tape 50mm;1.35" [_n_] "C-044;Strapping band;N/A") "UTF-8"; -> "[ins]" 0; -> "[upd]" 0; -> "[rej]" ""; csv parse (mql "D") (mql "tmp/rx_p4i_delta.csv") (mql ";") (mql "\"") (mql "") { if (type is_number [D_price]) { -> "[exists]" (sql value "rx_p4i_p" (concat "select count(*) from price where sku = " (sql encode [D_sku]))); if (== [exists] 0) {++ "[ins]"; -> "[old]" "null";} {++ "[upd]"; -> "[old]" (sql value "rx_p4i_p" (concat "select price from price where sku = " (sql encode [D_sku])));}; #3. Upsert, the old price is kept for the report; sql dml "rx_p4i_p" (concat "merge into price key(sku) values (" (sql encode [D_sku]) ", " (sql encode [D_label]) ", " [D_price] ", " [old] ")"); } { -> "[rej]" (concat [rej] [D_sku] " (price " [D_price] ") "); }; }; #4. Report; json load "rx_p4i_var" (sql to json "rx_p4i_p" "variation" (concat "select sku, old_price, price, round(100*(price-old_price)/old_price, 1) as pct from price where old_price is not null order by sku")); -> "[res]" (concat "inserted: " [ins] ", updated: " [upd] ", rejected: " [rej] [_n_]); json parse_array "rx_p4i_var" "/data" "[v]" { json load "rx_p4i_v" [v]; -> "[res]" (concat [res] (json select "rx_p4i_v" "/sku") " " (json select "rx_p4i_v" "/old_price") " -> " (json select "rx_p4i_v" "/price") " (" (json select "rx_p4i_v" "/pct") " %)" [_n_]); }; -> "[res]" (concat [res] "rows in the table: " (sql value "rx_p4i_p" (concat "select count(*) from price"))); sql disconnect "rx_p4i_p"; cm remove "tuto_p4i_price_db"; file delete "tmp/rx_p4i_prices"; file delete "tmp/rx_p4i_delta.csv"; json unload "rx_p4i_var"; json unload "rx_p4i_v"; [res];
inserted: 1, updated: 2, rejected: C-044 (price N/A) A-205 0.62 -> 0.66 (6.5 %) B-310 7.90 -> 7.50 (-5.1 %) rows in the table: 4
- Wrap the whole delta in a transaction with
sql auto_commitfalse, thensql commitorsql rollbackif a line is rejected. - Send the rejected lines back to the supplier with
mail send. - Pick the file up from the supplier with
sftp getbefore the parse.
Nightly export pack: CSV, Excel and PDF in an outbox connection
Produce the nightly export pack for the accounting team from the orders database: a CSV for their tool, an Excel file for the controllers and a PDF listing, written into the folder of a file connection, then zipped for the transfer.
#1. The database and the outbox; cm set "tuto_p4i_ord_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/rx_p4i_exp/db" "[user]" "sa" "[password]" "sa";}; cm set "tuto_p4i_outbox" {execute "file.local.config.get" "[dir]" "tmp/rx_p4i_outbox";}; json load "rx_p4i_ob" (cm get "tuto_p4i_outbox"); -> "[out]" (json select "rx_p4i_ob" "/localDir"); file mkdir [out]; sql connect "rx_p4i_e" {cm get "tuto_p4i_ord_db"}; sql dml "rx_p4i_e" (concat "create table orders (id int primary key, order_day date, customer varchar(40), amount decimal(10,2))"); sql dml "rx_p4i_e" (concat "insert into orders values (7001, '2026-10-31', 'Garage Hoarau', 420.00), (7002, '2026-10-31', 'Pharmacie du Port', 89.90), (7003, '2026-10-31', 'Garage Hoarau', 1310.50), (7004, '2026-10-31', 'Fleurs du Lagon', 56.00)"); -> "[q]" (concat "select id, order_day, customer, amount from orders where order_day = '2026-10-31' order by id"); #2. CSV, Excel and PDF from the same query; sql to csv_file "rx_p4i_e" (concat [out] "/orders_20261031.csv") [q] ";" "\""; sql to excelx "rx_p4i_e" "orders" [q] (concat [out] "/orders_20261031.xlsx"); sql to pdf "rx_p4i_e" "orders" [q] (concat [out] "/orders_20261031.pdf"); -> "[total]" (sql value "rx_p4i_e" (concat "select sum(amount) from orders where order_day = '2026-10-31'")); sql disconnect "rx_p4i_e"; #3. Pack the outbox; compress zip [out] "tmp/rx_p4i_pack_20261031.zip"; -> "[rep]" ""; json load "rx_p4i_fl" (file dir_list_regex [out] "orders_.*" true false); json parse_array "rx_p4i_fl" "/" "[f]" { -> "[rep]" (concat [rep] [f] " " (file size (concat [out] "/" [f])) " B" [_n_]); }; -> "[rep]" (concat [rep] "CSV lines: " (file count_lines (concat [out] "/orders_20261031.csv")) ", PDF pages: " (pdf page_count_file (concat [out] "/orders_20261031.pdf")) ", total " [total] " EUR" [_n_] "Pack: " (file size "tmp/rx_p4i_pack_20261031.zip") " B"); #4. Cleanup of the demo; cm remove "tuto_p4i_ord_db"; cm remove "tuto_p4i_outbox"; file delete [out]; file delete "tmp/rx_p4i_exp"; file delete "tmp/rx_p4i_pack_20261031.zip"; json unload "rx_p4i_ob"; json unload "rx_p4i_fl"; [rep];
orders_20261031.xlsx 3508 B orders_20261031.csv 181 B orders_20261031.pdf 2414 B CSV lines: 5, PDF pages: 1, total 1876.40 EUR Pack: 5550 B
- Send the pack with
sftp putto the accounting server. - Sign the zip with a SHA-256 manifest so the receiver can check it.
- Name the files with
date sysdatefor a real nightly run.
Nightly copy of an Oracle table into MySQL, committed by batches
Copy the day's invoice lines from the Oracle finance database into the MySQL reporting database: read with sql parse, insert in MySQL, commit every 500 rows to keep the transactions short, and log the volume and the duration.
#1. Connections (declared once on the server, here for the example); cm set "tuto_p4i_fin" {execute "db.oracle.config.get" "[hostname]" "ora.erp.example.com" "[port]" "1521" "[database]" "FINPRD" "[user]" "fin_read" "[password]" (parameter get value "tuto_p4i_fin_pwd");}; cm set "tuto_p4i_rep" {execute "db.mysql.config.get" "[hostname]" "mysql-rep.example.com" "[port]" "3306" "[database]" "reporting" "[user]" "loader" "[password]" (parameter get value "tuto_p4i_rep_pwd");}; -> "[t0]" (date systimestamp); sql connect "rx_p4i_ora" {cm get "tuto_p4i_fin";}; sql connect "rx_p4i_my" {cm get "tuto_p4i_rep";}; sql set_timeout 600; #2. Short transactions on the target; sql auto_commit "rx_p4i_my" false; sql dml "rx_p4i_my" (concat "delete from invoice_line where inv_date = " (sql encode (date sysdate))); #3. Copy, commit every 500 rows; -> "[n]" 0; sql parse "rx_p4i_ora" "L" (concat "select inv_no, line_no, account, amount, inv_date from fin.invoice_line where inv_date = trunc(sysdate)") { sql dml "rx_p4i_my" (concat "insert into invoice_line values (" (sql encode [L_INV_NO]) ", " [L_LINE_NO] ", " (sql encode [L_ACCOUNT]) ", " [L_AMOUNT] ", " (sql encode (string substring [L_INV_DATE] 0 10)) ")"); ++ "[n]"; if (== (math mod [n] 500) 0) {sql commit "rx_p4i_my";}; }; sql commit "rx_p4i_my"; sql disconnect "rx_p4i_ora"; sql disconnect "rx_p4i_my"; #4. Trace; log write (concat [n] " invoice lines copied in " (date duration [t0] (date systimestamp))) "OK" "rx_p4i_nightly" [n]; [n];
- Roll back with
sql rollbackand re-throw the error in atryblock if an insert fails. - Run it at 02:00 with
job addand a cron. - Check the totals of both sides with
sql valuebefore the commit.
Check a job schedule
Before deploying an ETL job, list its next runs from the cron expression, check that no run falls on a French public holiday, and give the ISO week and the maximum duration allowed between two runs.
#1. Every weekday at 6:00; json load "rx_c" (date cron_next "0 6 * * 1-5" "2026-12-22 12:00:00" 5); -> "[res]" ""; for (-> "[i]" 0) (< [i] (json count "rx_c" /)) (++ "[i]") { -> "[t]" (json select "rx_c" (concat "/[" [i] "]")); -> "[res]" (concat [res] [t] (if (equal (date is_holiday_fr [t]) "1") {" HOLIDAY"} {""}) " (" (date iso_week [t]) ") | "); }; -> "[res]" (concat [res] "max gap " (date duration_format (date duration_parse "3d 1h") "human")); json unload "rx_c"; [res]
2026-12-23 06:00:00 (2026-W52) | 2026-12-24 06:00:00 (2026-W52) | 2026-12-25 06:00:00 HOLIDAY (2026-W52) | 2026-12-28 06:00:00 (2026-W53) | 2026-12-29 06:00:00 (2026-W53) | max gap 3d 1h
- Day of week 1-5 = Monday to Friday; names are accepted too: mon-fri.
date business_days_frcounts the business days between two dates.
Load a partner CSV file into a table: detection, typing, upsert and rejected rows
A partner drops a French CSV file (separator ;, decimal comma, codes with leading zeros). The file is analysed (encoding, separator, types), read as JSON rows and loaded with an upsert on the code: the rows already loaded are updated, the bad rows are rejected with their reason instead of stopping the load.
if (equal (file exist "tmp/tuto_etl") "0") {file mkdir "tmp/tuto_etl";}; #The file of the partner (here written by the recipe, French format with a BOM); csv write "tmp/tuto_etl/partner.csv" "[{\"code\": \"00042\", \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"balance\": \"1250,50\", \"updated\": \"2026-10-31\"}, {\"code\": \"00043\", \"name\": \"Martin\", \"city\": \"Saint-Pierre\", \"balance\": \"-80\", \"updated\": \"2026-11-02\"}, {\"code\": \"00044\", \"name\": \"Hoarau & Fils\", \"city\": \"Le Port\", \"balance\": \"abc\", \"updated\": \"2026-11-02\"}, {\"code\": \"00042\", \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"balance\": \"1300\", \"updated\": \"2026-11-03\"}]" "{\"separator\": \";\", \"bom\": true}"; json load "rx_s" (csv sniff "tmp/tuto_etl/partner.csv"); -> "[rows]" (csv read "tmp/tuto_etl/partner.csv"); sql connect "rx_db" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:rx_etl\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; sql dml "rx_db" "CREATE TABLE customer (code VARCHAR(10) PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), balance DECIMAL(12,2), updated DATE)"; json load "rx_l" (sql bulk_upsert "rx_db" "customer" [rows] "[\"code\"]" "{\"on_error\": \"skip\"}"); -> "[res]" (concat "separator " (json select "rx_s" "/separator") ", decimal " (json select "rx_s" "/decimal") ", balance typed " (json select "rx_s" "/types/balance") " | inserted " (json select "rx_l" "/inserted") ", updated " (json select "rx_l" "/updated") ", rejected " (json select "rx_l" "/rejected") " (row " (json select "rx_l" "/errors/[0]/row") ": " (json select "rx_l" "/errors/[0]/error") ") | balance of 00042: " (sql value "rx_db" "SELECT balance FROM customer WHERE code='00042'")); sql disconnect "rx_db"; json unload "rx_s"; json unload "rx_l"; file delete "tmp/tuto_etl/partner.csv"; [res]
separator ;, decimal ,, balance typed string | inserted 2, updated 1, rejected 1 (row 2: column BALANCE: 'abc' is not a number) | balance of 00042: 1300.00
- The code 00042 keeps its leading zeros: a column with leading zeros stays text.
- The balance column has a text value (abc), so it is read as text and the database conversion rejects only that row.
- With {"on_error": "stop"} (the default), the first bad row stops the load and says how many rows were committed before.
From a REST API answer to an Excel report: nested orders flattened into lines
An order API answers JSON with a nested customer and a list of lines per order. json to_rows flattens it into one row per order line (customer.name, lines.sku...), ready for a table, a database or Excel. The report is written with one sheet of lines and one sheet of totals per customer.
if (equal (file exist "tmp/tuto_etl") "0") {file mkdir "tmp/tuto_etl";}; #In production: -> "[api]" (rest request "GET" "https://api.example.com/v1/orders" "{\"output\": \"body\"}"); -> "[api]" "{\"data\": [{\"id\": \"C-1001\", \"date\": \"2026-11-02\", \"customer\": {\"name\": \"Dupont SA\", \"city\": \"Saint-Denis\"}, \"lines\": [{\"sku\": \"A1\", \"qty\": 2, \"price\": 12.5}, {\"sku\": \"B7\", \"qty\": 1, \"price\": 40}]}, {\"id\": \"C-1002\", \"date\": \"2026-11-03\", \"customer\": {\"name\": \"Martin\", \"city\": \"Saint-Pierre\"}, \"lines\": [{\"sku\": \"A1\", \"qty\": 5, \"price\": 12.5}]}], \"next\": null}"; -> "[rows]" (json to_rows [api] "{\"path\": \"$.data\", \"explode\": \"lines\", \"rename\": {\"customer.name\": \"customer\", \"customer.city\": \"city\", \"lines.sku\": \"sku\", \"lines.qty\": \"qty\", \"lines.price\": \"price\"}}"); -> "[totals]" (table group_by [rows] "[\"customer\"]" "{\"lines\": [\"count\"], \"qty\": [\"sum\", \"qty\"]}"); excelx write "tmp/tuto_etl/orders.xlsx" (concat "{\"Lines\": " [rows] ", \"Customers\": " [totals] "}") "{\"date_columns\": [\"date\"]}"; -> "[res]" (concat (excelx sheets "tmp/tuto_etl/orders.xlsx") " first line: " (excelx read "tmp/tuto_etl/orders.xlsx" "{\"max_rows\": 1}")); file delete "tmp/tuto_etl/orders.xlsx"; [res]
[{"name": "Lines", "index": 0, "rows": 4, "columns": 7}, {"name": "Customers", "index": 1, "rows": 3, "columns": 3}] first line: [{"id": "C-1001", "date": "2026-11-02", "customer": "Dupont SA", "city": "Saint-Denis", "sku": "A1", "qty": 2, "price": 12.5}]- With "explode": "lines", the fields of the order are repeated on each line; an order without lines keeps one row with empty line fields.
- For an API with several pages, rest paginate gathers all the items before json to_rows.
Read a mainframe fixed width export with header, trailer and two record types
Legacy systems (AS400, mainframe, banks) export fixed width files: each field at a position, amounts with implied decimals, several record types identified by their first characters. fixed parse turns them into JSON rows, which are then joined back (lines with their invoice) and written in CSV for a modern tool.
-> "[layout]" "{\"record_type\": {\"start\": 1, \"length\": 2}, \"layouts\": {\"01\": [{\"name\": \"invoice\", \"start\": 3, \"length\": 8}, {\"name\": \"customer\", \"length\": 10}], \"02\": [{\"name\": \"item\", \"start\": 3, \"length\": 6}, {\"name\": \"qty\", \"length\": 3, \"type\": \"int\"}, {\"name\": \"price\", \"length\": 7, \"type\": \"decimal\", \"decimals\": 2}]}}"; #Records of 20 characters without line breaks, as in a mainframe dataset; json load "rx_r" (fixed parse "HEADER 20261103 01F0000104ACME 02CABLE 0020001050 02SWITCH0010004900 01F0000105BOLT INC 02CABLE 0100001050 TRAILER 3 " [layout] "{\"record_length\": 20, \"skip\": 1, \"skip_trailer\": 1}"); #Each line 02 belongs to the last invoice 01 above it; json load "rx_o" "[]"; -> "[inv]" ""; -> "[cust]" ""; for (-> "[i]" 0) (< [i] (json count "rx_r" "/")) (++ "[i]") { if (equal (json select "rx_r" (concat "/[" [i] "]/_type")) "01") { -> "[inv]" (json select "rx_r" (concat "/[" [i] "]/invoice")); -> "[cust]" (json select "rx_r" (concat "/[" [i] "]/customer")); } { json iarray "rx_o" "/" (concat "{\"invoice\": \"" [inv] "\", \"customer\": \"" [cust] "\", \"item\": \"" (json select "rx_r" (concat "/[" [i] "]/item")) "\", \"qty\": " (json select "rx_r" (concat "/[" [i] "]/qty")) ", \"price\": " (json select "rx_r" (concat "/[" [i] "]/price")) "}") "OBJ"; }; }; -> "[res]" (csv from_json (json doc "rx_o") "{\"separator\": \";\", \"decimal\": \",\", \"columns\": [\"invoice\", \"customer\", \"item\", \"qty\", \"price\"]}"); json unload "rx_r"; json unload "rx_o"; [res]
invoice;customer;item;qty;price F0000104;ACME;CABLE;2;10,5 F0000104;ACME;SWITCH;1;49 F0000105;BOLT INC;CABLE;10;10,5
- decimals 2: 0001050 is read 10.50; a sign at the end (sign trailing) or at the start is read too.
- For an EBCDIC file without line breaks: {"encoding": "Cp1047", "record_length": 80} with fixed read.
Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
A partner sends the full customer list every day. Instead of reloading everything, the table is created once from the data (types inferred), then each day the new extract is compared with the previous one by the customer code: only the added and changed customers are upserted and the removed ones are deleted.
-> "[yesterday]" "[{\"code\": \"C001\", \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"credit_limit\": 5000}, {\"code\": \"C002\", \"name\": \"Martin\", \"city\": \"Saint-Pierre\", \"credit_limit\": 1500}, {\"code\": \"C003\", \"name\": \"Hoarau & Fils\", \"city\": \"Le Port\", \"credit_limit\": 2500}, {\"code\": \"C004\", \"name\": \"Grondin\", \"city\": \"Cilaos\", \"credit_limit\": 800}]"; -> "[today]" "[{\"code\": \"C001\", \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"credit_limit\": \"5000.00\"}, {\"code\": \"C002\", \"name\": \"Martin\", \"city\": \"Saint-Paul\", \"credit_limit\": 1500}, {\"code\": \"C004\", \"name\": \"Grondin\", \"city\": \"Cilaos\", \"credit_limit\": 1200}, {\"code\": \"C005\", \"name\": \"Payet Transport\", \"city\": \"Saint-Louis\", \"credit_limit\": 3000}]"; sql connect "rx_db" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:rx_sync\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; json load "rx_t" (sql create_table "rx_db" "customer" [yesterday] "{\"primary_key\": [\"code\"]}"); sql bulk_insert "rx_db" "customer" [yesterday]; #Next day: what changed since yesterday; json load "rx_d" (table diff [yesterday] [today] "[\"code\"]" "{\"output\": \"upsert\"}"); json load "rx_u" (sql bulk_upsert "rx_db" "customer" (json select "rx_d" "/upsert") "[\"code\"]"); for (-> "[i]" 0) (< [i] (json count "rx_d" "/delete_keys")) (++ "[i]") { sql dml "rx_db" (concat "DELETE FROM customer WHERE code=" (sql encode (json select "rx_d" (concat "/delete_keys/[" [i] "]/code")))); }; -> "[res]" (concat "credit_limit typed " (json select "rx_t" "/columns/[3]/type") " | upserted " (json select "rx_u" "/rows") " (" (json select "rx_u" "/inserted") " new, " (json select "rx_u" "/updated") " updated), deleted " (json count "rx_d" "/delete_keys") " | table now: " (sql value "rx_db" "SELECT COUNT(*) FROM customer") " customers, C004 limit " (sql value "rx_db" "SELECT credit_limit FROM customer WHERE code='C004'")); sql disconnect "rx_db"; json unload "rx_t"; json unload "rx_d"; json unload "rx_u"; [res]
credit_limit typed INTEGER | upserted 3 (1 new, 2 updated), deleted 1 | table now: 4 customers, C004 limit 1200
- C001 is unchanged: "5000.00" and 5000 are the same value (loose_types), so it is not sent again.
- With {"output": "counts"} table diff only gives the numbers: useful to alert when a daily extract changes too much (a truncated file).
- sql infer_schema gives the same DDL without a connection, to review it before creating the table.
Generate a Word document from a template: fields, a repeated table row, a condition
The template is an ordinary .docx written with {{name}} fields, so that a business user can change it. Here it is created by docx create to keep the example complete; in production it is a file made in Word.
if (equal (file exist "tmp/rec_docx") "0") {file mkdir "tmp/rec_docx";}; docx create "tmp/rec_docx/tpl.docx" "[{\"type\": \"heading\", \"level\": 1, \"text\": \"Quote {{number}}\"}, {\"type\": \"paragraph\", \"text\": \"For {{customer.name}}, {{customer.city}}\"}, {\"type\": \"table\", \"header\": [\"Item\", \"Quantity\", \"Price\"], \"rows\": [[\"{{#lines}}{{item}}\", \"{{qty}}\", \"{{price}}{{/lines}}\"]]}, {\"type\": \"paragraph\", \"text\": \"{{?urgent}}Urgent: delivery within 48 hours{{/urgent}}\"}]"; -> "[fields]" (docx placeholders "tmp/rec_docx/tpl.docx"); json load "rec_f" (docx fill "tmp/rec_docx/tpl.docx" "tmp/rec_docx/quote.docx" "{\"number\": \"D-204\", \"customer\": {\"name\": \"Hoarau SARL\", \"city\": \"Saint-Pierre\"}, \"urgent\": true, \"lines\": [{\"item\": \"Cable\", \"qty\": 12, \"price\": 4.5}, {\"item\": \"Socket\", \"qty\": 3, \"price\": 18}]}"); -> "[text]" (docx to_text "tmp/rec_docx/quote.docx"); -> "[unresolved]" (json select "rec_f" "/unresolved"); json unload "rec_f"; file delete "tmp/rec_docx"; -> "[res]" (concat "unresolved: " [unresolved] (string char 10) [text]); [res]
unresolved: [] Quote D-204 For Hoarau SARL, Saint-Pierre Item Quantity Price Cable 12 4.5 Socket 3 18 Urgent: delivery within 48 hours
- Placeholders split by Word over several runs are found, and the formatting of the first run is kept.
- For a PDF, the text can go through markdown to_html and the PDF functions; docx to_markdown also gives the structure.
One Markdown report published in HTML, in Word and in plain text
A flow writes its report once, in Markdown, then publishes it where it is needed. The safe HTML mode escapes raw HTML, so that a value coming from a source system cannot inject a script.
if (equal (file exist "tmp/rec_md") "0") {file mkdir "tmp/rec_md";}; -> "[nl]" (string char 10); -> "[md]" (concat "# Daily report" [nl] [nl] "**3** orders, total **418.50** EUR" [nl] [nl] "| Shop | Amount |" [nl] "|---|---|" [nl] "| Saint-Denis | 300 |" [nl] "| Le Tampon | 118.5 |" [nl] [nl] "Note: <script>alert(1)</script>"); -> "[html]" (markdown to_html [md]); markdown to_docx [md] "tmp/rec_md/report.docx"; json load "rec_i" (docx info "tmp/rec_md/report.docx"); -> "[tables]" (json select "rec_i" "/tables"); json unload "rec_i"; file delete "tmp/rec_md"; -> "[res]" (concat "script executable in the HTML: " (string contains [html] "<script>") " | HTML table: " (string contains [html] "<table>") " | tables in the Word file: " [tables] [nl] (markdown to_text [md])); [res]
script executable in the HTML: 0 | HTML table: 1 | tables in the Word file: 1 Daily report 3 orders, total 418.50 EUR Shop Amount Saint-Denis 300 Le Tampon 118.5 Note: alert(1)
- markdown from_html goes the other way, for a page read with html load_from_url.
Send an appointment and a contact that open in every calendar and phone
An .ics file and a vCard are plain text with strict rules (folded lines, escaping). The event is created, then read back to check it; the contact can also become a QR code to print on a business card.
if (equal (file exist "tmp/rec_cal") "0") {file mkdir "tmp/rec_cal";}; -> "[ics]" (ics create "[{\"uid\": \"visit-204@acme.re\", \"summary\": \"Site visit; bring the plans, please\", \"start\": \"2026-11-16T14:00:00\", \"end\": \"2026-11-16T15:30:00\", \"timezone\": \"Indian/Reunion\", \"location\": \"12 rue de Paris, Saint-Denis\", \"alarm_minutes_before\": 60}]" "{\"now\": \"2026-11-04T08:00:00Z\", \"calendar_name\": \"Chantier\"}"); json load "rec_ev" (ics parse [ics]); -> "[summary]" (json select "rec_ev" "/[0]/summary"); json unload "rec_ev"; -> "[card]" (vcard create "{\"fn\": \"Marie Grondin\", \"org\": \"ACME Reunion\", \"title\": \"Project manager\", \"tel\": \"+262 692 00 00 00\", \"email\": \"marie@acme.re\"}"); qr create [card] "tmp/rec_cal/card.png"; json load "rec_r" (qr read "tmp/rec_cal/card.png"); -> "[same]" (equal (json select "rec_r" "/text") [card]); json unload "rec_r"; file delete "tmp/rec_cal"; -> "[res]" (concat "event: " [summary] " | escaped in the file: " (string contains [ics] "plans\, please") " | card scanned correctly: " [same]); [res]
event: Site visit; bring the plans, please | escaped in the file: 1 | card scanned correctly: 1
- Attach the text as a .ics file to a mail: the invitation opens in Outlook, Gmail or an iPhone.
Import an EDIFACT purchase order and answer with an acknowledgement
A large customer sends its orders in EDIFACT. The interchange is checked first (counts, control references), then read as business rows ready for a table, and acknowledged. The partner guide of each customer must still be checked: the codes it uses vary.
-> "[order]" "UNB+UNOC:3+1234567890123:14+3210987654321:14+200315:1030+ICR1'UNH+M1+ORDERS:D:96A:UN'BGM+220+PO-12345+9'DTM+137:20200315:102'NAD+BY+1234567890123::9'NAD+SU+3210987654321::9'CUX+2:EUR:9'LIN+1++4000862141404:EN'IMD+F++:::BLUE WIDGET ?+ PLUS'QTY+21:48:PCE'PRI+AAA:12.50'UNS+S'CNT+2:1'UNT+13+M1'UNZ+1+ICR1'"; json load "rec_c" (edifact check [order]); -> "[valid]" (json select "rec_c" "/valid"); json unload "rec_c"; json load "rec_r" (edifact to_rows [order]); -> "[po]" (json select "rec_r" "/messages/[0]/header/document_number"); -> "[item]" (json select "rec_r" "/messages/[0]/lines/[0]/item_id"); json unload "rec_r"; -> "[ack]" (edifact contrl [order] "{\"date\": \"261104\", \"time\": \"0800\", \"control_ref\": \"ACK1\"}"); -> "[res]" (concat "valid: " [valid] " | acknowledgement starts with: " (string substr [ack] 0 3) " | order " [po] " | first item " [item]); [res]
valid: true | acknowledgement starts with: UNB | order PO-12345 | first item 4000862141404
- x12 check, x12 to_rows and x12 ack_997 do the same for the American X12 format (850, 856, 810).
Read a laboratory result in HL7 v2 and answer the sender
Hospital systems exchange pipe-separated HL7 v2 messages. A field is read by its usual path (PID-5.1, OBX(2)-5), results become rows, and the ACK tells the sender that the message was received. All the data here are fictional.
-> "[cr]" (string char 13); -> "[msg]" (concat "MSH|^~\&|LABAPP|LAB|HIS|HOSP|20261104101530||ORU^R01|MSG42|P|2.5" [cr] "PID|1||900001^^^HOSP^MR||TESTPATIENT^ALICE||19800102|F" [cr] "OBR|1|||GLU^Glucose" [cr] "OBX|1|NM|GLU^Glucose||5.4|mmol/L|3.9-6.1|N|||F" [cr] "OBX|2|NM|K^Potassium||6.2|mmol/L|3.5-5.1|H|||F"); -> "[family]" (hl7 get [msg] "PID-5.1"); -> "[second]" (hl7 get [msg] "OBX(2)-5"); -> "[flag]" (hl7 get [msg] "OBX(2)-8"); -> "[ack]" (hl7 ack [msg] "{\"now\": \"2026-11-04T08:00:00Z\", \"control_id\": \"ACK42\"}"); -> "[res]" (concat "patient " [family] " | second result " [second] " flag " [flag] " | ack " (hl7 get [ack] "MSA-1") " for " (hl7 get [ack] "MSA-2")); [res]
patient TESTPATIENT | second result 6.2 flag H | ack AA for MSG42
- hl7 send delivers a message over MLLP and returns the ACK; patient data must go through TLS or a VPN.
- fhir get, fhir search and fhir create speak to the FHIR REST servers. Neither is a certified medical interface.
Import a JSON Lines export and report the bad lines
An export of a few million events always contains a truncated or corrupted line. Stopping the whole import for one line is rarely what you want, and silently losing lines is worse. Here the good lines are kept, with their big numbers exact, and each bad line is reported with its real line number.
-> "[nl]" (string char 10); if (equal (file exist "tmp/rec_api") "0") {file mkdir "tmp/rec_api";}; -> "[export]" (concat "{\"id\": 1, \"amount\": 12.50, \"ref\": 1}" [nl] "{\"id\": 2, \"amount\": 7.00, \"ref\": 2}" [nl] "" [nl] "{\"id\": 3, \"amount\": " [nl] "{\"id\": 4, \"amount\": 1.25, \"ref\": 4}"); json load "rec_j" (jsonl to_rows [export] "{\"skip_bad\": true}"); -> "[good]" (json select "rec_j" "/count"); -> "[bad]" (json select "rec_j" "/bad_count"); -> "[line]" (json select "rec_j" "/bad/[0]/line"); json unload "rec_j"; -> "[clean]" (jsonl to_rows (concat "{\"id\": 1, \"ref\": 12345678901234567890}" [nl] "{\"id\": 2, \"ref\": 2}")); jsonl write "tmp/rec_api/clean.jsonl" [clean]; jsonl write "tmp/rec_api/clean.jsonl" "[{\"id\": 4, \"ref\": 4}]" "{\"append\": true}"; -> "[back]" (jsonl read "tmp/rec_api/clean.jsonl"); -> "[res]" (concat "good lines: " [good] " | bad lines: " [bad] " | first bad line: " [line] " | file: " [back]); [res]
good lines: 3 | bad lines: 1 | first bad line: 4 | file: [{"id": 1, "ref": 12345678901234567890}, {"id": 2, "ref": 2}, {"id": 4, "ref": 4}]- Without skip_bad, the first bad line stops with an error that gives its number: the right choice when a file must be complete.
- offset and limit read a big file page by page; max_lines and max_bytes protect the memory of the server.
Write an order confirmation from a template, with the customer data escaped
A confirmation mail mixes a fixed layout and data that comes from outside: a customer name can hold < or &. With escape html every value is safe in the page, the lines are repeated by a loop, and the amounts are computed in exact decimals. A template can only read its data: it cannot run anything on the server, so it can be stored in a table and edited by a business user.
-> "[nl]" (string char 10); -> "[tpl]" (concat "<h1>Order {{number}} for {{customer}}</h1>" [nl] "{{#each lines as l}}" [nl] "<p>{{loop.index}}. {{l.name}}: {{l.qty * l.price | number:2,',',' '}} EUR</p>" [nl] "{{/each}}" [nl] "{{#set total = 0}}{{#each lines as l}}{{#set total = total + l.qty * l.price}}{{/each}}" "<p>Total: {{total | number:2,',',' '}} EUR{{#if total > 1000}}, free delivery{{/if}}</p>"); -> "[data]" "{\"number\": \"C-2026-117\", \"customer\": \"Dupont & Fils <SARL>\", \"lines\": [{\"name\": \"Valve DN50\", \"qty\": 12, \"price\": \"84.10\"}, {\"name\": \"Seal kit\", \"qty\": 3, \"price\": \"19.90\"}]}"; -> "[res]" (template render [tpl] [data] "{\"escape\": \"html\"}"); [res]
<h1>Order C-2026-117 for Dupont & Fils <SARL></h1> <p>1. Valve DN50: 1 009,20 EUR</p> <p>2. Seal kit: 59,70 EUR</p> <p>Total: 1 068,90 EUR, free delivery</p>
- template render_file reads the layout from a file. A loop that never ends, or a text that grows without limit, stops with a clear error: see the parameters timeout_ms, max_steps and max_output.
Read a GS1 logistic label and write the next one
A scanner gives one line for a GS1-128 or DataMatrix code: the product, its expiry date, a weight with its decimals inside the identifier, a batch. gs1 parse cuts it and checks it, with exact decimals. The other way, an SSCC gets its check digit and a new label is written, with separators only where the specification needs them.
json load "rec_g" (gs1 parse "]C1010950110153000317261231310300125010ABC123" "{\"now\": \"2026-11-05\"}"); -> "[valid]" (json select "rec_g" "/valid"); -> "[gtin]" (json select "rec_g" "/elements/[0]/value"); -> "[expiry]" (json select "rec_g" "/elements/[1]/date"); -> "[kg]" (json select "rec_g" "/elements/[2]/decimal"); -> "[lot]" (json select "rec_g" "/elements/[3]/value"); json unload "rec_g"; -> "[base]" "10614141123456789"; -> "[sscc]" (concat [base] (gs1 check_digit [base])); -> "[ok]" (type is_sscc [sscc]); json load "rec_b" (gs1 build (concat "{\"00\": \"" [sscc] "\", \"400\": \"PO-77\"}")); -> "[label]" (json select "rec_b" "/element_string"); json unload "rec_b"; -> "[res]" (concat "valid: " [valid] " | GTIN " [gtin] " | expiry " [expiry] " | " [kg] " kg | lot " [lot] " | SSCC check: " [ok] " | next label: " [label]); [res]
valid: true | GTIN 09501101530003 | expiry 2026-12-31 | 1.250 kg | lot ABC123 | SSCC check: 1 | next label: (00)106141411234567897(400)PO-77
- barcode create writes the code as an image. A bad check digit or a date that does not exist gives valid false and a line in errors, not a failure of the script.
Turn the registers of an energy meter into numbers, and prepare a setpoint
A Modbus device only gives 16-bit registers. A float takes two registers, and each manufacturer chooses the order of the words. The registers here were recorded from a meter, so this runs without any device; on site, modbus read_holding with the same type and word_order gives the same numbers directly. Writing to a real device can start a machine: the setpoint is only computed here.
-> "[regs]" "[0, 16968, 0, 17096]"; json load "rec_mb" (modbus decode [regs] "float32" "{\"word_order\": \"little\"}"); -> "[voltage]" (json select "rec_mb" "/[0]"); -> "[current]" (json select "rec_mb" "/[1]"); json unload "rec_mb"; json load "rec_bad" (modbus decode [regs] "float32"); -> "[wrong]" (json select "rec_bad" "/[0]"); json unload "rec_bad"; -> "[setpoint]" (modbus encode "21.5" "float32" "{\"word_order\": \"little\"}"); -> "[res]" (concat "voltage " [voltage] " | current " [current] " | with the wrong word order: " [wrong] " | setpoint registers " [setpoint]); [res]
voltage 50.0 | current 100.0 | with the wrong word order: 0.000000000000000000000000000000000000000023777 | setpoint registers [0, 16812]
- A value that looks absurd, like the wrong one here, is the usual sign of a wrong word_order: compare with the display of the device.
- modbus batch reads several ranges on one connection. Keep Modbus on a closed network: it has no authentication.
Export orders to an Avro file for a data pipeline, then check the file
Data pipelines (Kafka Connect, Spark, Hadoop) exchange rows in Avro files, which carry their schema. The schema protects the file: a wrong type or a decimal that would be rounded is refused before anything is written, with the row and the field in the message.
if (equal (file exist "tmp/rec_avro") "0") {file mkdir "tmp/rec_avro";}; -> "[schema]" "{\"type\": \"record\", \"name\": \"Order\", \"namespace\": \"com.acme\", \"fields\": [{\"name\": \"id\", \"type\": \"long\"}, {\"name\": \"shop\", \"type\": \"string\"}, {\"name\": \"state\", \"type\": {\"type\": \"enum\", \"name\": \"State\", \"symbols\": [\"NEW\", \"PAID\"]}}, {\"name\": \"day\", \"type\": {\"type\": \"int\", \"logicalType\": \"date\"}}, {\"name\": \"amount\", \"type\": {\"type\": \"bytes\", \"logicalType\": \"decimal\", \"precision\": 10, \"scale\": 2}}, {\"name\": \"note\", \"type\": [\"null\", \"string\"], \"default\": null}]}"; -> "[rows]" "[{\"id\": 1, \"shop\": \"Saint-Denis\", \"state\": \"PAID\", \"day\": \"2026-11-05\", \"amount\": \"120.50\", \"note\": \"first\"}, {\"id\": 2, \"shop\": \"Le Tampon\", \"state\": \"NEW\", \"day\": \"2026-11-06\", \"amount\": \"7.00\"}]"; avro write "tmp/rec_avro/orders.avro" [schema] [rows] "{\"codec\": \"deflate\", \"overwrite\": true}"; json load "rec_av" (avro schema "tmp/rec_avro/orders.avro"); -> "[count]" (json select "rec_av" "/rows"); -> "[codec]" (json select "rec_av" "/codec"); json unload "rec_av"; json load "rec_rows" (avro read "tmp/rec_avro/orders.avro" "{\"offset\": 1}"); -> "[amount]" (json select "rec_rows" "/[0]/amount"); json unload "rec_rows"; file delete "tmp/rec_avro/orders.avro"; -> "[res]" (concat "rows " [count] " | codec " [codec] " | amount of the second order " [amount]); [res]
rows 2 | codec deflate | amount of the second order 7.00
- The decimal comes back as the exact text 7.00, never as a rounded floating point number.
- For a table read by column, see parquet write. webdav put or s3 put can then deliver the file.
Build and read a Protocol Buffers message of a sensor, from the text of its .proto
Sensors and gRPC services often speak Protocol Buffers. The .proto text is enough: no code generation, no compiler. When the schema is lost, decode_raw still shows the numbers of the fields. The base64 form travels well in MQTT, Kafka or a database.
-> "[proto]" "syntax = \"proto3\"; message Measure { string sensor = 1; double value = 2; enum Unit { C = 0; F = 1; } Unit unit = 3; repeated int32 flags = 4; }"; -> "[bin]" (protobuf encode [proto] "Measure" "{\"sensor\": \"T1\", \"value\": 21.5, \"unit\": \"F\", \"flags\": [1, 2, 300]}"); json load "rec_pb" (protobuf decode [proto] "Measure" [bin]); -> "[unit]" (json select "rec_pb" "/unit"); -> "[value]" (json select "rec_pb" "/value"); json unload "rec_pb"; json load "rec_raw" (protobuf decode_raw [bin]); -> "[field1]" (json select "rec_raw" "/[0]/value"); json unload "rec_raw"; -> "[res]" (concat "base64 " [bin] " | value " [value] " " [unit] " | field 1 without the schema: " [field1]); [res]
base64 CgJUMREAAAAAAIA1QBgBIgQBAqwC | value 21.5 F | field 1 without the schema: T1
- A field that the .proto does not know is not lost at decoding: it comes back in _unknown.
Merge the news feeds of suppliers into one feed for the intranet
Suppliers and carriers publish their news in RSS or Atom. Read both formats into the same JSON shape, merge the items, and publish one Atom feed that the intranet or a feed reader follows. The feeds are given as text so that the recipe runs alone; on site, feed fetch reads them with their etag, so that an unchanged feed is not downloaded again.
-> "[rss]" "<rss version=\"2.0\"><channel><title>Supplier news</title><link>https://supplier.example.com/</link><description>Products</description><item><title>Price list 2027</title><link>https://supplier.example.com/prices-2027</link><guid isPermaLink=\"false\">p-2027</guid><pubDate>Mon, 09 Nov 2026 08:00:00 +0100</pubDate><description>New prices from January.</description></item><item><title>Holiday closing</title><link>https://supplier.example.com/closing</link><pubDate>Fri, 06 Nov 2026 12:00:00 GMT</pubDate></item></channel></rss>"; -> "[atom]" "<feed xmlns=\"http://www.w3.org/2005/Atom\" xml:base=\"https://carrier.example.net/\"><title>Carrier status</title><id>urn:carrier:status</id><updated>2026-11-10T06:00:00Z</updated><author><name>Carrier ops</name></author><entry><title>Strike on 12 November</title><id>tag:carrier.example.net,2026:77</id><link href=\"status/77\"/><updated>2026-11-10T06:00:00Z</updated><summary>Deliveries delayed by one day.</summary></entry></feed>"; json load "rec_a" (feed read [rss]); json load "rec_b" (feed read [atom]); -> "[items]" (array merge (json select "rec_a" "/items") (json select "rec_b" "/items")); -> "[link]" (json select "rec_b" "/items/[0]/link"); json unload "rec_a"; json unload "rec_b"; -> "[digest]" (feed write (concat "{\"title\": \"Partners digest\", \"link\": \"https://intranet.example.com/news\", \"authors\": [\"Purchasing\"], \"items\": " [items] "}") "{\"format\": \"atom\", \"self_url\": \"https://intranet.example.com/news.atom\"}"); json load "rec_d" (feed read [digest]); -> "[res]" (concat "items: " (json count "rec_d" "/items") " | newest date: " (json select "rec_d" "/feed/updated") " | first: " (json select "rec_d" "/items/[0]/title") " | relative link made absolute: " [link]); json unload "rec_d"; [res]
items: 3 | newest date: 2026-11-10T06:00:00Z | first: Price list 2027 | relative link made absolute: https://carrier.example.net/status/77
- The dates of RSS (Mon, 09 Nov 2026 08:00:00 +0100) and of Atom are both given in ISO UTC, so items can be sorted or filtered by date with the json functions.
- summary and content keep the HTML of the feed: clean it with the html functions before showing it in a page.
Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
A partner tool (a Python script, a mobile application) wants the catalogue as one SQLite file rather than an API. The JSON rows become a table whose column types are guessed from the values (price is forced to REAL), with sku as primary key; a list is stored as JSON text and a BLOB comes from base64. The schema is read back, the file is checked, and an online backup keeps a consistent copy even if the file is in use.
if (equal (file exist "tmp/rec_sqlite") "0") {file mkdir "tmp/rec_sqlite";}; -> "[db]" "tmp/rec_sqlite/catalogue.db"; -> "[items]" "[{\"sku\": \"P-100\", \"label\": \"Pompe immergée\", \"price\": 349.9, \"stock\": 4, \"updated\": \"2026-11-02\", \"tags\": [\"eau\", \"jardin\"]}, {\"sku\": \"P-200\", \"label\": \"Tuyau 25 m\", \"price\": 39, \"stock\": 12, \"updated\": \"2026-11-05\"}, {\"sku\": \"G-010\", \"label\": \"Gants\", \"price\": 7.5, \"stock\": null, \"updated\": \"2026-10-28\", \"photo\": {\"base64\": \"iVBORw0KGgo=\"}}]"; json load "rx_imp" (sqlite import_json [db] "item" [items] "{\"mode\": \"replace\", \"primary_key\": \"sku\", \"types\": {\"price\": \"REAL\"}}"); json load "rx_tab" (sqlite tables [db] "{\"counts\": true, \"sql\": false}"); json load "rx_chk" (sqlite integrity_check [db]); json load "rx_bk" (sqlite backup [db] "tmp/rec_sqlite/catalogue_copy.db" "{\"overwrite\": true}"); json load "rx_cp" (sqlite query "tmp/rec_sqlite/catalogue_copy.db" "SELECT sku, typeof(price) AS price_type, tags, length(photo) AS photo_bytes FROM item WHERE sku = ?" "[\"G-010\"]"); -> "[res]" (concat "rows " (json select "rx_imp" "/rows") " | table " (json select "rx_tab" "/[0]/name") " with " (json select "rx_tab" "/[0]/rows") " rows, column 3 " (json select "rx_tab" "/[0]/columns/[2]/name") " " (json select "rx_tab" "/[0]/columns/[2]/type") " | integrity ok: " (json select "rx_chk" "/ok") " | copy: " (json select "rx_cp" "/[0]/sku") " price " (json select "rx_cp" "/[0]/price_type") ", photo " (json select "rx_cp" "/[0]/photo_bytes") " bytes"); json unload "rx_imp"; json unload "rx_tab"; json unload "rx_chk"; json unload "rx_bk"; json unload "rx_cp"; [res]
rows 3 | table item with 3 rows, column 3 price REAL | integrity ok: true | copy: G-010 price real, photo 8 bytes
- In Python the file opens with sqlite3.connect("catalogue.db"): the integers, reals, texts, NULL and BLOB come back with their types.
- mode replace drops and creates the table again; append adds rows, and a duplicate sku then stops the whole load with the row number.
Review a configuration change as a diff, apply it elsewhere and merge two edits
A colleague changed the configuration of a connector: text diff shows the change as a unified diff (the format of diff -u and git), to send for review. Once approved, text patch applies the same diff to the copy of another server, which has one more line at the top: the hunk is found 1 line lower (offset 1). Meanwhile somebody else changed the host in his copy: text merge3 merges the two edits of the same base without a conflict, as the changed lines are far apart.
-> "[v1]" "host=db.local\nport=5432\npool=10\ntimeout=30\n"; -> "[v2]" "host=db.local\nport=5432\npool=20\ntimeout=30\nssl=on\n"; -> "[diff]" (text diff [v1] [v2] "{\"format\": \"unified\", \"from_label\": \"connector.conf\", \"to_label\": \"connector.conf\"}"); json load "rx_p" (text patch (concat "# server 2\n" [v1]) [diff] "{\"report\": true}"); json load "rx_m" (text merge3 [v1] [v2] "host=db2.local\nport=5432\npool=10\ntimeout=30\n"); -> "[res]" (concat "patched at line " (json select "rx_p" "/hunks/[0]/line") ", offset " (json select "rx_p" "/hunks/[0]/offset") " | merge clean " (json select "rx_m" "/clean") ", conflicts " (json select "rx_m" "/conflicts") " | merged: " (json select "rx_m" "/text") "diff: " [diff]); json unload "rx_p"; json unload "rx_m"; [res]
patched at line 2, offset 1 | merge clean true, conflicts 0 | merged: host=db2.local port=5432 pool=20 timeout=30 ssl=on diff: --- connector.conf +++ connector.conf @@ -1,4 +1,5 @@ host=db.local port=5432 -pool=10 +pool=20 timeout=30 +ssl=on
- When the same line was changed on both sides, text merge3 gives clean false and writes the two versions between <<<<<<< and >>>>>>>; favor ours or theirs chooses one side automatically.
- A hunk that does not match the text is an error that names the line: nothing is changed, so a script can stop there and ask a person.
Turn a country typed by a user into its ISO codes, calling code, currency and EU status
A form gives the country as free text, in French or English, with or without accents: "cote d ivoire", "Allemagne", "usa". country find returns the best match first, with its ISO 3166 codes (alpha-2 for phone parse and date holidays, alpha-3 for some customs and bank formats), its currency, its calling code and whether it is in the EU and in the euro area. The membership list is embedded with the date it was checked.
json load "rx_ci" (country find "cote d ivoire"); json load "rx_de" (country find "Allemagne" "{\"exact\": true}"); json load "rx_bg" (country info "BG"); json load "rx_euro" (country list "{\"euro_area\": true}"); -> "[res]" (concat (json select "rx_ci" "/[0]/name_fr") " = " (json select "rx_ci" "/[0]/alpha2") "/" (json select "rx_ci" "/[0]/alpha3") ", currency " (json select "rx_ci" "/[0]/currency") ", calling code " (json select "rx_ci" "/[0]/calling_code") " | Allemagne = " (json select "rx_de" "/[0]/alpha2") ", in the EU since " (json select "rx_de" "/[0]/eu_since") " | Bulgaria: currency " (json select "rx_bg" "/currency") ", euro since " (json select "rx_bg" "/euro_since") " | euro area: " (json count "rx_euro" "/") " countries (checked " (json select "rx_bg" "/membership_checked") ")"); json unload "rx_ci"; json unload "rx_de"; json unload "rx_bg"; json unload "rx_euro"; [res]
Côte d’Ivoire = CI/CIV, currency XOF, calling code +225 | Allemagne = DE, in the EU since 1958 | Bulgaria: currency EUR, euro since 2026 | euro area: 21 countries (checked 2026-11-10)
- country info takes alpha-2, alpha-3 or the numeric code (250, or 40 for 040); a name goes to country find.
- The French overseas departments (RE, GP, MQ, GF, YT) have their own ISO code and eu false: they are in the EU as part of France (FR).
Run an import job once at a time and process each message only once
A job reads messages from a queue every minute; a slow run must not overlap the next one, and a message delivered twice (at-least-once delivery) must not be booked twice. cache lock gives a named lock with a lease: a second run gets an empty token and stops at once; the lock is released by cache unlock, at the end of the lease, or when the session ends. cache add stores the id of a message only if it is not there yet, atomically: it returns 1 for a new message and 0 for a duplicate. The cache of the ids has evict false, so no id is pushed out by other caches under memory pressure. Counters with cache incr give the totals of the run.
cache config "rec.inbox" "{\"evict\": false, \"max_bytes\": 1048576, \"default_ttl\": 86400}"; cache clear "rec.inbox"; -> "[token]" (cache lock "rec.job.inbox" "120"); -> "[second]" (cache lock "rec.job.inbox" "120"); -> "[msgs]" "[{\"id\": \"m-101\", \"amount\": 40}, {\"id\": \"m-102\", \"amount\": 25}, {\"id\": \"m-101\", \"amount\": 40}, {\"id\": \"m-103\", \"amount\": 10}, {\"id\": \"m-102\", \"amount\": 25}]"; json load "rx_m" [msgs]; -> "[n]" (json count "rx_m" "/"); for (-> "[i]" 0) (< [i] [n]) (++ "[i]") { -> "[id]" (json select "rx_m" (concat "/[" [i] "]/id")); -> "[new]" (cache add "rec.inbox" (concat "seen:" [id]) "1" "86400"); if (equal [new] "1") {cache incr "rec.inbox" "processed"; cache incr "rec.inbox" "amount" (json select "rx_m" (concat "/[" [i] "]/amount"));}; if (equal [new] "0") {cache incr "rec.inbox" "duplicates";}; }; json unload "rx_m"; -> "[released]" (cache unlock "rec.job.inbox" [token]); json load "rx_l" (cache lock_info "rec.job.inbox"); -> "[res]" (concat "lock token: " (string length [token]) " characters | second run gets: '" [second] "' | processed " (cache get "rec.inbox" "processed" "0") ", duplicates " (cache get "rec.inbox" "duplicates" "0") ", amount " (cache get "rec.inbox" "amount" "0") " | ids kept: " (cache keys "rec.inbox" "seen:*") " | lock released: " [released] ", locked now: " (json select "rx_l" "/locked")); json unload "rx_l"; [res]
lock token: 32 characters | second run gets: '' | processed 3, duplicates 2, amount 75 | ids kept: ["seen:m-101", "seen:m-102", "seen:m-103"] | lock released: 1, locked now: false
- The lease (120 seconds here) must be longer than the slowest run; for a long job call cache lock_renew from time to time. A lock that is not released ends with its lease, or with the session that took it.
- The ids live in memory for one day: after a restart of the server they are gone. When a duplicate must never be booked, also keep a unique key in the database.
Detect the encoding of a legacy export and convert it to UTF-8 before loading it
An old accounting program exports its customers in a CSV file written in windows-1252, with Windows line breaks; the next tools of the chain expect UTF-8. Here the export is simulated by csv write with the encoding windows-1252. file detect_encoding finds the encoding with a confidence; when it is sure enough, file convert_encoding writes a UTF-8 copy with Unix line breaks (through a temporary file, so the target is never half written), and the check shows that the accents read back are the same. A low confidence stops the chain instead of loading wrong letters.
if (equal (file exist "tmp/rec_legacy_clients.csv") "1") {file delete "tmp/rec_legacy_clients.csv";}; if (equal (file exist "tmp/rec_legacy_clients_utf8.csv") "1") {file delete "tmp/rec_legacy_clients_utf8.csv";}; csv write "tmp/rec_legacy_clients.csv" "[{\"code\": \"C-001\", \"nom\": \"Hélène Grondin\", \"ville\": \"Saint-André\", \"solde\": \"125,40 €\"}, {\"code\": \"C-002\", \"nom\": \"Jérôme Técher\", \"ville\": \"Saint-Benoît\", \"solde\": \"-12,00 €\"}, {\"code\": \"C-003\", \"nom\": \"Françoise Hoarau\", \"ville\": \"L'Étang-Salé\", \"solde\": \"0,00 €\"}]" "{\"separator\": \";\", \"encoding\": \"windows-1252\", \"line_end\": \"crlf\"}"; json load "rl_det" (file detect_encoding "tmp/rec_legacy_clients.csv"); -> "[enc]" (json select "rl_det" "/encoding"); -> "[conf]" (json select "rl_det" "/confidence"); -> "[breaks]" (json select "rl_det" "/line_breaks"); json unload "rl_det"; if (< [conf] 0.8) { -> "[res]" (concat "stopped: the encoding is not sure (" [enc] ", " [conf] ")"); } { json load "rl_conv" (file convert_encoding "tmp/rec_legacy_clients.csv" "tmp/rec_legacy_clients_utf8.csv" [enc] "UTF-8" "{\"line_breaks\": \"lf\"}"); json load "rl_rows" (csv read "tmp/rec_legacy_clients_utf8.csv" "{\"separator\": \";\"}"); json load "rl_after" (file detect_encoding "tmp/rec_legacy_clients_utf8.csv"); -> "[res]" (concat "detected " [enc] " (confidence " [conf] ", line breaks " [breaks] ") | converted " (json select "rl_conv" "/bytes_in") " bytes to " (json select "rl_conv" "/bytes_out") " bytes, " (json select "rl_conv" "/lines") " lines | copy: " (json select "rl_after" "/encoding") ", line breaks " (json select "rl_after" "/line_breaks") " | rows read: " (json count "rl_rows" "/") ", last name: " (json select "rl_rows" "/[2]/nom") ", " (json select "rl_rows" "/[2]/ville")); json unload "rl_conv"; json unload "rl_rows"; json unload "rl_after"; }; if (equal (file exist "tmp/rec_legacy_clients.csv") "1") {file delete "tmp/rec_legacy_clients.csv";}; if (equal (file exist "tmp/rec_legacy_clients_utf8.csv") "1") {file delete "tmp/rec_legacy_clients_utf8.csv";}; [res]
detected windows-1252 (confidence 0.98, line breaks CRLF) | converted 152 bytes to 164 bytes, 4 lines | copy: UTF-8, line breaks LF | rows read: 3, last name: Françoise Hoarau, L'Étang-Salé
- With a short file of a few accented letters, several code pages give a plausible text: read the candidates of file detect_encoding, or restrict them with the parameter candidates (for example windows-1252 and IBM850 for a program that may come from DOS).
- file convert_encoding stops at the first character that the target cannot write (the euro sign in ISO-8859-1), with its line and column; unmappable replace writes a replacement and counts them.
Import a stock count typed in LibreOffice Calc, with a title line and bad quantities
Each warehouse sends its stock count as an .ods file typed by hand: a title on the first line, an empty line, the header on line 3, then the products, with empty lines and some mistakes. Here the received file is first made with ods write (no header of its own). ods read takes the names from line 3 (header_row) and skips the empty lines; each quantity is then checked (a number, not negative): the good lines are kept for the stock table, the bad ones are listed to be sent back to the warehouse.
-> "[file]" "tmp/rec_ods/stock_count.ods"; ods write [file] "[{\"a\": \"Stock count - Saint-Denis warehouse\"}, {}, {\"a\": \"product\", \"b\": \"qty\", \"c\": \"counted_on\"}, {\"a\": \"Rice 5 kg\", \"b\": 40, \"c\": \"2026-03-02\"}, {\"a\": \"Oil 1 L\", \"b\": \"twelve\", \"c\": \"2026-03-02\"}, {}, {\"a\": \"Sugar 1 kg\", \"b\": -3, \"c\": \"2026-03-03\"}, {\"a\": \"Coffee 250 g\", \"b\": 25, \"c\": \"2026-03-03\"}]" "{\"header\": false, \"date_columns\": [\"c\"]}"; json load "rx_c" (ods read [file] "{\"header_row\": 3}"); -> "[n]" (json count "rx_c" "/"); -> "[good]" 0; -> "[bad]" ""; for (-> "[i]" 0) (< [i] [n]) (++ "[i]") { -> "[p]" (json select "rx_c" (concat "/[" [i] "]/product")); -> "[qty]" (json select "rx_c" (concat "/[" [i] "]/qty")); -> "[ok]" (type is_number [qty]); if (equal [ok] "1") {if (< [qty] 0) {-> "[ok]" "0";};}; if (equal [ok] "1") {-> "[good]" (+ [good] 1);} {-> "[bad]" (concat [bad] [p] " (" [qty] "); ");}; }; -> "[res]" (concat [n] " lines read, " [good] " good, to send back: " [bad] "| first date: " (json select "rx_c" "/[0]/counted_on")); json unload "rx_c"; file delete [file]; [res]
4 lines read, 2 good, to send back: Oil 1 L (twelve); Sugar 1 kg (-3); | first date: 2026-03-02
- header_row gives the line of the names; the data starts on the next line (first_row to start later). The title line and the empty lines are not data.
- A quantity typed as text in LibreOffice (twelve, 12 kg) stays a text in the JSON: always check the type of a value typed by hand before using it.
Send the report of a nightly import to Telegram and Discord without breaking the message
The supplier names of the import come from data and may hold <, > or &, and an error text may hold @everyone. With title and fields, notify telegram builds an HTML message where every value is escaped, so a name such as Dupont & Fils <SAS> is shown as it is instead of breaking the message. notify discord sends an embed and always adds allowed_mentions with nothing allowed, so @everyone in a text pings nobody. dry_run shows the exact payload without sending.
-> "[supplier]" "Dupont & Fils <SAS>"; -> "[params]" (concat "{\"chat_id\": \"@ops_alerts\", \"title\": \"Nightly import\", \"fields\": {\"Rows\": 1200, \"Rejected\": 3, \"Last supplier\": \"" [supplier] "\"}, \"disable_notification\": true, \"dry_run\": true}"); json load "rx_tg" (notify telegram "3 rows rejected, see the log" [params]); -> "[html]" (json select "rx_tg" "/payload/text"); -> "[mode]" (json select "rx_tg" "/payload/parse_mode"); json unload "rx_tg"; json load "rx_dc" (notify discord "Import done @everyone" "{\"title\": \"Nightly import\", \"fields\": {\"Rows\": 1200, \"Rejected\": 3}, \"color\": \"#2EB67D\", \"dry_run\": true}"); -> "[res]" (concat "telegram " [mode] ", escaped name: " (string contains [html] "Dupont & Fils <SAS>") ", raw name left: " (string contains [html] "<SAS>") " | discord embed fields: " (json count "rx_dc" "/payload/embeds/[0]/fields") ", mentions allowed: " (json count "rx_dc" "/payload/allowed_mentions/parse") ", color " (json select "rx_dc" "/payload/embeds/[0]/color")); json unload "rx_dc"; [res]
telegram HTML, escaped name: 1, raw name left: 0 | discord embed fields: 2, mentions allowed: 0, color 3061373
- In production give token (Telegram, the bot token) or webhook_url (Discord): the name of a secret (secret set) of the server, never the secret itself.
- Without title and fields, the text is sent as it is: with parse_mode HTML or MarkdownV2 you must escape it yourself.
Give GPS points to a GIS team as a Shapefile in the local projection
The field application records WGS84 latitudes and longitudes, but the GIS team of the island works in RGR92 / UTM zone 40S (EPSG:2975), the official projection of La Reunion, and exchanges Shapefiles. The points are projected in meters, written as a Shapefile with its .prj, checked with shp info, then read back in WGS84 to prove that nothing moved.
-> "[sites]" "[{\"site\": \"Mairie de Saint-Denis\", \"visits\": 120, \"lat\": -20.8789, \"lon\": 55.4481}, {\"site\": \"Mairie de Saint-Pierre\", \"visits\": 85, \"lat\": -21.3393, \"lon\": 55.4781}, {\"site\": \"Mairie du Port\", \"visits\": 60, \"lat\": -20.9374, \"lon\": 55.2919}]"; -> "[utm]" (geo transform_rows [sites] "4326" "2975"); json load "rg_w" (shp write "tmp/rec_geox/sites.shp" [utm] "{\"x_field\": \"x\", \"y_field\": \"y\", \"crs\": \"2975\"}"); -> "[count]" (json select "rg_w" "/count"); -> "[files]" (json count "rg_w" "/files"); json unload "rg_w"; json load "rg_i" (shp info "tmp/rec_geox/sites.shp"); -> "[info]" (concat (json select "rg_i" "/shape_type") ", " (json select "rg_i" "/crs/epsg") " (" (json select "rg_i" "/crs/name") "), " (json count "rg_i" "/fields") " fields"); json unload "rg_i"; json load "rg_r" (shp read "tmp/rec_geox/sites.shp" "{\"format\": \"rows\", \"to_crs\": \"4326\", \"decimals\": 6}"); -> "[back]" (concat (json select "rg_r" "/[0]/site") ": " (json select "rg_r" "/[0]/wkt")); json unload "rg_r"; -> "[res]" (concat [count] " points in " [files] " files | " [info] " | back in WGS84: " [back]); [res]
3 points in 5 files | Point, EPSG:2975 (RGR92 / UTM zone 40S), 4 fields | back in WGS84: Mairie de Saint-Denis: POINT (55.4481 -20.8789)
- WKT is longitude first (POINT (55.4481 -20.8789)), like GeoJSON; geo transform takes the latitude first for a single point.
- The other overseas systems: Mayotte RGM04 / UTM 38S (4471), Antilles RGAF09 / UTM 20N (5490), Guyane RGFG95 / UTM 22N (2972), Saint-Pierre-et-Miquelon RGSPM06 / UTM 21N (4467). geo crs gives the area of use and the accuracy of each.
- An old Gauss-Laborde file of La Reunion (Reunion 1947 / TM Reunion, EPSG:3727) is read the same way with to_crs 2975.
Read an application log of your own format with a grok pattern
Many applications write their own log format. A grok pattern, as in Logstash, names the parts of a line with ready-made patterns (TIMESTAMP_ISO8601, LOGLEVEL, INT, NUMBER...) and gives typed fields: here the order number is an integer, the amount a decimal, the date ISO 8601. A line that does not match (the frame of a stack trace) is counted and shown with its number, never lost silently. The result rows can go to a table with sql bulk or to a CSV file.
-> "[nl]" (string char 10); -> "[log]" (concat "2026-11-03 10:00:00,123 INFO [main] OrderService - order=42 amount=129.90 saved in 15 ms" [nl] "2026-11-03 10:00:01,456 WARN [pool-1] PaymentClient - order=43 amount=59.00 retry 2 after timeout" [nl] "2026-11-03 10:00:02,789 ERROR [pool-1] PaymentClient - order=43 amount=59.00 refused by the bank" [nl] " at com.shop.PaymentClient.pay(PaymentClient.java:88)" [nl] "2026-11-03 10:00:03,001 INFO [main] OrderService - order=44 amount=12.50 saved in 9 ms"); json load "rg_l" (log parse [log] "{\"pattern\": \"^%{TIMESTAMP_ISO8601:time} %{LOGLEVEL:level} +\\[%{DATA:thread}\\] %{WORD:logger} - order=%{INT:order:int} amount=%{NUMBER:amount:float} %{GREEDYDATA:message}$\", \"add_line\": true}"); -> "[total]" 0; -> "[errors]" ""; for (-> "[i]" 0) (< [i] (json count "rg_l" "/rows")) (++ "[i]") { if (equal (json select "rg_l" (concat "/rows/[" [i] "]/level")) "INFO") {-> "[total]" (+ [total] (json select "rg_l" (concat "/rows/[" [i] "]/amount")));}; if (equal (json select "rg_l" (concat "/rows/[" [i] "]/level")) "ERROR") {-> "[errors]" (concat [errors] " order " (json select "rg_l" (concat "/rows/[" [i] "]/order")) " at " (json select "rg_l" (concat "/rows/[" [i] "]/time")));}; }; -> "[res]" (concat "rows " (json select "rg_l" "/parsed") " | not matched: line " (json select "rg_l" "/unparsed_lines/[0]/line") " | saved amount " [total] " | errors:" [errors]); json unload "rg_l"; [res]
rows 4 | not matched: line 4 | saved amount 142.4 | errors: order 43 at 2026-11-03T10:00:02.789+00:00
- log grok_patterns lists the ready-made patterns; definitions adds patterns of your own, for example {"ORDER": "ORD-[0-9]{6}"}.
- Each line has a time limit (line_timeout_ms): a pattern that would backtrack for ever on a strange line only makes that line unreadable.
Publish the sales of the month as a LibreOffice document, then read its table back
The sales rows (from a SQL query in real life) are grouped by shop, put into the table of a LibreOffice report and written as .odt for the managers. The same file is read back: its table as JSON rows, to check that the total of the document is the total of the data, and as Markdown for the intranet page.
if (equal (file exist "tmp/rec_odt_report") "0") {file mkdir "tmp/rec_odt_report";}; odt create "tmp/rec_odt_report/tpl.odt" "[{\"type\": \"heading\", \"level\": 1, \"text\": \"Sales of {{month}}\"}, {\"type\": \"paragraph\", \"text\": \"Total of the month: {{total}} EUR.\"}, {\"type\": \"table\", \"header\": [\"Shop\", \"Sales\", \"Amount\"], \"rows\": [[\"{{#shops}}{{shop}}\", \"{{sales}}\", \"{{amount}}{{/shops}}\"]], \"widths\": [70, 30, 40]}, {\"type\": \"paragraph\", \"text\": \"{{?note}}Note: {{note}}{{/note}}\"}]" "{\"created\": \"2026-11-04T08:00:00Z\"}"; -> "[sales]" "[{\"shop\": \"Saint-Denis\", \"amount\": 1250.5}, {\"shop\": \"Saint-Pierre\", \"amount\": 980}, {\"shop\": \"Saint-Denis\", \"amount\": 455.25}, {\"shop\": \"Le Port\", \"amount\": 310}, {\"shop\": \"Saint-Pierre\", \"amount\": 120.75}]"; -> "[shops]" (table group_by [sales] "[\"shop\"]" "{\"sales\": [\"count\"], \"amount\": [\"sum\", \"amount\"]}"); json load "rec_d" "{}"; json iobject "rec_d" "/" "month" "March 2026" STR; json iobject "rec_d" "/" "total" "3116.5" STR; json iobject "rec_d" "/" "note" "Le Port was closed for two days." STR; json iobject "rec_d" "/" "shops" [shops] ARRAY; json load "rec_f" (odt fill "tmp/rec_odt_report/tpl.odt" "tmp/rec_odt_report/march.odt" (json doc "rec_d")); -> "[unresolved]" (json count "rec_f" "/unresolved"); json unload "rec_f"; json unload "rec_d"; json load "rec_t" (odt tables "tmp/rec_odt_report/march.odt" "{\"header\": true}"); -> "[rows]" (json count "rec_t" "/[0]"); -> "[sum]" 0; for (-> "[i]" 0) (< [i] [rows]) (++ "[i]") {-> "[sum]" (+ [sum] (json select "rec_t" (concat "/[0]/[" [i] "]/Amount")));}; json unload "rec_t"; -> "[md]" (odt to_markdown "tmp/rec_odt_report/march.odt"); file delete "tmp/rec_odt_report"; -> "[res]" (concat "unresolved: " [unresolved] " | shops in the table: " [rows] " | sum of the table: " [sum] (string char 10) [md]); [res]
unresolved: 0 | shops in the table: 3 | sum of the table: 3116.5 # Sales of March 2026 Total of the month: 3116.5 EUR. | Shop | Sales | Amount | | --- | --- | --- | | Saint-Denis | 2 | 1705.75 | | Saint-Pierre | 2 | 1100.75 | | Le Port | 1 | 310 | Note: Le Port was closed for two days.
- odt tables gives the cells as text: a number written by the template comes back as text, + converts it.
- LibreOffice can turn the .odt into a PDF on a server: soffice --headless --convert-to pdf march.odt.
