Process CSV Into Array With Column Headings For Key

Question

I have a CSV with the first row containing the field names. Example data is...

"Make","Model","Note"
"Chevy","1500","loaded"
"Chevy","2500",""
"Chevy","","loaded"

I need my data formatted in an array of key-value pairs where the key name is the column heading. I guess it would something like this for row 1:

$array = [
    "Make" => "Chevy",
    "Model" => "1500",
    "Note" => "loaded"
];

...row 2...

$array = [
    "Make" => "Chevy",
    "Model" => "1500",
    "Note" => ""
];

...and row 3...

$array = [
    "Make" => "Chevy",
    "Model" => "",
    "Note" => "loaded"
];

I'm not sure how to do this other than statically - problem is the columns with their associated data could change from one file to the next... columns rearranged, dropped, or added.

You ideas are much appreciated.

Trench

you probably mean 2500 in second array example – Belloir

Answer 1

$all_rows = array();
$header = fgetcsv($file);
while ($row = fgetcsv($file)) {
  $all_rows[] = array_combine($header, $row);
}

print_r($all_rows);

Waldheim

Thanks for the prompt response. This is close although I'm ending up with data in the keys. Don't see any column headings in any of the arrays that are returned. – Trench

@BitBucket: If you do a dump of the data that's in $all_rows, you should see an array with sub-arrays that have the header data as keys. – Waldheim

note you'll need to run through $header when you first create it to make sure you give any columns without titles dummy data like "unknown" . $x or the array combine will be different lengths – Schoenfelder

if you have issues with this approach, check your .csv file's character encoding. my file was in UTF-8 BOM format, and so my first header key was actually "[0xEF]FirstHeader", but since the BOM is invisible it took me a while to figure out. – Saltern

Answer 2

PHP offers already 99,9% of what you need within SplFileObject, you add the missing 0,1% by extending from it. In the following example CSVFile extends from it:

$csv = new CSVFile('../data/test.csv');

foreach ($csv as $line)
{
    var_dump($line);
}

With your example data:

array(3) {
  ["Make"]=>  string(5) "Chevy"
  ["Model"]=> string(4) "1500"
  ["Note"]=>  string(6) "loaded"
}
array(3) {
  ["Make"]=>  string(5) "Chevy"
  ["Model"]=> string(4) "2500"
  ["Note"]=> string(0) ""
}
array(3) {
  ["Make"]=>  string(5) "Chevy"
  ["Model"]=> string(0) ""
  ["Note"]=>  string(6) "loaded"
}

CSVFile is defined as the following:

class CSVFile extends SplFileObject
{
    private $keys;

    public function __construct($file)
    {
        parent::__construct($file);
        $this->setFlags(SplFileObject::READ_CSV);
    }

    public function rewind()
    {
        parent::rewind();
        $this->keys = parent::current();
        parent::next();
    }

    public function current()
    {
        return array_combine($this->keys, parent::current());
    }

    public function getKeys()
    {
        return $this->keys;
    }
}

If you do it this way, the details are nicely encapsulated away. Additionally it's more easy to deal with errors (e.g. count mismatch) inside the current() function so the code which makes use of the data does not need to deal with it.

Edit:

However the example given is short in terms of re-usablity. Instead of extending from SplFileObject it's much better to aggregate it:

class KeyedArrayIterator extends IteratorIterator
{
    private $keys;

    public function rewind()
    {
        parent::rewind();
        $this->keys = parent::current();
        parent::next();
    }

    public function current()
    {
        return array_combine($this->keys, parent::current());
    }

    public function getKeys()
    {
        return $this->keys;
    }
}

The code is identical but the details that were encapsulated in the constructor are left out. This reduction allows to use the type more broadly, e.g. with (but not only with) the said SplFileObject:

$file = new SplFileObject('../data/test.csv');
$file->setFlags($file::READ_CSV);

$csv = new KeyedArrayIterator($file);

foreach ($csv as $line) {
    var_dump($line);
}

If that now sounds too verbose, it again can be wrapped to give it again a nicer facade:

class CSVFile extends KeyedArrayIterator
{
    /**
     * @param string $file
     */
    public function __construct($file)
    {
        parent::__construct(new SplFileObject($file));
        $this->setFlags(SplFileObject::READ_CSV);
    }
}

Thanks to the standard decorating-ability of TraversableIterator, the original constructor code from the first example of CSVFile could just be copied 100%.

This last addition also allows to keep the original code that uses the CSVFile Iterator intact:

$csv = new CSVFile('../data/test.csv');

foreach ($csv as $line) {
    var_dump($line);
}

So just a quick refactoring to allow more code-reuse. You get a KeyedArrayIterator for free.

Omegaomelet

Could you think off hand of a way of making this handle headerless CSVs for the sake of interest? – Robles

That is pretty straight forward: Leave out the rewind function and pass keys in the constructor instead. If you need more flexibility, I've put some code into a gist with examples, but it's still pretty alpha: gist.github.com/4153380 – Omegaomelet

Just tested this and I love the idea, but it seems to be much less performant than fgetcsv like here https://mcmap.net/q/301308/-csv-to-associative-array – Boatyard

