Profiles / Documents & integration
IE

Integrator & ETL developer

Expose APIs, import partner files, send reports, and reshape JSON and CSV in memory.

37 recipes on this page · open in the interactive view

Recipes
  1. Publish a SQL table as a JSON web service
  2. Import a CSV from SFTP into a database, every morning
  3. Mail a PDF report built from live data
  4. Turn a partner CSV text into JSON objects
  5. Sort JSON orders and export them as CSV
  6. Incremental sync between two databases with a watermark
  7. One connection per environment and engine, checked without connecting
  8. Apply a partner's CSV delta to a price table with MERGE
  9. Nightly export pack: CSV, Excel and PDF in an outbox connection
  10. Nightly copy of an Oracle table into MySQL, committed by batches
  11. Check a job schedule
  12. Load a partner CSV file into a table: detection, typing, upsert and rejected rows
  13. From a REST API answer to an Excel report: nested orders flattened into lines
  14. Read a mainframe fixed width export with header, trailer and two record types
  15. Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
  16. Generate a Word document from a template: fields, a repeated table row, a condition
  17. One Markdown report published in HTML, in Word and in plain text
  18. Send an appointment and a contact that open in every calendar and phone
  19. Import an EDIFACT purchase order and answer with an acknowledgement
  20. Read a laboratory result in HL7 v2 and answer the sender
  21. Import a JSON Lines export and report the bad lines
  22. Write an order confirmation from a template, with the customer data escaped
  23. Read a GS1 logistic label and write the next one
  24. Turn the registers of an energy meter into numbers, and prepare a setpoint
  25. Export orders to an Avro file for a data pipeline, then check the file
  26. Build and read a Protocol Buffers message of a sensor, from the text of its .proto
  27. Merge the news feeds of suppliers into one feed for the intranet
  28. Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
  29. Review a configuration change as a diff, apply it elsewhere and merge two edits
  30. Turn a country typed by a user into its ISO codes, calling code, currency and EU status
  31. Run an import job once at a time and process each message only once
  32. Detect the encoding of a legacy export and convert it to UTF-8 before loading it
  33. Import a stock count typed in LibreOffice Calc, with a title line and bad quantities
  34. Send the report of a nightly import to Telegram and Discord without breaking the message
  35. Give GPS points to a GIS team as a Shapefile in the local projection
  36. Read an application log of your own format with a grok pattern
  37. Publish the sales of the month as a LibreOffice document, then read its table back
needs your systems

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.

Pipeline
Script
admin
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";
Result
This recipe uses your own connections (database, SFTP, SMTP...). Create them with the CM functions, then run it on your server.
Go further
  • 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 xml or sql to csv
  • Version it — Versioning keeps every change of the script
needs your systems

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.

Pipeline
Script
admin
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];

} "";
Result
This recipe uses your own connections (database, SFTP, SMTP...). Create them with the CM functions, then run it on your server.
Go further
needs your systems

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.

Pipeline
Script
admin
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";

} "";
Result
This recipe uses your own connections (database, SFTP, SMTP...). Create them with the CM functions, then run it on your server.
Go further
executed on MentDB

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.

Pipeline
Script
admin
#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";
Result
mentdb
[
  {
    "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"
  }
]
Go further
  • For a CSV file on disk, csv parse gives one variable per column directly
  • Reject bad lines: check the price with type is_decimal before inserting the object
executed on MentDB

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.

Pipeline
Script
admin
#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];
Result
mentdb
id,customer,amount
A-102,"Dupont, SARL",1250.5
A-104,L'Atelier du Bois,640
A-103,Martin,89.9
TOTAL,,1980.4
Go further
executed on MentDB

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.

Pipeline
Script
admin
#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];
Result
mentdb
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
Go further
  • 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_commit false and sql commit.
executed on MentDB

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.

Pipeline
Script
admin
#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_");
Result
mentdb
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
Go further
  • Generate the update script of one connection with cm generate_update to change its host.
  • Open the H2 bench with sql connect once the server is started.
  • Use cm show_scrud to see which connections can serve a SCRUD API.
executed on MentDB

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.

Pipeline
Script
admin
#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];
Result
mentdb
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
Go further
executed on MentDB

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.

Pipeline
Script
admin
#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];
Result
mentdb
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
Go further
  • Send the pack with sftp put to the accounting server.
  • Sign the zip with a SHA-256 manifest so the receiver can check it.
  • Name the files with date sysdate for a real nightly run.
needs your systems

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.

