Tutorials

PHP Spreadsheet to Contact List: Enrich Websites with Company Data

PHP Spreadsheet to Contact List: Enrich Websites with Company Data

A spreadsheet of company websites looks like a prospect list until someone has to research every row. Company names vary, contact pages move, and a general mailbox can easily be mistaken for a named person’s address.

This tutorial builds a Native PHP 8.3 application that enriches those websites through Website to Company data and produces a CSV for human review. It preserves uncertainty, checkpoints progress in SQLite, and keeps failed requests visible. A successful extraction means “ready to review,” not “verified contact.”

Get access and copy the service token

  1. Register or sign in.
  2. Open the Website to Company data service and plan page, choose an available Free, Plus, or Pro plan, and complete activation.
  3. Open the official documentation. Find the Service token panel and copy the service-scoped token.

This service requires a token. Regenerating it revokes the previous active token, so update deployed configuration before restarting workers.

The exact request is GET https://ai.mihajlo.mk/api/website-to-company-data/v1/extract, with token and website query parameters. For a minimal test, replace both placeholders, including the example website:

curl --get --connect-timeout 5 --max-time 25 \
  'https://ai.mihajlo.mk/api/website-to-company-data/v1/extract' \
  --data-urlencode 'token=YOUR_SERVICE_TOKEN' \
  --data-urlencode 'website=https://example.com'

Now create .env in the project directory:

WEBSITE_COMPANY_TOKEN='YOUR_SERVICE_TOKEN'

PHP does not automatically load this file. Our launcher exports its values. Because shell sourcing executes its contents, only trusted operators should edit it.

Keep the architecture small and recoverable

Use PHP 8.3 with cURL, PDO SQLite, Composer, and the extensions required by PHPUnit, including DOM, XML, XMLWriter, and mbstring. Create src/, tests/, and var/. The project has four source files: composer.json, src/App.php, run.php, and tests/AppTest.php.

SQLite provides durable checkpoints without a queue server. One process owns the list at a time. This suits a freelancer or small team; multiple application replicas would require shared coordination.

Save this as composer.json:

{
  "require": {
    "php": "^8.3",
    "ext-curl": "*",
    "ext-pdo": "*",
    "ext-pdo_sqlite": "*"
  },
  "require-dev": {
    "phpunit/phpunit": "^11.5"
  },
  "autoload": {
    "files": ["src/App.php"]
  }
}

Exclude .env, vendor/, and var/ from Git. Commit the generated Composer lockfile.

Isolate transport and response mapping

The boundary maps only the supplied company, contact, email, phone, and people categories. Nested values remain structured JSON; we assume no undocumented person or address fields. Missing values become warnings. An unrecognized envelope fails visibly.

Save the following as src/App.php:

<?php
declare(strict_types=1);

final class ApiFailure extends RuntimeException
{
    public function __construct(
        public readonly string $kind,
        public readonly bool $retryable = false,
    ) {
        parent::__construct($kind);
    }
}

final readonly class Reply
{
    public function __construct(public int $status, public string $body) {}
}

interface Transport
{
    public function get(string $url): Reply;
}

final class CurlTransport implements Transport
{
    public function get(string $url): Reply
    {
        $body = '';
        $oversized = false;
        $curl = curl_init($url);
        if ($curl === false) {
            throw new ApiFailure('transport_setup');
        }
        curl_setopt_array($curl, [
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 25,
            CURLOPT_FOLLOWLOCATION => false,
            CURLOPT_PROTOCOLS => CURLPROTO_HTTPS,
            CURLOPT_SSL_VERIFYPEER => true,
            CURLOPT_SSL_VERIFYHOST => 2,
            CURLOPT_HTTPHEADER => ['Accept: application/json'],
            CURLOPT_WRITEFUNCTION => static function (
                $handle, string $chunk
            ) use (&$body, &$oversized): int {
                if (strlen($body) + strlen($chunk) > 1048576) {
                    $oversized = true;
                    return 0;
                }
                $body .= $chunk;
                return strlen($chunk);
            },
        ]);
        $ok = curl_exec($curl);
        $errno = curl_errno($curl);
        $status = (int) curl_getinfo($curl, CURLINFO_RESPONSE_CODE);
        curl_close($curl);

        if ($ok === false) {
            $transient = in_array($errno, [
                CURLE_COULDNT_RESOLVE_HOST, CURLE_COULDNT_CONNECT,
                CURLE_OPERATION_TIMEDOUT, CURLE_RECV_ERROR,
                CURLE_SEND_ERROR, CURLE_GOT_NOTHING,
            ], true);
            throw new ApiFailure(
                $oversized ? 'response_too_large' : 'transport',
                !$oversized && $transient,
            );
        }
        return new Reply($status, $body);
    }
}

