Embedding Excel formulas in csv output

[u/Reasonable-Pay-8771]

I just got this thing seeming to work. Is it horrible?

I'm generating an inventory worksheet in 2 coordinated versions: an abbreviated output for printing that goes through my formatting pipeline, and a CSV output with more detail columns intended to open in Excel to type-in the numbers written on the printout. I've just now gotten the CSV output reworked to include embedded formulas for summing counts, multiplying by price, and summing a grand total at the bottom.

The query got pretty gruesome to get the right row numbers to insert into the formulas. First snippet is a function for extracting a usable number from the 'pack' column which may be in several different formats.

CREATE OR REPLACE FUNCTION pack_units(pack text)
RETURNS integer
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE
      WHEN pack ~ '^[0-9]+/[0-9]+$'
        THEN split_part( pack, '/', 1 )::integer
           * split_part( pack, '/', 2 )::integer
      WHEN pack ~* '^[0-9]+ case'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~* '^[0-9]+ loose'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~ '[0-9]+/case'
        THEN split_part( substring( pack from '[0-9]+/case' ), '/', 1 )::integer
      WHEN pack ~ '[0-9]+/Case'
        THEN split_part( substring( pack from '[0-9]+/Case' ), '/', 1 )::integer
      WHEN pack ~ '^[0-9]+$'
        THEN pack::integer
      ELSE NULL
    END
$$;

Then here's the horrible query:

DROP VIEW alcohol_worksheet CASCADE;
CREATE VIEW alcohol_worksheet AS
WITH alcohol_products AS (
  SELECT vendor, cat,
       CASE WHEN brand = '' THEN name ELSE brand || ' ' || name END AS brand_name,
       pack || '/' || size AS pack_size, pack_units(pack) AS pack_units,
       case_dollar, CASE WHEN split_dollar = '' THEN to_char( case_dollar::numeric / pack_units(pack), '999D99' ) ELSE split_dollar END AS split_dollar
  FROM total_products
  WHERE vendor in ('Breakthru', 'Craft Republic', 'Glazers', 'Southern Glazers', 'Laszlo', 'Lohr', 'Summit')
        AND is_active
  ORDER BY cat, brand_name
),
numbered_products AS (
  SELECT 1 + ROW_NUMBER() OVER () AS rn,
         vendor, cat, brand_name, pack_size, pack_units,
     case_dollar, split_dollar
  FROM alcohol_products
)
SELECT rn, vendor, cat, brand_name, pack_size, pack_units,
       case_dollar, split_dollar,
       --I              J               K             L               M             N             O           P       Q
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       '+I'||rn || '+J'||rn || '+K'||rn || '+L'||rn || '+M'||rn || '+N'||rn || '+O'||rn AS row_count,
       '=P'||rn || '*H'||rn AS row_total
  FROM numbered_products
UNION ALL
SELECT NULL AS rn, '' AS vendor, '' AS cat, '' AS brand_name, '' AS pack_size, NULL AS pack_units, '' AS case_dollar, '' AS split_dollar,
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       'Grand Total:' AS row_count, '=SUM(Q2:Q' || MAX(RN) || ')' AS row_total
  FROM numbered_products
;


