Extraction query

The DuckDB SQL that reads ASCAP International's CSV files into the standard schema. {{file}} is replaced with the statement file's path at import time.

Extraction query

-- ASCAP International distribution export -> normalized schema
SELECT
  row_number() OVER ()                                          AS line_number,
  trim("Work Title")                                            AS song_title,
  upper(trim("Work Title"))                                     AS song_title_key,
  upper(trim("Licensor"))                                       AS source_name_key,
  trim(COALESCE(upper(trim("Revenue Class Code")), '') || ' ' ||
       COALESCE(upper(trim("Revenue Class Description")), '')) AS income_class_key,
  upper(trim("Type Of Right"))                                  AS right_type_key,
  upper(trim("Role Type"))                                      AS role_type_key,
  COALESCE(NULLIF(upper(trim("Territory")), ''),
           upper(trim("Country Name")))                         AS territory_raw,
  COALESCE(NULLIF(trim("Legal Earner Party Name"), ''),
           trim("Party Name"))                                  AS writer_name,
  TRY_CAST("Member Share" AS DECIMAL(9,6))                      AS share_percentage,
  TRY_CAST("$ Amount" AS DECIMAL(18,4))                         AS amount_raw,
  COALESCE(upper(trim("Adjustment Indicator")), '')
    IN ('Y', 'YES', 'T', 'TRUE')                                AS adjustment,
  COALESCE(TRY_CAST(trim("Performance Start Date") AS DATE),
           CAST(try_strptime(trim("Performance Start Date"), '%m-%d-%Y') AS DATE)) AS earning_date,
  'USD'                                                         AS currency,
  to_json(row)                                                  AS source_row
FROM read_csv('{{file}}', header = true, sample_size = -1, all_varchar = true) AS row;

Must emit line_number, amount_raw and source_row; every other column matching a statement_lines column maps automatically, anything else is ignored.

Raw row count query

Optional — needed only when the generic reader can't count the file's data rows (spreadsheets with preamble above the header, like Sony CS04). Must use the extraction query's exact reader clause and alias the count AS raw_count. Blank uses the built-in reader.

Last updated 07/12/2026

Cancel