# AI Data Formatter & Schema Converter (`pearlescent_idiom/ai-data-formatter-schema-converter`) Actor

Transform messy CSV or JSON data into a validated custom schema with clean JSON and Excel-ready CSV outputs.

- **URL**: https://apify.com/pearlescent\_idiom/ai-data-formatter-schema-converter.md
- **Developed by:** [Ezgi Uysal](https://apify.com/pearlescent_idiom) (community)
- **Categories:**
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $40.00 / 1,000 small data formattings

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.
Actors are written with capital "A".

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## AI Data Formatter & Schema Converter

Transform inconsistent CSV or JSON records into an exact custom schema. The Actor maps semantically equivalent fields, converts safe value types, validates every result, reports records it cannot complete without inventing data, removes duplicates, and returns clean JSON plus an Excel-compatible CSV.

### What it does

- Accepts JSON objects, pasted CSV/JSON, or uploaded CSV/JSON files
- Maps inconsistent source field names into a user-provided JSON Schema
- Normalizes whitespace and safely converts numbers, booleans, arrays, and nested objects
- Validates every successful record against the target schema
- Marks incomplete records as failed instead of inventing required values
- Removes duplicates using selected target fields or the complete formatted record
- Produces dataset rows, full JSON, Excel-ready CSV, and a run summary

The Actor does not scrape third-party websites and has no OCR dependency, which keeps maintenance low.

### Input

Use one or more of:

- `records`: up to 500 JSON objects
- `files`: up to five UTF-8 CSV or JSON uploads
- `dataText`: pasted CSV or JSON

`targetSchema` must describe one JSON object with at least one property. Remote `$ref` schemas are intentionally disabled so each run remains self-contained and predictable.

```json
{
  "records": [
    { "Customer No": "C-001", "Name": " Ada Yilmaz ", "Mail": "ADA@EXAMPLE.COM", "Spent": "1250.50" }
  ],
  "targetSchema": {
    "type": "object",
    "properties": {
      "customer_id": { "type": "string" },
      "full_name": { "type": "string" },
      "email": { "type": ["string", "null"] },
      "total_spent": { "type": ["number", "null"] }
    },
    "required": ["customer_id", "full_name", "email", "total_spent"],
    "additionalProperties": false
  },
  "instructions": "Normalize email addresses to lowercase.",
  "removeDuplicates": true,
  "deduplicateBy": ["email"]
}
```

Use nullable types such as `["string", "null"]` when a missing value is acceptable. If a required non-null field cannot be derived from the source, the Actor returns that row with `status: "failed"` and a clear error.

### Output

- Dataset: one envelope per retained source row with `status`, the formatted `record`, warnings, and duplicate metadata
- `OUTPUT.json`: target schema, formatted records, batch metadata, and run statistics
- `OUTPUT.csv`: UTF-8, Excel-compatible table with dynamic target-schema columns
- `SUMMARY.json`: counts, processed size, and metering event

Every model response is validated. Malformed responses, missing source indexes, extra fields, and target-schema mismatches trigger bounded correction retries. Uploaded HTTP files are restricted to public addresses, five redirects, and 15 MB each. Total source text is capped at 500,000 characters.

### Environment and local test

Set `LLM_API_KEY` or `OPENAI_API_KEY` in the environment. Optional settings are `LLM_MODEL`, `LLM_BASE_URL`, and `LLM_TIMEOUT_MS`.

```bash
pnpm install
pnpm test
export LLM_API_KEY="your-key"
export DISABLE_METERING=true
apify run --input-file samples/cloud-input.json
```

Never put API keys in Actor input or source files. On Apify, keep the key in an encrypted secret and reference it from `.actor/actor.json`.

### Deploy to Apify

```bash
apify login
apify push
```

After deployment, run `samples/cloud-input.json`. The sample should produce two successful rows, remove one duplicate email, and provide `OUTPUT.json`, `OUTPUT.csv`, and `SUMMARY.json`.

### Suggested pay-per-event pricing

| Event | Workload | Suggested price |
| --- | ---: | ---: |
| `formatter-small` | Up to 25 records / equivalent text size | $0.04 |
| `formatter-medium` | 26–100 records / equivalent text size | $0.12 |
| `formatter-large` | 101–500 records / equivalent text size | $0.45 |

The workload tier also considers source character count so unusually large records are not underpriced. The event is emitted only after the dataset and downloadable outputs are saved. Review real model costs before changing prices.

### Publish in Apify Store

Keep the Actor private while testing. When ready:

1. Open **Actor → Publishing**.
2. Add the Store title and description below.
3. Select **Pay per event**.
4. Add the three exact event names and prices above.
5. Confirm the default input produces a non-empty dataset.
6. Publish only after a final successful cloud run.

Suggested Store title: **AI Data Formatter & Schema Converter**

Suggested description: **Transform messy CSV or JSON into any validated target schema with clean JSON, Excel-ready CSV, duplicate removal, and row-level error reporting.**

# Actor input Schema

## `records` (type: `array`):

Paste a JSON array of objects. Field names and value formats can be inconsistent.

## `files` (type: `array`):

Upload up to five UTF-8 CSV or JSON files.

## `dataText` (type: `string`):

Automatic detection supports standard JSON arrays/objects and comma-separated CSV.

## `inputFormat` (type: `string`):

Use automatic detection unless the source has an unusual extension or structure.

## `targetSchema` (type: `object`):

Define one output object. Missing required non-null values are reported as failed records instead of invented.

## `instructions` (type: `string`):

Optional field mappings, locale rules, date formats, units, or naming conventions.

## `removeDuplicates` (type: `boolean`):

Keep the first matching formatted record and report how many duplicates were found.

## `deduplicateBy` (type: `array`):

Optional target fields such as email or customer\_id. Leave empty to compare the complete formatted record.

## `retryCount` (type: `integer`):

Number of model correction attempts for invalid output.

## `continueOnError` (type: `boolean`):

Save failed rows with an error message and continue processing later batches.

## Actor input object example

```json
{
  "records": [
    {
      "Customer ID": "C-001",
      "Full Name": "  Ada Yilmaz ",
      "Mail": "ADA@EXAMPLE.COM",
      "Spent": "1,250.50",
      "Newsletter": "yes"
    },
    {
      "id": "C-001",
      "name": "Ada Yilmaz",
      "email": "ada@example.com",
      "total": 1250.5,
      "subscribed": true
    },
    {
      "customer_no": "C-002",
      "customer": "Mert Kaya",
      "email_address": "mert@example.com",
      "amount": "850",
      "newsletter": "no"
    }
  ],
  "inputFormat": "auto",
  "targetSchema": {
    "type": "object",
    "properties": {
      "customer_id": {
        "type": "string"
      },
      "full_name": {
        "type": "string"
      },
      "email": {
        "type": [
          "string",
          "null"
        ]
      },
      "total_spent": {
        "type": [
          "number",
          "null"
        ]
      },
      "subscribed": {
        "type": [
          "boolean",
          "null"
        ]
      }
    },
    "required": [
      "customer_id",
      "full_name",
      "email",
      "total_spent",
      "subscribed"
    ],
    "additionalProperties": false
  },
  "instructions": "Normalize email addresses to lowercase and trim surrounding whitespace.",
  "removeDuplicates": true,
  "deduplicateBy": [
    "email"
  ],
  "retryCount": 2,
  "continueOnError": true
}
```

# Actor output Schema

## `records` (type: `string`):

No description

## `json` (type: `string`):

No description

## `csv` (type: `string`):

No description

## `summary` (type: `string`):

No description

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {};

// Run the Actor and wait for it to finish
const run = await client.actor("pearlescent_idiom/ai-data-formatter-schema-converter").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {}

# Run the Actor and wait for it to finish
run = client.actor("pearlescent_idiom/ai-data-formatter-schema-converter").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{}' |
apify call pearlescent_idiom/ai-data-formatter-schema-converter --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,pearlescent_idiom/ai-data-formatter-schema-converter"
        }
    }
}

```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/iuJmpc3f3BpS79VOJ/builds/ppGF84cWLr20mE09s/openapi.json