Database Analyst · TRIM · NULLIF · regexp_replace · ::numeric · ::date
5. Parse a text-only import
Concept: TRIM · NULLIF · regexp_replace · ::numeric · ::dateshow
Spreadsheet imports often arrive as all-text columns. To turn text into a number or date: strip the formatting (regexp_replace(x, '[$,]', '', 'g')), turn placeholders like '' or 'N/A' into NULL with NULLIF, then cast with ::numeric or ::date. Casting '' or ' ' fails, so make them NULL first.
Example (different data):
SELECT TRIM(city) AS city, NULLIF(regexp_replace(pop_text, '[, ]', '', 'g'), '')::int AS population FROM census_raw;
Your task
Accounts payable loaded a vendor spreadsheet where every column is text, and the amounts can't be summed until they're real numbers.
Return id, vendor (with leading and trailing spaces trimmed), amount (amount_text as numeric, with $, commas and spaces removed; 'N/A', empty or spaces-only becomes NULL) and invoice_date (invoice_date_text as a date; empty or spaces-only becomes NULL) for every row of invoice_import_raw. Sort by id.
Starting Postgres in your browser…
Checking your access…