Parsing CSV file + appending data to another file by unique ID key

[slim_swan]

I have multiple CSV files (example file.csv) that need to be read, and the data needs to be compared to the data in another csv file (data.csv) by an ID key which is in the first column of the file

There are multiple rows and columns with data.

 

First rows of the file is header column info so the first row needs to be skipped

the data which is in the selected row of the file (file.csv) needs to be compared to data in data.csv by the ID key

 

when the same ID key is found in a row, the data needs to be appended to the end of the row of that same ID key row in data.csv (everything except the ID key is copied to file.csv)

CSV files are delimited by ; data is UTF-8 - Croatian

I've made a script to parse through a file (data.csv) and print it but ain't that good with working multiple files and arrays to make it check the data from file.csv and append it...help?

<?php
$row = 1;
if (($xxa = fopen("data.csv", "r")) !== FALSE) {
	echo "<meta charset=\"UTF-8\">";
    while (($data = fgetcsv($xxa, 1000, ";")) !== FALSE) {
	if ($row==1) { $row++; continue; }
        $num = count($data);
        echo "<p>$row. <i>red: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "</br>\n";
    }
    }
    fclose($xxa);
}

?>

-----
I've googled and found this other script online (other.php) which is awesome because it puts all the data in arrays but I have no idea how to select that first cell with an ID key in one file, and copy that row and append it to another csv file at the end of that ID key row :/

<?php
/**
 * Convert a comma separated file into an associated array.
 * The first row should contain the array keys.
 * 
 * Example:
 * 
 * @param string $filename Path to the CSV file
 * @param string $delimiter The separator used in the file
 * @return array
 * @link http://gist.github.com/385876
 * @author Jay Williams <http://myd3.com/>
 * @copyright Copyright (c) 2010, Jay Williams
 * @license http://www.opensource.org/licenses/mit-license.php MIT License
 */
function csv_to_array($filename='', $delimiter=',')
{
	if(!file_exists($filename) || !is_readable($filename))
		return FALSE;
	
	$header = NULL;
	$data = array();
	if (($handle = fopen($filename, 'r')) !== FALSE)
	{
		while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE)
		{
			if(!$header)
				$header = $row;
			else
				$data[] = array_combine($header, $row);
		}
		fclose($handle);
	}
	return $data;
}
/**
 * Example
 */
print_r(csv_to_array('example.csv'));
?>

[Jacques1]

I would do this in three steps:

• First read the entire content of data.csv into an associative array where the ID is the key and the array of all other fields is the value. For example, the row “123;foo;bar” would become the mapping “123” => [“foo”, “bar”]
• Loop through each of the other CSV files line by line, look up the ID in the data.csv array and append the corresponding array to the current row.
• Write each extended row into a new CSV file.

 

The first step would look something like this:

<?php

const CSV_DELIM = ';';
const CSV_MAX_ROW_LENGTH = 0;    // 0 means "no limit"



$data = [];

$data_handle = fopen(__DIR__.'/data.csv', 'r');
$data_columns = fgetcsv($data_handle, CSV_MAX_ROW_LENGTH, CSV_DELIM);
while ($data_row = fgetcsv($data_handle, CSV_MAX_ROW_LENGTH, CSV_DELIM))
{
    $id = $data_row[0];
    $data[$id] = array_slice($data_row, 1);
}

var_dump($data);

Edited March 4, 2016 by Jacques1

[QuickOldCar]

Working with multiple and large csv files, comparing and manipulating data is not a good way to go about this.

Use PDO with prepared statements and a database.

Normalize your database structure and associate the id's another table.

Once you do this would be lots easier even if you still have to read from a single csv file and insert new data into the database under those single id's.

 

Lets get back to comparing the 2 csv files.

When dealing with files the entire file is loaded into memory even if just need a single line, especially when need to loop through and check each line.

In your case have to load 2 files.

 

Take this example, you can then compare the arrays by it's line id in whatever way desire, then use array_merge(),array_combine() or as you wish to manipulate the data.

 

It's hard to assist any more without having samples of your data and expected changes you desire.

<?php
$errors = array();

$the_file = "file.csv";
$the_data = "data.csv";

if (is_file($the_file)) {
    if (!is_readable($filename)) {
        $errors[] = "$the_file not readable"; 
    }
} else {
    $errors[] = "$the_file is missing";
}

if (is_file($the_data)) {
    if (!is_readable($the_data)) {
        $errors[] = "$the_data not readable"; 
    }
    if (!is_writable($the_data)) {
        $errors[] = "$the_data not writable";
    }
} else {
    $errors[] = "$the_data is missing";
}

function array_by_id($file)
{
    $array         = file($file);
    $exploded_data = array();
    $explode       = array();
    foreach ($array as $key => $line) {
        if ($key != 0) {
            $line                    = trim($line);
            $explode                 = explode(";", $line);
            $line_id                 = $explode['0'];
            $exploded_data[$line_id] = $line;
        }
    }
    
    return $exploded_data;
}

if (empty($errors)) {
    
    $file_array = array_by_id($the_file);
    $data_array = array_by_id($the_data);
    
    //preview data
    echo "<pre>";
    print_r($file_array);
    echo "</pre>";
    
    echo "<pre>";
    print_r($data_array);
    echo "</pre>";
    
} else {
    foreach ($errors as $error) {
        echo "$error <br />";
    }
}
?>