@giorgio79: Well, this Iterator based approach is more useable if you don't want to store the whole file in memory. So it's less about speed but more about memory. So just showing alternative ways, each one has it's pro / cons. – Omegaomelet

Works great! To use a different delimiter (like a colon ;), add $this->setCsvControl( ';' ); in the CSVFile constructor – Ferrol

is this class on packagist :-D ? – Geordie

I know this is a pretty old post, but I used this with great success in a project recently, so thank you. One issue I did run into was not being able to use the SKIP_EMPTY flag. Is there a way to add that? – Kassiekassity

@AdamChristianson: Did you try like $...->setFlags(SplFileObject::READ_CSV | SKIP_EMPTY); ? Or was it this way and it was ineffective? – Omegaomelet

Answer 3

$csv_data = array_map('str_getcsv', file('Book.csv'));// reads the csv file in php array
$csv_header = $csv_data[0];//creates a copy of csv header array
unset($csv_data[0]);//removes the header from $csv_data since no longer needed
foreach($csv_data as $row){
    $row = array_combine($csv_header, $row);// adds header to each row as key
    var_dump($row);//do something here with each row
}

Befriend

$csv_header = $csv_data[0]; unset($csv_data[0]); equals $csv_header = array_shift($csv_data); and has the added bonus of not causing problems when [0] doesn't exist.l, as shown by Azam. – Alexaalexander

Answer 4

function processCsv($absolutePath)
{
    $csv = array_map('str_getcsv', file($absolutePath));
    $headers = $csv[0];
    unset($csv[0]);
    $rowsWithKeys = [];
    foreach ($csv as $row) {
        $newRow = [];
        foreach ($headers as $k => $key) {
            $newRow[$key] = $row[$k];
        }
        $rowsWithKeys[] = $newRow;
    }
    return $rowsWithKeys;
}

Marsala

Answer 5

At this point I'm assuming you've already solved the issue but thought I'd throw in a suggested way around this, probably not the best/most elegant solution but it does the trick:

$row = 1;
$array = array();
$marray = array();
$handle = fopen('file.csv', 'r');
if ($handle !== FALSE) {
    while (($data = fgetcsv($handle, 0, ',')) !== FALSE) {
        if ($row === 1) {
            $num = count($data);
            for ($i = 0; $i < $num; $i++) {
                array_push($array, $data[$i]);
            }
        }
        else {
            $c = 0;
            foreach ($array as $key) {
                $marray[$row - 1][$key] = $data[$c];
                $c++;
            }
        }
        $row++;
    }
    echo '<pre>';
    print_r($marray);
    echo '</pre>';
}

Defence

Answer 6

Try this

$csv = array_map("str_getcsv", file('file.csv', FILE_SKIP_EMPTY_LINES));    
$header = array_shift($csv); // get header from array

foreach ($csv as $key => $value) {    
    $csv[$key] = array_combine($header, $value);
    var_dump($csv[$key]['Model']);
}

var_dump($csv);

Bays

Answer 7

Try with this code:

$query = "SELECT * FROM datashep_AMS.COMPLETE_APPLICATIONS";
$export= mysql_query($query);
$first = true;
$temp = $export[0];
//echo "<pre>"; print_r($first); exit;

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename=file.csv');
header('Pragma: no-cache');
header("Expires: 0");

$outstream = fopen("php://output", "w");



foreach($export as $result)
{
    if($first){
        $titles = array();
        foreach($temp as $key=>$val){
            $titles[] = $key;
        }
        //print_r ($titles);exit;
        fputcsv($outstream, $titles);
    }
    $first = false;
    fputcsv($outstream, $result);
}

fclose($outstream);

Thanks

Saucier

Answer 8

In the answer of Tim Cooper above, instead of

$all_rows = array();
$header = null;
while ($row = fgetcsv($file)) {
    if ($header === null) {
        $header = $row;
        continue;
    }
    $all_rows[] = array_combine($header, $row);
}

I would code, in a more elegant and efficient way:

$rows = null;
$header = fgetcsv($file);
while ($row = fgetcsv($file)) {
    $rows[] = array_combine($header, $row);
}

Florio

Answer 9

The array_combine() function only works if header colums match the data colums otherwise an error is thrown.

Gaylord

This should be a comment to an existing answer and not an answer it self? – Shavon

Thanks but thought it might help to focus the solutions; It is a tight place already on this subject - with so many answeres . I only mentioned it to avoid confusion. Many PHP selfproclaimed "experts" have published solutions using array_combine function but failed to notice that there are flows in it. My solution would be to edit the CSV file headers so to match the data and then store the output prior to making use of array_combine... – Gaylord

@Pellumb: unless the error is intended. The remark (and IMHO this qualifies well as a comment, perhaps best placed under the question so it's more visible) is valid and if you're running most (if not all) of the examples (even those that do not use array_combine(), but especially those without) do not properly process comments in a CSV file/buffer/stream that come before the actual header columns. Another common problem is a terminator line (or multiple) at the end (which array_combine() also highlights with error messages). – Omegaomelet