Extract Json data from a countries.csv file on git…

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 AppHttpControllersTestController::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

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

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

Advertisement

Answer