Extract Json data from a countries.csv file on github, and create a seperate array of timezones

Question

Country.csv

this is countries.csv file, and i want to extract all the timezones from it, which is its 14th colomn, and the data in there is not properly json formatted. I'm trying to parse the json but it failed. Actually, I want to create an array of timezones like this

    [0] => {zoneName:'Asia -> Kabul',gmtOffset:16200,gmtOffsetName:'UTC+04:30',abbreviation:'AFT',tzName:'Afghanistan Time'}
    [1] => {zoneName:'Europe -> Mariehamn',gmtOffset:7200,gmtOffsetName:'UTC+02:00',abbreviation:'EET',tzName:'Eastern European Time'}
    [2] => {zoneName:'Europe -> Tirane',gmtOffset:3600,gmtOffsetName:'UTC+01:00',abbreviation:'CET',tzName:'Central European Time'}
    [3] => {zoneName:'Africa -> Algiers',gmtOffset:3600,gmtOffsetName:'UTC+01:00',abbreviation:'CET',tzName:'Central European Time'}
    [4] => {zoneName:'Pacific -> Pago_Pago',gmtOffset:-39600,gmtOffsetName:'UTC-11:00',abbreviation:'SST',tzName:'Samoa Standard Time'}
    [5] => {zoneName:'Europe -> Andorra',gmtOffset:3600,gmtOffsetName:'UTC+01:00',abbreviation:'CET',tzName:'Central European Time'}
    [6] => {zoneName:'Africa -> Luanda',gmtOffset:3600,gmtOffsetName:'UTC+01:00',abbreviation:'WAT',tzName:'West Africa Time'}

what i'm doing, is this in App\Http\Controllers\TestController::class is this

    public function timezone(): void {
        $data = [];

        if (($open = fopen(__DIR__ . '/countries.csv', 'r + b')) !== FALSE) {
            while (($singleRecord = fgetcsv($open, NULL, ',')) !== FALSE) {
                $data[] = $singleRecord;
            }
            fclose($open);
        }

        $data = $this->removeCharacters($data, ['[', ']']);
        $data = $this->removeCharacters($data, (array)'\/', " -> ");

        // $data = $this->removeCharacters($data, (array)'{}', '');
        // dd(explode('},', $data[33][14]));
        // dd(explode('},', $this->longJson));
        // dd(explode(',', str_replace(['{', '}'], '', $data[167][14])));

        $singleArray = [];
        $count = count($data);
        $itemsArray = [];

        for ($i = 1; $i < $count; $i++) {

            $singleArray[] = explode('},', $data[$i][14]);

            foreach ($singleArray as $item) {

                foreach ($item as $singleItem) {

                    $itemsArray[] = $singleItem;

                }
            }
        }
        $itemsArray = array_unique($itemsArray);

        $this->printFormattedData($itemsArray);
    }
 private function removeCharacters($hayStack, array $charsArray, $character = ''): array {
        $tempArray = [];
        foreach ($hayStack as $item) {
            $tempArray[] = str_replace($charsArray, $character, $item);
        }
        return $tempArray;
    }

    private function printFormattedData($data): void {
        echo '<pre>';
        print_r($data);
        echo '</pre>';
    }

Answer 1

Using regexp its not perfect solution, but you can transform timezone data to correct json format using function like this:

public function fixJson(string $str): string {
    return preg_replace(
        '/(?<=(\{|\,))(\w+)(?=\:)/',
        '"$2"',
        str_replace("'", '"', $zoneRaw) // may not work properly, if values may contain apostroph symbols, but seems not actual for your case
    );
}

So, use this function:

$this->fixJson($data[$i][14]); // returns json string
json_decode($this->fixJson($data[$i][14])); // returns json decoded array

See usage example here https://sandbox.onlinephpfunctions.com/c/88f21

Answer 2

Following code would do, what you aim.

Please do not forget to mark this answer as ACCEPTED and thumbs up if it solves your problem, so that the work of the developers who help is appreciated and other developers can see in question list, that your question has already an accepted answer.

$lines = file("countries.csv");
array_shift($lines); // remove the first line with column names

$searchReplace  = ['\/' => '->'];
$search = array_keys($searchReplace);
$replace = array_values($searchReplace);

$jsonFormattedTimeZones = [];
foreach($lines as $line)
{
    $line = trim(str_getcsv($line)[14], " []");
    $line = str_replace($search, $replace, $line);
    $jsonFormattedTimeZones[] = $line;
}

print_r($jsonFormattedTimeZones);