Blog

Um Abonnenten in den Newsletter von einem System in Magento 2 zu importieren kann dieses Script genutzt werden. Dabei wird *keine* Email an den jeweiligen Kunden gesendet.

Usage:
php scripts/import_confirmed_newsletter_subscribers.php --file=/path/subscribers.csv --store-id=3

Test ohne Import:
php scripts/import_confirmed_newsletter_subscribers.php --file=/path/subscribers.csv --store-id=3 --dry-run

<?php
use Magento\Framework\App\Bootstrap;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Math\Random;
use Magento\Framework\Stdlib\DateTime\DateTime;
use Magento\Newsletter\Model\Subscriber;
use Magento\Store\Model\StoreManagerInterface;

require __DIR__ . '/../app/bootstrap.php';

$usage = <<<'USAGE' Import newsletter subscribers as confirmed without sending emails. Usage: php scripts/import_confirmed_newsletter_subscribers.php --file=/path/subscribers.csv [--store-id=1] [--email-column=email] [--delimiter=,] [--dry-run] Input: - CSV with a header column named "email" by default - or a plain text file with one email address per line USAGE; $options = getopt('', [ 'file:', 'store-id::', 'email-column::', 'delimiter::', 'dry-run', 'help', ]); if (isset($options['help'])) { echo $usage; exit(0); } $file = (string)($options['file'] ?? ''); $storeId = (int)($options['store-id'] ?? 1); $emailColumn = (string)($options['email-column'] ?? 'email'); $delimiter = (string)($options['delimiter'] ?? ','); $dryRun = isset($options['dry-run']); if ($file === '' || !is_readable($file)) { fwrite(STDERR, "Import file is missing or not readable.\n\n" . $usage); exit(1); } if (strlen($delimiter) !== 1) { fwrite(STDERR, "Delimiter must be exactly one character.\n"); exit(1); } $bootstrap = Bootstrap::create(BP, $_SERVER); $objectManager = $bootstrap->getObjectManager();

/** @var StoreManagerInterface $storeManager */
$storeManager = $objectManager->get(StoreManagerInterface::class);
/** @var ResourceConnection $resource */
$resource = $objectManager->get(ResourceConnection::class);
/** @var DateTime $dateTime */
$dateTime = $objectManager->get(DateTime::class);
/** @var Random $random */
$random = $objectManager->get(Random::class);

try {
$store = $storeManager->getStore($storeId);
} catch (Throwable $exception) {
fwrite(STDERR, "Unknown store ID: {$storeId}\n");
exit(1);
}

$read = readEmails($file, $emailColumn, $delimiter);
if (!$read['emails']) {
echo "No valid email addresses found.\n";
writeSkippedRows($read['skipped']);
exit(0);
}

$websiteId = (int)$store->getWebsiteId();
$result = importEmails(
$read['emails'],
$storeId,
$websiteId,
$dryRun,
$resource,
$storeManager,
$dateTime,
$random
);

echo ($dryRun ? "Dry run complete.\n" : "Import complete.\n");
echo 'Inserted: ' . $result['inserted'] . PHP_EOL;
echo 'Updated: ' . $result['updated'] . PHP_EOL;
echo 'Already subscribed: ' . $result['unchanged'] . PHP_EOL;
echo 'Duplicate rows ignored: ' . $read['duplicates'] . PHP_EOL;
writeSkippedRows($read['skipped']);

function readEmails(string $file, string $emailColumn, string $delimiter): array
{
$handle = fopen($file, 'rb');
$header = null;
$emails = [];
$duplicates = 0;
$skipped = [];
$lineNumber = 0;

while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
$lineNumber++;
$row = stripBom($row);

if (trim(implode('', $row)) === '') {
continue;
}

if ($header === null && rowLooksLikeHeader($row, $emailColumn)) {
$header = array_map('strtolower', array_map('trim', $row));
continue;
}

$email = extractEmail($row, $header, $emailColumn);
if (!isValidEmail($email)) {
$skipped[] = ['line' => $lineNumber, 'value' => $email];
continue;
}

$email = strtolower($email);
if (isset($emails[$email])) {
$duplicates++;
continue;
}

$emails[$email] = $email;
}

