PHP function to convert a CSV to JSON

I have the following CSV :

siret;nom long;nom_court;type;rue;code_postal;ville;pays
123;Nom long;Nom_court;Entreprise;rue du lac;21789;Marseille;France

And I need to obtain this one:

{

    "1": {

        "siret": "123",

        "nom long": "Nom long",

        "nom_court": "Nom_court",

        "type": "Entreprise",

        "address": {

            "ville": "Marseille",

            "code_postal": "21789",

            "country": "France"

        },

        ...

    },

I made this operation often and to simply my job I defined a generic function.

Initialization

First, we need to define elements like the separator by defining a constant : const SEPARATOR = ';';

We also need to identify columns associate to the JSON file. For example, in our CSV file, siret is at the first position. So we set : $columns[0]="siret";

Then longname is at the second position, so we set  $columns[1]="longname"; and so on.

Thus, at the beginning of our php file, we defined an array and set the correspondance between CSV and JSON :

$ini_array=array();
const SEPARATOR = ';';
// CSV files columns
$columns = array();
$columns[0]="siret";
$columns[1]="longname";
$columns[2]="shortname";
$columns[3]="type";
$columns[4]="rue";
$columns[5]="zipcode";
$columns[6]="city";
$columns[7]="country";

The next step is to identify dependency between elements. By using a simple array we set for example the address dependency :

$jsonHierarchy = array(

    "ville" => "address",

    "code_postal" => "address",

    "country" => "address",

);

In this array, we give all sub element of address in order to obtain this JSON structure :

        "address": {

            "ville": "Marseille",

            "code_postal": "21789",

            "country": "France"

These variables will be used by the convert method.

Read the CSV file

In my case, I used a config file where I stored the path of the CSV file path : PI_import_pathfile="C:\\tmp\file.csv"

I also have severeal environnement so I need to choose between them (Linux or Windows ) :

if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {

    $ini_array = parse_ini_file("C:\\tmp\\config.ini");

} else {

    $ini_array = parse_ini_file("/root/config/config.ini");

}

$file=$ini_array["PI_import_pathfile"];

Call the method  convert_CSV_to_JSON()

The next step is to read the CSV file and convert it in JSON file :

if( file_exists($file)) {

    $csv = file_get_contents($file);

    $json = convert_CSV_to_JSON($csv, $columns, $jsonHierarchy);

    echo json_encode($json, JSON_PRETTY_PRINT);

} else {

    echo "File doesn't exist";

}

Method convert_CSV_to_JSON()

This is the code of the convert CSV to JSON method :

/**
 * Convert CSV content in an JSON array
 * Need to be improve for global use
 * @param String $csv_data Content of csv file
 * @param array $columns Field array of you Excel column
 *     e.g     // Excel column
 *              $columns[0]="uid";
 *              $columns[1]="code";
 * @param array $jsonHierarchy Field hierarchy
 * @return array for json encode
 */
function convert_CSV_to_JSON($csv_data, $columns, $jsonHierarchy){

    $json = array();
    $Data = str_getcsv($csv_data, "\n"); //parse the rows
    foreach($Data as $row_index => $Row) {
        $data = str_getcsv($Row, SEPARATOR);

        // skip the first row, since it's the headers
        if($row_index === 0) {
            continue;
        }
        $addr=array();
        foreach ($data as $column_index => $column_value) {

            // get the key for each entry
            if (isset($columns[$column_index])) {

                $label = $columns[$column_index];
                if (array_key_exists($label, $jsonHierarchy)) {

                    $parent = $jsonHierarchy[$label];
                    $addr[$label]=trim($column_value);
                    $json[$row_index][$parent]=$addr;
                } else {
                    $json[$row_index][$label] = trim($column_value);
                }
            }

        }
    }

    return $json;
}

Full code to convert CSV to JSON

<?php
/**
 * Read a csv file and produce an JSON output in order to import organisations.
 *
 * Note :
 * - The column order is important and set in the convert_CSV_to_JSON§.
 * - csv in UTF8 format.
 *
 * e.g :
 * php convertCSVtoJSON.php > organisationPI.json
 * php public/index.php oscar organizationsjson:sync /root/PI/organisationPI.json
 *
 */
$ini_array=array();
const SEPARATOR = ';';
// Excel columns
$columns = array();
$columns[0]="siret";
$columns[1]="longname";
$columns[2]="shortname";
$columns[3]="type";
$columns[4]="rue";
$columns[5]="zipcode";
$columns[6]="city";
$columns[7]="country";

/*$columns[0]="uid";
$columns[1]="code";
$columns[2]="shortname";
$columns[3]="longname";
$columns[4]="type";
$columns[5]="address";
$columns[6]="city";
$columns[7]="zipcode";
$columns[8]="country";
$columns[9]="phone";
$columns[10]="url";
$columns[11]="email";
$columns[12]="siret";
$jsonHierarchy = array(
    "address1" => "address",
    "city" => "address",
    "zipcode" => "address",
    "country" => "address",
);
//PI_import_pathfile="C:\\tmp\\tmp.csv"
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
    $ini_array = parse_ini_file("C:\\tmp\\config.ini");
} else {
    $ini_array = parse_ini_file("/tmp/config.ini");
}
$file=$ini_array["PI_import_pathfile"];
if( file_exists($file)) {
    $csv = file_get_contents($file);
    $json = convert_CSV_to_JSON($csv, $columns, $jsonHierarchy);
    echo json_encode($json, JSON_PRETTY_PRINT);
} else {
    echo "Le fichier d'import CSV n'existe pas !";
}


/**
 * Convert CSV content in an JSON array
 * Need to be improve for global use
 * @param String $csv_data Content of csv file
 * @param array $columns Field array of you Excel column
 *     e.g     // Excel column
 *              $columns[0]="uid";
 *              $columns[1]="code";
 * @param array $jsonHierarchy Field hierarchy
 * @return array for json encode
 */
function convert_CSV_to_JSON($csv_data, $columns, $jsonHierarchy){

    $json = array();
    $Data = str_getcsv($csv_data, "\n"); //parse the rows
    foreach($Data as $row_index => $Row) {
        $data = str_getcsv($Row, SEPARATOR);

        // skip the first row, since it's the headers
        if($row_index === 0) {
            continue;
        }
        $addr=array();
        foreach ($data as $column_index => $column_value) {
            // get the key for each entry
            if (isset($columns[$column_index])) {
                $label = $columns[$column_index];
                if (array_key_exists($label, $jsonHierarchy)) {


                    $parent = $jsonHierarchy[$label];
                    $addr[$label]=trim($column_value);
                    $json[$row_index][$parent]=$addr;
                } else {
                    $json[$row_index][$label] = trim($column_value);
                }
                //exit(1);
                /*switch ($column_index) {
                    case 5:
                        $addr["address1"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 6:
                        $addr["city"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 7:
                        $addr["zipcode"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    case 8:
                        $addr["country"]=trim($column_value);
                        $json[$row_index]["address"]=$addr;
                        break;
                    default:
                        $json[$row_index][$label] = trim($column_value);
                }*/
            }
        }
    }
    return $json;
}

Version : PHP 7.3.1