Guide · API Integration

CSV to REST API without writing a one-off script

A CSV file and a REST API may describe the same business data while using completely different structures. Here is how to bridge that gap safely.

Why CSV-to-API work becomes a script

Imagine you receive a spreadsheet containing company data that needs to be imported into an API. At first the task sounds simple: read each row and send it to the endpoint.

The difficulty is that CSV files contain strings arranged under spreadsheet headers, while APIs expect structured fields with specific names and data types.

Company NameCountryRevenueActive
AppleUS₹3,83,000Yes

The API might instead expect:

{
  "company_name": "Apple",
  "country_code": "US",
  "revenue": 383000,
  "active": true
}

Four different problems are hidden in one row

Field names

Company Name must become company_name and Country must become country_code.

Number formatting

₹3,83,000 is readable to a person but is not directly a JSON number.

Boolean representation

Yes is not the JSON boolean true.

Validation

The converted object should be checked before it is used in a request.

Step 1: understand the target API

Start with the API rather than the CSV. Identify the endpoint, HTTP method, authentication headers and expected body structure. An existing cURL request or example JSON payload is often enough to establish the basic target structure.

Step 2: map source columns to target fields

Mapping separates the name used in the spreadsheet from the name expected by the API.

Company Name → company_name
Country → country_code
Revenue → revenue
Active → active

Keeping mapping separate from value cleanup makes the workflow easier to inspect. A field can be mapped correctly while still containing values that need transformation.

Step 3: transform values before type conversion

The Revenue value can be cleaned by removing the currency symbol and thousands separators:

₹3,83,000
↓ remove ₹
3,83,000
↓ remove ,
383000

The Active value can similarly be normalized:

Yes→true

Step 4: validate the whole dataset

Testing only the first row is risky. A rule that handles a rupee-formatted number might still fail on a dollar-formatted number or a value such as N/A.

Validate transformations across the complete column before treating the dataset as ready. Failing rows should remain visible so they can be corrected or handled with additional rules.

Step 5: generate typed output

After mapping, transformation and validation, the original spreadsheet row can safely become:

{
  "company_name": "Apple",
  "country_code": "US",
  "revenue": 383000,
  "active": true
}

When a reusable tool is better than a one-off script

A script is still appropriate when you need a fully automated production integration, specialized business logic or complex orchestration.

An interactive workflow is useful when the job begins with a spreadsheet, requires human review, changes from dataset to dataset or would otherwise require repeatedly modifying a disposable import script.

Try the workflow in PayloadForge

Configure an API, import your tabular data, map fields and preview transformations before generating the final request.

Open BuilderRead Documentation