Hi all

I want to parse a CSV file and get the last few items (which are monthly totals), into variables.

The CSV length will change but the last 14 items are always the same - the word "Totals", 12 values, then the word "End of Report".

"Totals",2000.00,0.00,480.00,480.00,480.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,"End of Report"

From the above example I'd like to get:

$month1 = 2000.00
$month2 = 0.00
$month3 = 480.00
etc

I don't know how to parse the file then start from the end and work backwards!

Would it be best to re-order the CSV then take items 2 - 13? (and if so... how do I do that!)

I also can't look for the word "Totals" and start from there because it exists more than once in the CSV.

Can anyone suggest a way of doing this?

Many thanks

Simon

    I think I'm getting closer...

    If I do:

    $row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
    }
}
$row=$row-1;
$startpos = $num-13;
echo "Last row number = $row <br />";
echo "Start position = $startpos <br />";
fclose($handle);
?>

    This script prints data contained in 3 rows of 77 items each.

    If anyone knows how to parse the file starting at row 3, item 64, taking only 12 items - I'd get the 12 items of data I need. Because the code above tells me what the last row is, it doesn't matter if there's 3 or 50 - I'll always start at the last row at the 64th item.

    Any comments on the code above welcome too, I amended something I found on php.net.

    Simon

      Even better - I can now get the data I need. Does anyone know a neater way of doing this, or have a way of optimising this code please?

      $row = 1;
$handle = fopen("reviewbox/be.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    $startpos = $num-13;
    $row++;
}
fclose($handle);

$handle = fopen("reviewbox/be.csv", "r");
$data = fgetcsv($handle, 1000, ",");

$row=$row-1;

$item=$startpos;
$itemend=$startpos+12;

while ($item < $itemend) {
echo "Item number " . $item . " = " . $data[$item] . "<br />";
$item++;
 }

fclose($handle);

        It's not entirely clear to me what an "item" is in this case, nor exactly which rows/items you want, but you could load everything into an array, and then grab things from it as desired, using the various array functions to reverse it, sort it, grab chunks of it, etc.

        $data = array();
if(($fh = fopen('/path/to/file')) != false)
{
   while($row = fgetcsv($fh, 1000) !== false)
   {
      if(count($row) == 1 and $row[0] === null)
      {
         continue;  // skip empty rows
      }
      $data[] = $row;
   }
}
// get 12 rows starting with 3rd row (offset 2):
$myData = array_slice($data, 2, 12);
// reverse the order of the selected rows:
$myData = array_reverse($myData);
foreach($myData as $row)
{
   echo "The fifth element in this row is: " . $row[4] . "<br />";
}

        The above is untested and merely intended to give you some ideas.

          Thank you, I will have a look at that way of doing it; a lot neater!

          Simon

            4 days later

            Here is an alternate script using biterscripting.

            set $wsep = ","
var str data, lastline, month1, month2
# Read the csv file.
cat "file.csv" > $data
# Get the last line.
lex "l" $data > $lastline
# Get and assign 2nd, 3rd, etc fields.
set $month1 = { wex -p "2" $lastline }
set $month2 = { wex -p "3" $lastline }
# etc.

              That's clever - I don't know anything about biterscripting (goes to Google it...)

                NogDog, I couldn't get that way of doing it to work. I'll post some samples of what I've tried when I can.

                  13 days later

                  Working through NogDog's example and referring to the manual I have code that does what I want (which has changed slightly from the initial 'last few items' question that the post began with but I feel the content is still on topic and will still assist others with CSV parse questions).

                  I'm parsing a csv file that contains varying numbers of rows; I want to get specific elements (is that the correct term? Fields?) from each row into different arrays.

                  The code below works fine but I can't see how it knows to read the next row in the CSV file.

                  I am incrementing $row but as it's not actually used in the WHILE command, how does it know to start the next one?

                  I'm obviously happy it works but I'd really like to know how! Can anyone assist please?

                  $row = 1;
if (($handle = fopen('awreviewbox/be.csv', 'r')) !== FALSE) {  // only proceed if the file is present

  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {  // only proceed if there is data in the csv

   $num = count($data);
   // There are $num of fields in row $row
       $row++;

        for ($c=0; $c < $num; $c++) {

        // fill arrays with data
        $name[$row-1]=$data[21];
        $id[$row-1]=$data[22];
        $reference[$row-1]=$data[24];
       }
}
fclose($handle);
$totalrows = $row-1;

//  There are $totalrows in the csv file

                    When reading from a file with a function such as fgetcsv() or fgets() PHP maintains a file pointer which points to the current position in that file. The pointer is automatically incremented each time a read is done, and can be directly manipulated with e.g. fseek() or rewind().

                      Thank you!

                      So does it presume end-of-row in both these cases;

                      1) reaches the specified number of delimeters (1000 in fgetcsv($handle, 1000, ",")
                      2) reachs the end of the row

                      ?

                        As the manual entry for fgetcsv() says:

                        Reading ends when length - 1 bytes have been read, on a newline (which is included in the return value), or on EOF (whichever comes first). If no length is specified, it will keep reading from the stream until it reaches the end of the line.

                        The file pointer, btw, is bound to the resource returned by fopen().

                          Thanks again. I couldn't find (and still can't find) that quote on the manual entry for fgetcsv() so I'm grateful for the information.

                            .. and also for the fopen() information!

                              My mistake; the quote is from the entry for fgets(), which is linked to from the entry for fgetcsv(). It applies to the length parameter for the former. That parameter for fgetcsv() works slightly differently.

                                Write a Reply...