CODEWITHSUNDEEP
phpparsingcsvexport-to-csv
SUDEEP VICTOR
SUDEEP VICTOR

Reputation: 3

how to parse multiple rows from a csv file using php

i have 2 .csv files which have first column having similar values, the code which is included does the job only for one row, it does only one iteration, kindly help me modify the code...

hostrefined.csv contents...
name1,23423,detail1
name2,12312,detail2
name3,21312,detail3

hbarefined.csv contents...
name1,det1,det2,2323
name1,det3,det4,23434
name1,det5,det6,34343
name2,det9,det7,232322
name3,det10,det28,232

the output file has to be like

name1,23423,detail1,det1,det2,2323
name1,23423,detail1,det3,det4,23434
name1,23423,detail1,det5,det6,34343
name2,12312,detail2,det9,det7,232322
name3,21312,detail3,det10,det28,232

here goes the code

$handle = fopen("hostsrefined.csv", "r");
$handle1 = fopen("hbarefined.csv", "r");

$fp=fopen('test.csv','w');
while($data1 = fgetcsv($handle1, 1000, ","))
{
while($data = fgetcsv($handle, 1000, ","))
{
    if($data1[0]==$data[0])
    {
        for($s=0;$s<9;$s++)  // to write data from first .csv file
        {

            fwrite($fp,$data[$s]);
            fwrite($fp,",");
        }
        for($s=1;$s<6;$s++)     // to write data frm second .csv file
        {


                fwrite($fp,$data1[$s]);
                fwrite($fp,",");

        }

    }

    fwrite($fp,"\n");
}fwrite($fp,"\n");

}

Upvotes: 0

Views: 1426

Answers (1)

Jojo
Jojo

Reputation: 2760

First, lets debug your script. The problem why you get your desired result only for the first row is obvious if you follow your algorithm and take a closer look on php's file-handling.

You are opening both files at the beginning of your script. PHP sets a file-pointer for each file at the beginning of the file. Let's say at the first line for easier understanding.

Your first while-loop goes through your details-file (hbarefined.csv) where you want to join contents based on your first field that is equal in each file. Then, you start a while-loop that reads each line of hostrefined.csv. If you find the line with your desired primary-key, you join the contents of both lines from hbarefined and hostrefined and write it to test.csv. Here we have the first 2 glitches of your script.

  1. You write a new line to test.csv even if you don't match with your primary-key. Looks ugly in the result.

  2. You are creating your csv-format for test.csv yourself. Don't! Have a look at fputcsv()

What you don't know is that your second while-loop moves your file-pointer for hostrefined.csv one line forward for each iteration, but does not reset it when reaching the end of the file. Because you read through the whole hostrefined.csv for each row of hbarefined.csv, you reached the end after the first iteration of your first while-loop. The second and all upcoming iterations of your first while-loop start reading at the end of hostrefined.csv and will therefor never find a matching line. You need to use rewind() at the end of each iteration of your first while-loop.

$handle = fopen("hostsrefined.csv", "r");
$handle1 = fopen("hbarefined.csv", "r");

$fp=fopen('test.csv','w');
while($data1 = fgetcsv($handle1, 1000, ","))
{
while($data = fgetcsv($handle, 1000, ","))
{
    if($data1[0]==$data[0])
    {
        for($s=0;$s<9;$s++)  // to write data from first .csv file
        {

            fwrite($fp,$data[$s]);
            fwrite($fp,",");
        }
        for($s=1;$s<6;$s++)     // to write data frm second .csv file
        {


                fwrite($fp,$data1[$s]);
                fwrite($fp,",");

        }

    }

    fwrite($fp,"\n");
}
fwrite($fp,"\n");
rewind($handle);
}

That will fix your script.

Some general notes: Your code is very hard to read, that made it hard for me to debug and fix. Use indentation properly, and use new lines for new commands, consider this for example:

while($data = fgetcsv($handle, 1000, ","))
{
    // ...some code
}fwrite($fp,"\n");

Also, try to be clear with your variable-names. using $s as index-variable in a for-loop 2 times in a row confuses a lot. What is $fp? you get me...

I did a rewrite of your code for your reference:

<?php
/**
 * Goal of this script is to read a csv-file with a primary-key (input-primary.csv)
 * in field 0 and join contents from a second csv-file (input-detail.csv).
 * Each row in input-detail.csv has the primary key from input-primary.csv
 * in field 0 as well.
 * This script needs php version 5.4 o higher
 */

/**
 * First, we define some helper functions
 */

/**
 * Read csv-contents from $filename and return it indexed by primary-key.
 * Primary-key is in field 0
 * 
 * @param string $filename file to read
 * @return array
 */
function getCsvContentIndexedByPrimaryKey($filename)
{
    $handle = fopen($filename, 'r');
    $indexedContents = [];
    while (false !== $row = fgetcsv($handle)) {
        $primaryKey = $row[0];
        $indexedContents[$primaryKey] = $row;
    }

    return $indexedContents;
}

/**
 * Joins contents from $row and $indexedContents by index taken from 
 * field 0 of $row. Primarykey-field of $row will be unset. If no content
 * was found in $indexedContents an exception is thrown with the primary-key.
 * 
 * @param array $row row from input-detail.csv
 * @param array $indexContents result from getCsvContentIndexedByPrimaryKey
 * @return array joined content
 * @throws Exception if no content for $row[0] was found in $indexedContents
 */
function joinRowByPrimaryKey($row, $indexedContents)
{
    $primaryKey = $row[0];
    if (isset($indexedContents[$primaryKey])) {
        $contentToJoin = $indexedContents[$primaryKey]; unset($row[0
        ]); return array_merge($contentToJoin, $row);
    }
    throw new \Exception(sprintf(
        'Primary-key %s not found in indexed-contents', $row[0]));
}

/**
 * Now, here we go.
 */

// we create the indexed-content and initialize our output and error-handling
$indexedContents = getCsvContentIndexedByPrimaryKey('input-primary.csv');
$outputContent = [];
$errors = [];

// now we read the second csv-file
$handle = fopen('input-detail.csv', 'r');
while (false !== $row = fgetcsv($handle)) {
    try {
        $outputContent[] = joinRowByPrimaryKey($row, $indexedContents);
    } catch (\Exception $e) { // we catch the exception from joinRowByPrimaryKey here
        $errors[$row[0]] = $e->getMessage();
    }
}

// Finally, we create our result-file and write our output-content to it
// note the usage of fputcsv @see http://php.net/fputcsv
// there is no need to manually write commas, line-endings and the like
$handle = fopen('result.csv', 'w');
foreach ($outputContent as $row) {
    fputcsv($handle, $row);
}

// and print our errors
foreach ($errors as $error) {
    echo $error . PHP_EOL;
}

Code with sample csv-files is on github as well: https://github.com/jbrinksmeier/so-28431197

Enjoy

Upvotes: 1

Related Questions