fclose($handle);

return [
'emails' => array_values($emails),
'duplicates' => $duplicates,
'skipped' => $skipped,
];
}

function importEmails(
array $emails,
int $storeId,
int $websiteId,
bool $dryRun,
ResourceConnection $resource,
StoreManagerInterface $storeManager,
DateTime $dateTime,
Random $random
): array {
$connection = $resource->getConnection();
$subscriberTable = $resource->getTableName('newsletter_subscriber');
$customerTable = $resource->getTableName('customer_entity');
$websiteStoreIds = $storeManager->getWebsite($websiteId)->getStoreIds();
$now = $dateTime->gmtDate();
$result = ['inserted' => 0, 'updated' => 0, 'unchanged' => 0];

foreach ($emails as $email) {
$subscriber = findSubscriber($resource, $subscriberTable, $email, $websiteStoreIds);

if ($subscriber && (int)$subscriber['subscriber_status'] === Subscriber::STATUS_SUBSCRIBED) {
$result['unchanged']++;
continue;
}

if ($subscriber) {
$result['updated']++;
if (!$dryRun) {
$connection->update(
$subscriberTable,
[
'subscriber_status' => Subscriber::STATUS_SUBSCRIBED,
'change_status_at' => $now,
],
['subscriber_id = ?' => (int)$subscriber['subscriber_id']]
);
}
continue;
}

$result['inserted']++;
if (!$dryRun) {
$connection->insert($subscriberTable, [
'store_id' => $storeId,
'change_status_at' => $now,
'customer_id' => findCustomerId($resource, $customerTable, $email, $websiteId),
'subscriber_email' => $email,
'subscriber_status' => Subscriber::STATUS_SUBSCRIBED,
'subscriber_confirm_code' => substr($random->getUniqueHash(), 0, 32),
]);
}
}

return $result;
}

function findSubscriber(ResourceConnection $resource, string $subscriberTable, string $email, array $storeIds): ?array
{
$connection = $resource->getConnection();
$select = $connection->select()
->from($subscriberTable)
->where('subscriber_email = ?', $email)
->where('store_id IN (?)', $storeIds)
->limit(1);

$subscriber = $connection->fetchRow($select);

return $subscriber ?: null;
}

function findCustomerId(ResourceConnection $resource, string $customerTable, string $email, int $websiteId): int
{
$connection = $resource->getConnection();
$select = $connection->select()
->from($customerTable, ['entity_id'])
->where('email = ?', $email)
->where('website_id = ?', $websiteId)
->limit(1);

return (int)$connection->fetchOne($select);
}

function extractEmail(array $row, ?array $header, string $emailColumn): string
{
if ($header !== null) {
$columnIndex = array_search(strtolower($emailColumn), $header, true);
if ($columnIndex !== false && isset($row[$columnIndex])) {
return trim((string)$row[$columnIndex]);
}
}

return trim((string)reset($row));
}

function rowLooksLikeHeader(array $row, string $emailColumn): bool
{
$columns = array_map('strtolower', array_map('trim', $row));

return in_array(strtolower($emailColumn), $columns, true);
}

function isValidEmail(string $email): bool
{
return $email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

function stripBom(array $row): array
{
if (isset($row[0])) {
$row[0] = preg_replace('/^\xEF\xBB\xBF/', '', (string)$row[0]);
}

return $row;
}

function writeSkippedRows(array $skipped): void
{
if (!$skipped) {
return;
}

echo 'Skipped invalid rows: ' . count($skipped) . PHP_EOL;
foreach (array_slice($skipped, 0, 10) as $row) {
$value = $row['value'] !== '' ? $row['value'] : '[empty]';
echo 'Line ' . $row['line'] . ': ' . $value . PHP_EOL;
}

if (count($skipped) > 10) {
echo "Only the first 10 skipped rows are shown.\n";
}
}