How to Backfill a MySQL Table from a Manually Uploaded CSV

Build a Spojit workflow that you trigger on demand with a base64-encoded CSV in the request body, parses and validates every row, then bulk-inserts the clean rows into a MySQL table for a one-off backfill.

What This Integration Does

Backfills happen when a table is missing historical rows: a new column needs seeding, a migration dropped records, or a system was offline while data piled up in a spreadsheet. Rather than hand-running raw SQL, this workflow lets you (or a teammate) drop a CSV export into a single request and have Spojit decode it, parse it into structured rows, validate each one, and load only the rows that pass into a target MySQL table. You get a repeatable, reviewable load that records its own execution history instead of an untracked one-off query.

The workflow starts from a Webhook trigger. You send one HTTP POST containing the CSV as a base64 string, the workflow decodes and parses it, runs validation, and ends with a single MySQL insert-rows call that loads the whole batch. It is stateful only in the target table: each run inserts whatever rows you pass, so re-running with the same file inserts the same rows again. For a true one-off backfill that is what you want, but plan for idempotency (see Common Pitfalls) if you might replay the same file.

Prerequisites

  • A MySQL connection added under Connections -> Add connection, pointed at the database that holds your target table, with insert permission on that table.
  • The exact target table name and its column names. Run the MySQL describe-table tool once (or use Miraxa) to confirm column names, types, and which columns are NOT NULL or unique.
  • A CSV export whose header row matches the column names you intend to load. The csv, json, and encoding utility connectors are built in and need no connection.
  • Optionally, a signing connection if you want to verify the inbound webhook with HMAC (scheme Spojit or Custom).

Step 1: Add a Webhook trigger and define the request body

On the canvas, set the Trigger type to Webhook. Spojit gives the workflow a unique URL and returns 202 with an executionId to the caller. The parsed JSON body is exposed as {{ input }}. Design your caller to POST a body like this, where file is the base64 of the raw CSV text:

{
  "table": "orders_archive",
  "file": "aWQsY3VzdG9tZXJfZW1haWwsdG90YWwKMSx..."
}

To protect the endpoint, attach a signing connection so Spojit verifies the HMAC signature before running. Keep your CSV under the platform request-size limits by loading a focused export rather than a year of data in one call.

Step 2: Decode the base64 CSV

Add a Connector node in Direct mode using the encoding connector and the base64-decode tool. Map its input to {{ input.file }} so the base64 payload becomes the raw CSV text. Name the output variable decoded. This gives you a plain UTF-8 CSV string that the next step can parse.

If your caller can send the CSV as plain text instead of base64, you can skip this step and feed {{ input.file }} straight into Step 3. Base64 is recommended because it survives multi-line content and quoting without breaking the JSON body.

Step 3: Parse the CSV into structured rows

Add a Connector node in Direct mode with the csv connector and the parse tool. Map the csv field to {{ decoded.value }} (or {{ input.file }} if you skipped decoding). Leave header set to true so the first row becomes object keys, and keep dynamicTyping on so numeric strings load as numbers. Name the output variable parsed.

The parse tool returns items (an array of row objects), count (how many rows it read), and meta.fields (the detected header names). You will reference {{ parsed.items }} as the row set and {{ parsed.count }} when reporting. A quick sanity check: meta.fields should list exactly the columns you expect to load.

Step 4: Validate the rows before loading

Add a Connector node in Direct mode with the validation connector for field-level checks, then gate the result with a Condition node. For example, if every row must carry a customer email, loop or transform the rows and run the email tool on each value, or use the json connector's query tool to pull out rows that are missing a required key. A practical pattern is a Transform node that splits {{ parsed.items }} into a valid list and a rejected list based on your rules.

Then add a Condition node that checks whether any valid rows remain, for example whether {{ parsed.count }} is greater than 0 and the valid list is non-empty. Route the false branch to a Response node (Step 7) so an empty or all-invalid file fails fast instead of hitting the database. For judgment calls that are hard to express as rules (free-text categories, inconsistent formats), use a Connector node in Agent mode with a Response Schema so Miraxa, the intelligent layer across your automation, returns a clean structured row set.

Step 5: Confirm the target table shape (optional safety check)

Add a Connector node in Direct mode with the MySQL connector and the describe-table tool, passing table as {{ input.table }}. This returns the column definitions and the CREATE TABLE statement, letting a follow-up Condition node confirm your CSV headers line up with real columns before you insert. Skip this in steady state once you trust the file format, but it is worth keeping while you tune the backfill.

Step 6: Bulk-insert the clean rows into MySQL

Add a Connector node in Direct mode with the MySQL connector and the insert-rows tool. Set table to {{ input.table }} and map rows to your validated array (for example {{ parsed.items }} or the valid list from Step 4). Each object's keys must match the table's column names exactly. The tool inserts the whole batch in one call:

{
  "table": "orders_archive",
  "rows": [
    { "id": 1, "customer_email": "a@example.com", "total": 49.95 },
    { "id": 2, "customer_email": "b@example.com", "total": 120.00 }
  ]
}

If you need conditional loading (insert new, skip existing) rather than a plain insert, use the MySQL execute-query tool with an INSERT ... ON DUPLICATE KEY UPDATE statement and ? placeholders bound through the params field. Name the insert step's output variable loaded so you can report on it.

Step 7: Return a summary to the caller

Because the trigger is a Webhook, finish with a Response node so the caller learns what happened in the same request. Return the parsed count, the number loaded, and any rejected rows, for example:

{
  "table": "{{ input.table }}",
  "rowsParsed": "{{ parsed.count }}",
  "rowsLoaded": "{{ loaded.affectedRows }}",
  "status": "backfill complete"
}

For an audit trail you can also add a Send Email node that mails the summary to the person who ran the backfill, using Spojit's built-in mail service with no connection required.

Tips

  • Keep each upload to a manageable batch. Very large CSVs can exceed request-size limits or strain a single insert-rows call. For big backfills, split the file and run the workflow once per chunk.
  • Use the csv info tool early to read row and column counts and catch a malformed file before you spend a database call.
  • If your CSV uses a non-comma delimiter, set the delimiter field on the csv parse tool explicitly instead of relying on auto-detection.
  • Scaffold the whole pipeline by asking Miraxa, for example: "Build a workflow with a Webhook trigger that base64-decodes a CSV, parses it with the csv connector, then inserts the rows into MySQL with insert-rows," then fine-tune each node in the properties panel.

Common Pitfalls

  • Re-running duplicates rows. A plain insert-rows call is not idempotent: replaying the same file inserts the rows again. Add a unique key and use execute-query with INSERT ... ON DUPLICATE KEY UPDATE, or check for existing rows first.
  • Header mismatch. Object keys that do not match real column names cause the insert to fail. Confirm headers against describe-table and rename columns with the csv transform tool if the export uses different labels.
  • Type and null surprises. Empty CSV cells can arrive as empty strings rather than NULL, breaking NOT NULL or numeric columns. Normalize values in a Transform node before the insert.
  • Forgetting to decode. If you feed the base64 string straight into parse without base64-decode, the parser reads gibberish. Confirm {{ decoded.value }} looks like real CSV in the execution logs.

Testing

Test against a throwaway table first. Create a temporary copy of the target table, point {{ input.table }} at it, and POST a small CSV of two or three rows base64-encoded in the file field. Open the run in the execution logs and step through each node: confirm {{ decoded.value }} is readable CSV, that {{ parsed.count }} matches your row count, that validation routed correctly, and that insert-rows reports the expected affected rows. Once the small batch loads cleanly and the Response summary looks right, switch {{ input.table }} to the real table and run your full backfill file.

Learn More

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.