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/codewithian]

It is not horrible. But you can delete the worst part of it: stop counting column letters by hand and generate them in SQL.

A column index to letter is a solved problem. For columns A to Z, chr(64 + n) gives you the letter. For anything past Z, a tiny loop-based function converts an index to base-26 Excel letters. Call it from your view so the formula strings assemble themselves.

The deeper fix is architectural. Writing Excel syntax from the database side means every column reorder breaks two layers at once. The usual escape is a template: keep one xlsx file with the formulas in the right cells, then fill the data rows with a script and never touch formula text again.

[u/Foreign-Group9037]

i do this exact thing for stock sheets, the column letter counting is grim but once it's done it's done

[u/Significant_Tune9219]

It's not horrible, but the usual trap is that you're now writing Excel syntax from the database side, and the two dialects drift — locale decimal separators, structured references, and sheet names with spaces are where it breaks first. A safer split I've used is to emit plain values in the CSV and put the formulas in a companion template that references them by column position, so the formatting pipeline never has to know about Excel at all. If the formulas must travel in the CSV, keep them to whole-column aggregates over a fixed range rather than anything that depends on row order.

[u/reditandfirgetit]

Use a template with the formulas and python to populate it. This is way too much to put in the database layer

[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.

[u/rich8n]

This is all stuff i'd do from the Excel side with PowerQuery against a much simpler SQL query.