CSV Import

Category: Data & Analytics

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

Overview

Accept a posted CSV body, parse it into rows, and bulk-insert them into a Postgres contacts table. Demonstrates parse_csv over the raw request body plus a lookup fan-out insert.

Endpoints

• POST contacts/import — parse a posted CSV and bulk-insert rows

Required variables

• ap_var::DATABASE_URL — Postgres connection string

Configuration

csv_import.yml

name: CsvImport
description: >
  Accept a posted CSV body, parse it into rows, and bulk-insert them into a
  Postgres contacts table. Demonstrates parse_csv over the raw request body plus
  a lookup fan-out insert.

docs: true

# Required managed variables:
#   DATABASE_URL — Postgres connection string

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"
interfaces:
  contacts/import:
    output: http
    method: POST
    summary: Parse a posted CSV and bulk-insert the rows
    actions:
      - name: ParseCsv
        input: a|raw_body|
        post_transforms:
          - parse_csv: "$"
      - name: InsertRows
        run_when_succeeded: [ParseCsv]
        lookup: a|ParseCsv|
        actions:
          - name: InsertOne
            database: main
            query: |-
              INSERT INTO contacts (name, email)
              VALUES (a|body::name->single_quote|, a|body::email->single_quote|)
              ON CONFLICT (email) DO NOTHING
      - name: Respond
        run_when_succeeded: [InsertRows]
        json_output: |-
          { "status": "imported" }

seed.yml

name: CsvImportSeed
description: Creates the contacts table used by the import endpoint. Safe to re-run.
docs: true
global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"
interfaces:
  api/seed:
    output: http
    method: POST
    summary: Create and clear the contacts table
    actions:
      - name: CreateContactsTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS contacts (
            id    BIGSERIAL   PRIMARY KEY,
            name  TEXT,
            email TEXT UNIQUE NOT NULL
          );
      - name: TruncateContacts
        run_when_succeeded: [CreateContactsTable]
        database: main
        hide_data_on_success: true
        query: TRUNCATE contacts RESTART IDENTITY;
      - name: Respond
        run_when_succeeded: [TruncateContacts]
        json_output: |-
          { "status": "seeded" }