Pipeline
Script
admin
#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];
Result
This recipe uses your own connections (database, SFTP, SMTP...). Create them with the CM functions, then run it on your server.
Go further
  • Roll back with sql rollback and re-throw the error in a try block if an insert fails.
  • Run it at 02:00 with job add and a cron.
  • Check the totals of both sides with sql value before the commit.
executed on MentDB

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.

Pipeline
Script
admin
#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]
Result
mentdb
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
Go further
  • Day of week 1-5 = Monday to Friday; names are accepted too: mon-fri.
  • date business_days_fr counts the business days between two dates.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
[{"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}]
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
invoice;customer;item;qty;price
F0000104;ACME;CABLE;2;10,5
F0000104;ACME;SWITCH;1;49
F0000105;BOLT INC;CABLE;10;10,5
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
credit_limit typed INTEGER | upserted 3 (1 new, 2 updated), deleted 1 | table now: 4 customers, C004 limit 1200
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
unresolved: []
Quote D-204
For Hoarau SARL, Saint-Pierre
Item	Quantity	Price
Cable	12	4.5
Socket	3	18
Urgent: delivery within 48 hours
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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)
Go further
  • markdown from_html goes the other way, for a page read with html load_from_url.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
event: Site visit; bring the plans, please | escaped in the file: 1 | card scanned correctly: 1
Go further
  • Attach the text as a .ics file to a mail: the invitation opens in Outlook, Gmail or an iPhone.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
valid: true | acknowledgement starts with: UNB | order PO-12345 | first item 4000862141404
Go further
  • x12 check, x12 to_rows and x12 ack_997 do the same for the American X12 format (850, 856, 810).
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
patient TESTPATIENT | second result 6.2 flag H | ack AA for MSG42
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
good lines: 3 | bad lines: 1 | first bad line: 4 | file: [{"id": 1, "ref": 12345678901234567890}, {"id": 2, "ref": 2}, {"id": 4, "ref": 4}]
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
<h1>Order C-2026-117 for Dupont &amp; Fils &lt;SARL&gt;</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>
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
valid: true | GTIN 09501101530003 | expiry 2026-12-31 | 1.250 kg | lot ABC123 | SSCC check: 1 | next label: (00)106141411234567897(400)PO-77
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
voltage 50.0 | current 100.0 | with the wrong word order: 0.000000000000000000000000000000000000000023777 | setpoint registers [0, 16812]
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
rows 2 | codec deflate | amount of the second order 7.00
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
base64 CgJUMREAAAAAAIA1QBgBIgQBAqwC | value 21.5 F | field 1 without the schema: T1
Go further
  • A field that the .proto does not know is not lost at decoding: it comes back in _unknown.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
items: 3 | newest date: 2026-11-10T06:00:00Z | first: Price list 2027 | relative link made absolute: https://carrier.example.net/status/77
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
rows 3 | table item with 3 rows, column 3 price REAL | integrity ok: true | copy: G-010 price real, photo 8 bytes
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
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
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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)
Go further
  • 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).
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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é
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
4 lines read, 2 good, to send back: Oil 1 L (twelve); Sugar 1 kg (-3); | first date: 2026-03-02
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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 &amp; Fils &lt;SAS&gt;") ", 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]
Result
mentdb
telegram HTML, escaped name: 1, raw name left: 0 | discord embed fields: 2, mentions allowed: 0, color 3061373
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
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)
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
-> "[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]
Result
mentdb
rows 4 | not matched: line 4 | saved amount 142.4 | errors: order 43 at 2026-11-03T10:00:02.789+00:00
Go further
  • 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.
executed on MentDB

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.

Pipeline
Script
admin
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]
Result
mentdb
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.
Go further
  • 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.

Also in Documents & integration

PD
PDF & document automation specialist
Generate invoices and reports, merge and split dossiers, watermark, protect, fill forms and extract text.
11 recipes
ER
Excel reporting analyst
Build xlsx and xls workbooks with formulas and formats, evaluate them on the server and read them back.
11 recipes
XH
XML, HTML & Word integrator
Parse and transform XML with XPath, scrape HTML with selectors, convert to JSON and fill Word templates.
14 recipes
FA
Files & archives manager
Write and read files with streams, rotate logs, archive in zip, tar.gz or bz2, convert encodings and images.
14 recipes
FT
File transfer & remote systems engineer
Exchange files over SFTP, FTP, FTPS and Windows shares, run remote commands over SSH, call other MentDB servers and watch folders.
11 recipes
AD
API & database integrator
Call REST and SOAP services, take payments, send and read mail, and move data between SQL and MongoDB.
13 recipes