Merge different language csv to determine which phrases missing a translation

Question

Currently we have our shop only in English language and want to add an Italian one. So we have collected all phrases with bin/magento i18n:collect-phrases --magento --output="en_EN.csv".
We installed this package to get Italian translations. Obviously the package doesn't cover all our phrases. Is there already a way or package to merge the Italian translations into the English ones? Our goal is to have one file with all phrases and the Italian translation behind it if present. If there is no Italian translation it should be only displayed the Phrase.

I already tried bin/magento i18n:pack --mode=merge -d it_IT.csv en_EN but i think i don't get right what the command does. I think it only writes the translations from the (it_IT.csv) into all translation files of en_EN in all packages.

Answer 1

In the next step I merged en_EN.csv and it_IT.csv via PHP into two new files. One containing the translated Version with EN and IT in one File and the other containing all English phrases that got no translation from the it_IT.csv file.

<?php

ini_set('max_execution_time', 0);

$translatedArray = [];            //translations done $noTranslationFoundArray = [];      //left to translate

$enENArray = BuildArray("en_EN.csv");
$itITArray = BuildArray("it_IT.csv");

foreach ($enENArray as $enEN) {
    $found = false;
    if ($enEN==false) continue;
    foreach ($itITArray as $itIT) {
        if ($enEN[0] == $itIT[0] && $enEN[2] == $itIT[2] && $enEN[3] == $itIT[3]) {
            array_push($translatedArray, $itIT);
            $found = true;
            break;
        }
    }
    if(!$found){
        array_push($noTranslationFoundArray,$enEN);
    }
}

buildCsv($translatedArray,"translatedEnIt.csv"); 
buildCsv($noTranslationFoundArray,"noTranslationFound.csv");

function buildCsv($array, $filename){
    $oFile = fopen($filename, "w");
    foreach ($array as $element){
        fputcsv($oFile, $element);
    }
    fclose($oFile);
}

function buildArray($fileName) {
    $oFile = fopen("$fileName", "r");
    $array = [];
    while (!feof($oFile)) {
        $array[] = fgetcsv($oFile, 0, ",");
    }
    fclose($oFile);
    return $array;
}

Answer 2

You could use csvkit to diff the first columns of the CSVs

In this example we compare de_DE with the theme we have to see what is not translated yet

# download existing language pack to de_DE.csv first
$ csvcut -c 1 de_DE.csv | sort > de_DE-1.csv
$ csvcut -c 1 theme-strings.csv | sort > theme-strings-1.csv
$ diff -u de_DE-1.csv theme-strings-1.csv | grep ^+ | cut -d"+" -f2 > untranslated.csv