lumnik. / scenarios All scenarios · Français

Every line of the Excel export gets its region and its client back

Goal Read an Excel report with merged cells as a table, one row per sale.

Sales send an Excel report every month. One group per client, region and client in merged cells, a subtotal under each group. As CSV, a merged cell keeps its value on the first row only.

ventes_2026-09.csv
Région;Client;Date;Pièce;Article;Quantité;Montant HT
Nord;Boulangerie des Tilleuls;01/09/2026;FA26090001;Farine T65 25 kg;1;18,40
;;04/09/2026;FA26090002;Sucre semoule 10 kg;8;103,20
;;07/09/2026;FA26090003;Beurre AOP 5 kg;3;124,50
;;10/09/2026;FA26090004;Levure fraîche 1 kg;10;32,00
;;13/09/2026;FA26090005;Chocolat noir 64 % 5 kg;5;290,00
;;16/09/2026;FA26090006;Crème 35 % 5 L;12;273,00
;;19/09/2026;FA26090007;Œufs plein air x180;7;277,20
;;22/09/2026;FA26090008;Amandes effilées 1 kg;2;32,60
;;25/09/2026;FA26090009;Farine T65 25 kg;9;165,60
;;28/09/2026;FA26090010;Sucre semoule 10 kg;4;51,60
;;03/09/2026;FA26090011;Beurre AOP 5 kg;11;456,50
;;06/09/2026;FA26090012;Levure fraîche 1 kg;6;19,20
;;09/09/2026;FA26090013;Chocolat noir 64 % 5 kg;1;58,00
;;12/09/2026;FA26090014;Crème 35 % 5 L;8;182,00
;;15/09/2026;FA26090015;Œufs plein air x180;3;118,80
;;18/09/2026;FA26090016;Amandes effilées 1 kg;10;163,00
;;21/09/2026;FA26090017;Farine T65 25 kg;5;92,00
;;24/09/2026;FA26090018;Sucre semoule 10 kg;12;154,80
;;27/09/2026;FA26090019;Beurre AOP 5 kg;7;290,50

… 1078 more lines

manifests/20-sales-line.yaml
kind: Entityname: sales_line_blabel: Ligne de ventedescription: "One sale line of a monthly Excel export: its region and client, its date, invoice, article, quantity and amount."match: [piece]scopes: [sales-b]columns:  piece: { label: "Pièce" }  month: { label: "Mois" }  region: { label: "Région" }  client: { label: "Client" }  sale_date: { label: "Date" }  article: { label: "Article" }  quantity: { label: "Quantité" }  amount: { label: "Montant HT" }sources:  - table: t_scenario_excel_b    map: { piece: piece, month: month, region: region, client: client, sale_date: sale_date, article: article, quantity: quantity, amount: amount }
ventes_2026-10.csv
Région;Client;Date;Pièce;Article;Quantité;Montant HT
;;01/10/2026;FA26100001;Farine T65 25 kg;1;18,40
;;04/10/2026;FA26100002;Sucre semoule 10 kg;8;103,20
;;07/10/2026;FA26100003;Beurre AOP 5 kg;3;124,50
;Total Boulangerie des Tilleuls;;;;12;246,10
Est;Fromagerie du Val;02/10/2026;FA26100004;Sucre semoule 10 kg;2;25,80
;;05/10/2026;FA26100005;Beurre AOP 5 kg;9;373,50
;;08/10/2026;FA26100006;Levure fraîche 1 kg;4;12,80
;;11/10/2026;FA26100007;Chocolat noir 64 % 5 kg;11;638,00
;;14/10/2026;FA26100008;Crème 35 % 5 L;6;136,50
;Total Fromagerie du Val;;;;32;1 186,60
Ouest;Épicerie Lemoine;03/10/2026;FA26100009;Beurre AOP 5 kg;3;124,50
;;06/10/2026;FA26100010;Levure fraîche 1 kg;10;32,00
;;09/10/2026;FA26100011;Chocolat noir 64 % 5 kg;5;290,00
;;12/10/2026;FA26100012;Crème 35 % 5 L;12;273,00
;Total Épicerie Lemoine;;;;30;719,50

Limits

  • fill-down follows the file's order: sort-by beside it is refused.
  • A line dropped by filter-where carries nothing: a group's first row must not be filtered out.
  • Synthetic files, fictitious clients.

Sales · Excel

The sales report is an Excel file.

Made for the eye, not for a table.

Two months, 1097 sale lines

The problem

Merged cells come out empty.

  • As CSV, only the first row keeps the client.
  • The next ones are empty.

What is not changed

The report stays as it is.

  • The same export, every month.
  • Nobody touches it up by hand.

The trap

A group cut in two.

  • The hub reads in chunks of 500 rows.
  • One client straddles two.

What had to be written

fill-down: { columns: [region, client] }

One line.

  • The value flows down its group.
  • It crosses from one chunk to the next.
  • A 50-line scenario, two manifests.

The proof

13,603.90, the subtotal found again.

  • 84 rows, all on the right client.
  • The 15 subtotals counted apart.

The limit

The file's order matters.

  • fill-down follows the rows in order.
  • A sort beside it is refused.

Try it

The scenario replays locally.

Every step is published with its output.

This scenario runs on lumnik Open, free and open source.

Try lumnik Open →