final class CompanyApi
{
    public function __construct(
        private Transport $transport,
        private string $token,
    ) {
        if ($token === '') {
            throw new InvalidArgumentException('Service token missing.');
        }
    }

    public function extract(string $website): array
    {
        $query = http_build_query([
            'token' => $this->token, 'website' => $website,
        ], '', '&', PHP_QUERY_RFC3986);
        $reply = $this->transport->get(
            'https://ai.mihajlo.mk/api/website-to-company-data/v1/extract?'
            . $query
        );

        if (in_array($reply->status, [401, 403], true)) {
            throw new ApiFailure('auth');
        }
        if ($reply->status === 429) {
            throw new ApiFailure('quota');
        }
        if ($reply->status < 200 || $reply->status >= 300) {
            throw new ApiFailure('http_' . $reply->status,
                in_array($reply->status, [408, 500, 502, 503, 504], true));
        }
        try {
            $root = json_decode($reply->body, false, 64, JSON_THROW_ON_ERROR);
        } catch (JsonException) {
            throw new ApiFailure('invalid_json');
        }
        if (!$root instanceof stdClass) {
            throw new ApiFailure('schema');
        }

        $out = [];
        $warnings = [];
        $recognized = false;
        foreach (['company', 'contact', 'email', 'phone', 'people'] as $key) {
            if (!property_exists($root, $key)) {
                $out[$key] = null;
                $warnings[] = "missing:$key";
                continue;
            }
            $recognized = true;
            $value = $root->$key;
            if ($value !== null && !is_string($value)
                && !is_array($value) && !$value instanceof stdClass) {
                $warnings[] = "type:$key";
                $value = null;
            }
            if ($value === null || $value === '' || $value === []) {
                $warnings[] = "empty:$key";
            }
            $out[$key] = $value;
        }
        if (!$recognized) {
            throw new ApiFailure('schema');
        }
        $out['warnings'] = $warnings;
        return $out;
    }
}

function website(string $input): string
{
    $input = trim($input);
    if (!str_contains($input, '://')) {
        $input = 'https://' . $input;
    }
    $parts = parse_url($input);
    if (!is_array($parts)) {
        throw new InvalidArgumentException('Invalid website.');
    }
    $scheme = strtolower($parts['scheme'] ?? '');
    $host = strtolower($parts['host'] ?? '');
    if (strlen($input) > 2048
        || !in_array($scheme, ['http', 'https'], true)
        || isset($parts['user']) || isset($parts['pass'])
        || isset($parts['port']) || !str_contains($host, '.')
        || filter_var($host, FILTER_VALIDATE_IP)
        || !filter_var($host, FILTER_VALIDATE_DOMAIN, FILTER_FLAG_HOSTNAME)) {
        throw new InvalidArgumentException('Invalid website.');
    }
    return "$scheme://$host";
}

function csvCell(mixed $value): string
{
    $text = $value === null ? '' : (is_string($value)
        ? $value : json_encode($value, JSON_THROW_ON_ERROR));
    return preg_match('/^[\x00-\x20]*[=+@-]/', $text)
        ? "'" . $text : $text;
}

Origin normalization deliberately discards paths, queries, and fragments. Duplicate origins share one research record. Rejecting IP literals and credentials is an input policy, not proof of public DNS reachability. This application connects only to the fixed API host.

Import, checkpoint, and export

Export the source spreadsheet as UTF-8 CSV with one column named website. Save it as var/input.csv. Invalid websites remain visible in the output.

Save this CLI entry point as run.php:

<?php
declare(strict_types=1);
require __DIR__ . '/vendor/autoload.php';

