Examples

Example DuckDB SQL transformations for messy files.

These examples are intentionally boring. That is the point. Bendit is for real-world file cleanup work.

Download sample CSV Download sample Parquet

Clean names, dates and amounts

select
  trim(customer_name) as customer_name,
  try_cast(order_date as date) as order_date,
  replace(amount, ',', '.')::decimal(18,2) as amount
from orders
where customer_name is not null;

Find duplicate customer names

select
  lower(trim(customer_name)) as normalized_name,
  count(*) as count
from orders
group by normalized_name
having count(*) > 1
order by count desc;

Export a clean subset

select
  order_id,
  customer_name,
  order_date,
  amount
from clean_orders
where amount > 0
order by order_date;

Fix Excel's serial date numbers

select
  order_id,
  date '1899-12-30' + interval (excel_date_serial) day as order_date
from imported_excel;

Excel stores dates as a day count from 1899-12-30, leap-year bug included. This turns raw serial numbers back into real dates without touching the source file.

Standardize mixed date formats

select
  order_id,
  coalesce(
    try_cast(order_date as date),
    try_strptime(order_date, '%d-%m-%Y')::date,
    try_strptime(order_date, '%Y/%m/%d')::date
  ) as order_date
from orders;

Real exports mix ISO dates, European dd-mm-yyyy and slash-separated formats in the same column. Try each format in order and keep the first one that parses.

Keep only the latest row per customer

select * exclude (rn)
from (
  select *,
    row_number() over (
      partition by customer_name
      order by order_date desc
    ) as rn
  from orders
)
where rn = 1;

Common with repeated exports from the same source system — several rows per customer, only the newest one is current.

Join two files without a database

select
  o.order_id,
  o.customer_name,
  c.region
from orders o
left join customers c
  on lower(trim(o.customer_name)) = lower(trim(c.customer_name));

Import both files, then join them directly — no staging tables, no separate ETL step.