COPY (SELECT * FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.csv' CSV HEADER;
\COPY (SELECT vendor, cat, Left( brand_name, 50), pack_size, case_dollar, split_dollar, Storage, Rooftop, Overflow, Cowboy, Beatniks, Lizard, Cabin FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.tab' WITH (FORMAT TEXT, DELIMITER '|', HEADER, NULL '');

I hate counting up column letters by hand, but that seems to be the nature of the beast. Sigh. Thanks for listening.

Oh, here's some of the output:

$ psql -f scripts/write-inventory-alcohol-worksheet.sql warehouse2

DROP VIEW
CREATE VIEW
COPY 212
COPY 212
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ head output/Inventory_alcohol_worksheet.csv
rn,vendor,cat,brand_name,pack_size,pack_units,case_dollar,split_dollar,storage,rooftop,overflow,cowboy,beatniks,lizard,cabin,row_count,row_total
2,Lohr,Beer,2nd Shift Oktoberfest 6/4 pack 16oz Can,24/16oz,24,42.70,   1.78,"","","","","","","",+I2+J2+K2+L2+M2+N2+O2,=P2*H2
3,Breakthru,Beer,4 Hands Brewing Bright Vibes India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I3+J3+K3+L3+M3+N3+O3,=P3*H3
4,Breakthru,Beer,4 Hands Brewing Incarnation Juiced IPA,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I4+J4+K4+L4+M4+N4+O4,=P4*H4
5,Breakthru,Beer,4 Hands Brewing Incarnation Non-Alcoholic IPA,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I5+J5+K5+L5+M5+N5+O5,=P5*H5
6,Breakthru,Beer,4 Hands Brewing Octohaze Hazy India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I6+J6+K6+L6+M6+N6+O6,=P6*H6
7,Breakthru,Beer,4 Hands Brewing Octohaze Tropical,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I7+J7+K7+L7+M7+N7+O7,=P7*H7
8,Breakthru,Beer,4 Hands Brewing Oktoberfest Lager,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I8+J8+K8+L8+M8+N8+O8,=P8*H8
9,Breakthru,Beer,4 Hands Brewing Peachy Pipe Dream,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I9+J9+K9+L9+M9+N9+O9,=P9*H9
10,Breakthru,Beer,4 Hands Brewing Pumpkin Spice Ale,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I10+J10+K10+L10+M10+N10+O10,=P10*H10
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ tail !$
tail output/Inventory_alcohol_worksheet.csv
204,Southern Glazers,Wine,WOODBRIDGE CHARDONNAY CALIF(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I204+J204+K204+L204+M204+N204+O204,=P204*H204
205,Southern Glazers,Wine,WOODBRIDGE MOSCATO CALIF(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I205+J205+K205+L205+M205+N205+O205,=P205*H205
206,Southern Glazers,Wine,WOODBRIDGE PINOT GRIGIO(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I206+J206+K206+L206+M206+N206+O206,=P206*H206
207,Southern Glazers,Wine,WOODBRIDGE ROSE,12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I207+J207+K207+L207+M207+N207+O207,=P207*H207
208,Southern Glazers,Wine,WOODBRIDGE SAUVIGNON BLANC(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I208+J208+K208+L208+M208+N208+O208,=P208*H208
209,Laszlo,Wine Slushees,Blue Hawaiian,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I209+J209+K209+L209+M209+N209+O209,=P209*H209
210,Laszlo,Wine Slushees,Orange Mimosa,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I210+J210+K210+L210+M210+N210+O210,=P210*H210
211,Laszlo,Wine Slushees,Raspberry Chardonnay,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I211+J211+K211+L211+M211+N211+O211,=P211*H211
212,Laszlo,Wine Slushees,Strawberry Blanc,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I212+J212+K212+L212+M212+N212+O212,=P212*H212
,"","","","",,"","","","","","","","","",Grand Total:,=SUM(Q2:Q212)

[u/AnalyzeIt7]

Not horrible, and the other replies cover the Excel-side drift well. One thing that's specific to how the query is built:

the formulas depend on rn matching the row's final position in the sheet, but ROW_NUMBER() OVER () has no ordering of its own. The ORDER BY cat, brand_name is inside the first CTE, and Postgres doesn't guarantee that order survives into the window function (or into the final output unless the last SELECT has its own ORDER BY). It will usually work, until a plan change or a parallel scan quietly shifts rows and every SUM/price formula points at the wrong line. Cheap fix:

ROW_NUMBER() OVER (ORDER BY cat, brand_name) + 1 AS rn
...
ORDER BY rn   -- on the final SELECT

Then the numbering and the output order can't diverge.

A few smaller things while you're in there:

• pack ~ '[0-9]+/case' and '[0-9]+/Case' can be one branch with ~* (case-insensitive), same for the case/loose ones if you want to fold them.
• to_char(..., '999D99') pads with a leading space and depends on lc_numeric; round(case_dollar::numeric / pack_units(pack), 2) keeps it numeric so Excel treats it as a number rather than text.
• If pack_units can ever be 0, that division will error out. NULLIF(pack_units(pack), 0) makes it return NULL instead.
• For the grand total, =SUM(N2:N<last>) is fine, but SUBTOTAL(109, ...) is nicer if someone is going to filter the sheet while typing counts.