umask(0077);
try {
    $dir = __DIR__ . '/var';
    if (!is_dir($dir)) {
        mkdir($dir, 0700, true);
    }
    $lock = fopen($dir . '/worker.lock', 'c');
    if (!$lock || !flock($lock, LOCK_EX | LOCK_NB)) {
        exit(75);
    }
    $db = new PDO('sqlite:' . $dir . '/research.sqlite', null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $db->exec("CREATE TABLE IF NOT EXISTS jobs (
        website TEXT PRIMARY KEY, state TEXT NOT NULL,
        attempts INTEGER NOT NULL DEFAULT 0,
        next_at INTEGER NOT NULL DEFAULT 0, data TEXT, error TEXT
    );
    CREATE TABLE IF NOT EXISTS control (
        id INTEGER PRIMARY KEY, blocked INTEGER NOT NULL
    );
    INSERT OR IGNORE INTO control VALUES (1, 0)");

    $sql = static function (string $query, array $args = []) use ($db) {
        $statement = $db->prepare($query);
        $statement->execute($args);
        return $statement;
    };
    $command = $argv[1] ?? '';

    if ($command === 'import') {
        $input = fopen($dir . '/input.csv', 'rb');
        if (!$input) {
            throw new InvalidArgumentException('Cannot open var/input.csv.');
        }
        $header = fgetcsv($input, 0, ',', '"', '');
        if (is_array($header) && is_string($header[0])) {
            $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', $header[0]);
        }
        if ($header !== ['website']) {
            throw new InvalidArgumentException('Expected one website column.');
        }
        while (($row = fgetcsv($input, 0, ',', '"', '')) !== false) {
            if ($row === [null]) {
                continue;
            }
            if (count($row) !== 1) {
                throw new InvalidArgumentException('Unexpected column count.');
            }
            $value = trim((string) $row[0]);
            if ($value === '') {
                continue;
            }
            $state = 'pending';
            $error = null;
            try {
                $value = website($value);
            } catch (InvalidArgumentException) {
                $state = 'invalid';
                $error = 'invalid_url';
            }
            $sql('INSERT OR IGNORE INTO jobs (website,state,error)
                  VALUES (?,?,?)', [$value, $state, $error]);
        }
        fclose($input);
    } elseif ($command === 'work') {
        if ((int) $db->query('SELECT blocked FROM control')->fetchColumn()) {
            throw new InvalidArgumentException(
                'Paused: resolve authentication or quota, then run resume.'
            );
        }
        $api = new CompanyApi(new CurlTransport(),
            trim((string) getenv('WEBSITE_COMPANY_TOKEN')));
        $sql("UPDATE jobs SET state='failed', error='attempt_limit'
              WHERE state IN ('pending','retry') AND attempts >= 3");

        while ($job = $sql("SELECT * FROM jobs
            WHERE state IN ('pending','retry') AND next_at <= ?
            AND attempts < 3 ORDER BY website LIMIT 1", [time()])->fetch()) {
            $attempt = (int) $job['attempts'] + 1;
            $sql('UPDATE jobs SET attempts=? WHERE website=?',
                [$attempt, $job['website']]);
            $state = 'review';
            $error = null;
            $data = null;
            $next = 0;
            try {
                $data = json_encode($api->extract($job['website']),
                    JSON_THROW_ON_ERROR);
            } catch (ApiFailure $failure) {
                $error = $failure->kind;
                $state = $failure->retryable && $attempt < 3
                    ? 'retry' : 'failed';
                $next = time() + (2 ** $attempt) + random_int(0, 3);
            }
            $sql('UPDATE jobs SET state=?,error=?,data=?,next_at=?
                  WHERE website=?',
                [$state, $error, $data, $next, $job['website']]);
            fwrite(STDOUT, json_encode([
                'id' => substr(hash('sha256', $job['website']), 0, 12),
                'state' => $state, 'attempt' => $attempt, 'error' => $error,
            ], JSON_THROW_ON_ERROR) . PHP_EOL);

            if (in_array($error, ['auth', 'quota'], true)) {
                $sql('UPDATE control SET blocked=1');
            }
            if ($error !== null) {
                break;
            }
            sleep(1);
        }
    } elseif ($command === 'resume') {
        $db->beginTransaction();
        $sql("UPDATE jobs SET state='pending',attempts=0,next_at=0,error=NULL
              WHERE error IN ('auth','quota')");
        $sql('UPDATE control SET blocked=0');
        $db->commit();
    } elseif ($command === 'export') {
        $output = fopen($dir . '/review.csv.tmp', 'wb');
        if (!$output) {
            throw new RuntimeException('Export unavailable.');
        }
        $fields = ['company','contact','email','phone','people','warnings'];
        $write = static function (array $cells) use ($output): void {
            if (fputcsv($output, array_map('csvCell', $cells),
                ',', '"', '') === false) {
                throw new RuntimeException('Export write failed.');
            }
        };
        $write(array_merge(['website','state','attempts','error'], $fields));
        foreach ($db->query('SELECT * FROM jobs ORDER BY website') as $job) {
            $data = json_decode($job['data'] ?? '{}', false, 64,
                JSON_THROW_ON_ERROR);
            $write(array_merge([
                $job['website'], $job['state'],
                (string) $job['attempts'], $job['error'],
            ], array_map(static fn($key) => $data->$key ?? null, $fields)));
        }
        if (!fflush($output) || !fclose($output)
            || !rename($dir . '/review.csv.tmp', $dir . '/review.csv')) {
            throw new RuntimeException('Export publication failed.');
        }
    } else {
        throw new InvalidArgumentException('Use import, work, export, or resume.');
    }
    fwrite(STDOUT, json_encode(['states' => $db->query(
        'SELECT state,COUNT(*) AS count FROM jobs GROUP BY state'
    )->fetchAll()], JSON_THROW_ON_ERROR) . PHP_EOL);
} catch (Throwable $exception) {
    fwrite(STDERR, ($exception instanceof InvalidArgumentException
        ? $exception->getMessage() : 'Command failed; inspect configuration and input.')
        . PHP_EOL);
    exit(1);
}

Retries have a three-attempt lifetime budget and persisted exponential backoff with jitter. Authentication and HTTP 429 pause the entire list until an operator resolves access or quota restrictions and runs resume. Check any server-provided Retry-After instruction before resuming.

Attempts are recorded before requests. A crash can therefore consume an attempt; a crash after the response but before saving can also cause another extraction. Checkpointing cannot guarantee exactly-once billing without an upstream idempotency contract.

Test without spending requests

Save tests/AppTest.php. The fake transport makes classification, mapping, and query encoding deterministic:

<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

final class FakeTransport implements Transport
{
    public string $url = '';
    public function __construct(private Reply $reply) {}
    public function get(string $url): Reply
    {
        $this->url = $url;
        return $this->reply;
    }
}

final class AppTest extends TestCase
{
    public function testMappingAndQuery(): void
    {
        $fake = new FakeTransport(new Reply(200,
            '{"company":{"label":"Example"},"email":["[email protected]"],"people":[]}'));
        $data = (new CompanyApi($fake, 'YOUR_SERVICE_TOKEN'))
            ->extract('https://example.com');
        parse_str((string) parse_url($fake->url, PHP_URL_QUERY), $query);
        self::assertSame('YOUR_SERVICE_TOKEN', $query['token']);
        self::assertSame('https://example.com', $query['website']);
        self::assertInstanceOf(stdClass::class, $data['company']);
        self::assertSame(['[email protected]'], $data['email']);
        self::assertContains('missing:phone', $data['warnings']);
    }

    public function testFailureClassification(): void
    {
        foreach ([
            [401, '', 'auth', false],
            [429, '', 'quota', false],
            [503, '', 'http_503', true],
            [200, '{}', 'schema', false],
            [200, 'broken', 'invalid_json', false],
        ] as [$status, $body, $kind, $retryable]) {
            $api = new CompanyApi(
                new FakeTransport(new Reply($status, $body)),
                'YOUR_SERVICE_TOKEN'
            );
            try {
                $api->extract('https://example.com');
                self::fail('Expected failure.');
            } catch (ApiFailure $failure) {
                self::assertSame($kind, $failure->kind);
                self::assertSame($retryable, $failure->retryable);
            }
        }
    }

    public function testNormalizationAndSpreadsheetSafety(): void
    {
        self::assertSame('https://example.com',
            website('https://EXAMPLE.com/contact?tracking=discarded'));
        self::assertSame("' \t=1+1", csvCell(" \t=1+1"));
        $this->expectException(InvalidArgumentException::class);
        website('http://127.0.0.1');
    }
}

Run and deploy deliberately

composer install
chmod 700 var
chmod 600 .env
vendor/bin/phpunit --bootstrap vendor/autoload.php tests

set -a
. ./.env
set +a
php run.php import
php run.php work
php run.php export

Schedule work every minute with the same environment and service account. The lock prevents overlapping runs; pending retries wait until eligible. A one-second gap softens successful bursts but does not establish compatibility with any plan’s limits.

Deploy outside the public document root, install production dependencies with composer install --no-dev --classmap-authoritative, and retain the database across releases. Protect CSVs and backups, restrict access, and set a retention period.

Logs contain hashed record identifiers, attempt counts, and fixed failure categories. Monitor failed counts and prolonged pending work. Because authentication lives in a query parameter, redact complete request URLs in HTTP tracing and error-reporting systems.

For auth, check activation and token rotation. For quota, inspect plan usage before resuming. For schema or invalid_json, compare a privately inspected response with the official documentation; never invent an envelope. Oversized responses require reviewing the explicit one-megabyte boundary.

Verify the review list

  • Import duplicate origins and an invalid URL; confirm deduplication and an invalid row.
  • Enrich one real company and compare every mapped category with its response.
  • Restart the worker; completed rows must remain completed.
  • Open var/review.csv; inspect warnings, nested JSON, and literal formula-like cells.

The useful result is a research list whose evidence and gaps are visible. Keep human review between extraction and outreach: a discovered address is a lead for investigation, not proof of accuracy, ownership, or permission to contact someone.

Blog author portrait

Mihajlo

I’m Mihajlo — a developer driven by curiosity, discipline, and the constant urge to create something meaningful. I share insights, tutorials, and free services to help others simplify their work and grow in the ever-evolving world of software and AI.

Search

Categories

Recent Posts

Lead with Ownership: Architect AI That Learns Your Business Logic
Beyond Prompts: Make Your Code the AI's Masterclass
Embed Your Business Logic: Design AI That Actually Learns
Ship Better Software: How Technical Leadership Fuels AI's Business Instinct
Symfony: Build a Smart FAQ Helper for Customer Portals with AI Smart Routing