Extraction query

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

Extraction query

-- ASCAP Domestic 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("Music User"))                                     AS source_name_key,
  upper(trim("Performance Source/Broadcast Medium"))            AS income_class_key,
  upper(trim("Type Of Right"))                                  AS right_type_key,
  upper(trim("Role Type"))                                      AS role_type_key,
  upper(trim("Territory"))                                      AS territory_raw,
  trim("Composer Name")                                         AS writer_name,
  TRY_CAST("Number of Plays" AS DECIMAL(14,2))                  AS units,
  TRY_CAST("Dollars" AS DECIMAL(18,4))
    + COALESCE(TRY_CAST("Premium Dollars" AS DECIMAL(18,4)), 0) AS amount_raw,
  COALESCE(upper(trim("Adjustment Indicator")), '') IN ('Y', 'YES') 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