Extraction query

The DuckDB SQL that reads Sony Music Publishing's XLSX files into the standard schema. {{file}} is replaced with the statement file's path at import time.

Extraction query

-- Sony Music Publishing CS04 statement -> normalized schema
SELECT
  row_number() OVER ()                                          AS line_number,
  trim("Song")                                                  AS song_title,
  upper(trim("Song"))                                           AS song_title_key,
  COALESCE(NULLIF(upper(trim("Src4 Nm")), ''),
           NULLIF(upper(trim("Src3 Nm")), ''),
           NULLIF(upper(trim("Src2 Nm")), ''),
           upper(trim("Src Nm")))                               AS source_name_key,
  upper(trim("Inc Typ"))                                        AS income_class_key,
  upper(trim("Inc Typ"))                                        AS right_type_key,
  COALESCE(NULLIF(upper(trim("Src4Ctry")), ''),
           NULLIF(upper(trim("Src3Ctry")), ''),
           NULLIF(upper(trim("Src2Ctry")), ''),
           upper(trim("Src Ctry")))                             AS territory_raw,
  trim("Writer")                                                AS writer_name,
  TRY_CAST("Units" AS DECIMAL(14,2))                            AS units,
  TRY_CAST("SngShr%" AS DECIMAL(9,6))                           AS share_percentage,
  TRY_CAST("RoyAmt" AS DECIMAL(18,4))                           AS amount_raw,
  FALSE                                                         AS adjustment,
  CAST(try_strptime(substr(COALESCE(NULLIF(trim("Sales Pd"), ''),
                                    trim("Rpt'g Pd")), 1, 4), '%m%y') AS DATE) AS earning_date,
  NULLIF(upper(trim("ISWC Cd")), '')                            AS iswc,
  NULLIF(upper(trim("ISRC")), '')                               AS isrc,
  'USD'                                                         AS currency,
  to_json(row)                                                  AS source_row
FROM read_xlsx('{{file}}',
          header = true,
          all_varchar = true,
          stop_at_empty = true,
          range = 'A10:AD1048576') 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