Concatenate Jira Comments to Description for Import

concatenate-jira-comments-to-description.md

Purpose

Reformat Jira ticket export to include comments in the ticket description and to update attachment URLs to include an authorization key.

Background

Jira provides the ability to import from CSV two different ways:

1. External System Import: A more powerful import accessible only to admins and that is potentially too powerful, auto-creating users and custom fields in a way that may not be desired. https://support.atlassian.com/jira-cloud-administration/docs/import-data-from-a-csv-file/
2. Import Issues: A more limited import available to all users, but which (contrary to the documentation as of Dec 2021) does not support importing comments natively. This script will append each comment to the ticket description with the author and date so that the data is not lost. https://support.atlassian.com/jira-software-cloud/docs/create-issues-using-the-csv-importer/

Usage

The script will process multiple CSV files, looking for each in a folder called input in the current working directory and will save the reformatted to a folder called output. This is to, for example, export/import each project separately as its own file.

Since users may now identified in exports by a GUID and not a regular username, the script will look up the users in an external users file exported and saved next to the file as users.csv. See: https://support.atlassian.com/organization-administration/docs/export-users-from-a-site/

Attachments are updated with the JIRA credentials (set in the JIRA_API_USER_PASS constant below) to allow them to be side-loaded into the new instance. See: https://confluence.atlassian.com/cloudkb/importing-attachments-into-jira-cloud-using-csv-external-system-importer-966669960.html There may also be system users that are not in this file. These can be mapped manually in the EXTRA_USERS constant below.

reformat.php

<?php
const JIRA_API_USER_PASS = 'user:password';
const JIRA_HOSTNAME = 'yourinstancehere.atlassian.net';
const EXTRA_USERS = [
    '557058:0d45f440-dd16-437d-98f0-d4c44a18bd3a' => 'Client'
];

const COMMENT_FIELDS = ['date', 'author', 'text'];

if (!ini_get("auto_detect_line_endings")) {
    ini_set("auto_detect_line_endings", '1');
}

function importUsers() {
    $file = fopen('users.csv', 'r');
    $headers = fgetcsv($file);

    $users = [];
    while ($csvLine = fgetcsv($file)) {
        $userData = array_combine($headers, $csvLine);
        $users[$userData['User id']] = $userData['User name'];
    }

    return $users;
}

function transformTickets($file, $outputDir, $users) {
    $inputFile = fopen($file, 'r');
    $outputFile = fopen($outputDir . '/clean.' . basename($file), 'w');

    $descriptionIndex = null;
    $attachmentIndices = [];
    $commentIndices = [];
    $unsetIndices = [];
    $headers = fgetcsv($inputFile);
    foreach ($headers as $i => $header) {
        switch ($header) {
            case 'Description':
                $descriptionIndex = $i;
                break;
            case 'Attachment':
                $attachmentIndices[] = $i;
                break;
            case 'Comment':
                $commentIndices[] = $i;
                $unsetIndices[] = $i;
                break;
            case 'Watchers':
            case 'Watchers Id':
            case 'Log Work':
                $unsetIndices[] = $i;
                break;
        }
    }
    fputcsv($outputFile, array_diff_key($headers, array_flip($unsetIndices)), ',', '"', '⚑');

    // NOTE: Using '⚑' as an unlikely character because not yet running PHP7.4 locally where proprietary escape can be disabled.
    while ($csvLine = fgetcsv($inputFile, 0, ',', '"', '⚑')) {
        foreach ($attachmentIndices as $attachmentIndex) {
            $csvLine[$attachmentIndex] = str_replace(
                'https://' . JIRA_HOSTNAME . '/',
                'https://' . JIRA_API_USER_PASS . '@' . JIRA_HOSTNAME . '/',
                $csvLine[$attachmentIndex]
            );
        }

        foreach ($commentIndices as $commentIndex) {
            if (empty($csvLine[$commentIndex])) continue;

            $comment = array_combine(COMMENT_FIELDS, explode(';', $csvLine[$commentIndex], 3));
            $comment['author'] = $users[$comment['author']] ?? 'Unknown User';
            $comment['text'] = preg_replace_callback('/\[~accountid:([a-z0-9:-]+)]/', function ($matches) use ($users) {
                return '@' . $users[$matches[1]] ?? 'Unknown User';
            }, $comment['text']);

            $csvLine[$descriptionIndex] .= "\n\nComment from {$comment['author']} on {$comment['date']}:\n{$comment['text']}";
        }

        foreach ($unsetIndices as $index) {
            unset($csvLine[$index]);
        }

        fputcsv($outputFile, $csvLine, ',', '"', '⚑');
    }
}

foreach (glob('input/*.csv') as $filename) {
    transformTickets($filename, 'output', importUsers() + EXTRA_USERS);
}

users.csv