Format CSV File Embedded in PHP

[jcollier]

I found the following forum topic and have implemented the solution provided at the bottom:

 

http://www.phpfreaks.com/forums/index.php?topic=310258.0

 

<?php 
$cnx = fopen("data/Book1.csv", "r"); //open example.csv
echo("<table>\n\t<tbody>\n"); // echo the table
while (!feof ($cnx)) { // while not end of file
$buffer = fgets($cnx); // get contents of file (name) as variable
$values = explode(",", $buffer); //explode "," between the values within the contents
echo "\t\t<tr>\n";
for ( $j = 0; $j < count($values); $j++ ) {  //
	echo("\t\t\t<td>$values[$j]</td>\n");
}
echo"\t\t</tr>\n";
};
echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

It works great.  One question though - - what might it take to add an if/else statement that inserted the following for the first row in the CSV file ...

 

echo("\t\t\t<th>$values[$j]</th>\n");

 

... and inserted the following for every row after the first ...

 

echo("\t\t\t<td>$values[$j]</td>\n");

 

This way I can better control my table through CSS.  Any ideas?

[codebyren]

Something like this:

 

<?php
$i = 1;  # counter variable (to note which row we're working with)
while ( ! feof($cnx)) {
    if ($i === 1) {
        // this is the first row - use <th> cells
    }
    else {
        // this is NOT the first row - use <td> cells
    }
    $i++;  # increment the counter
}
?>

[jcollier]

Something like this:

 

<?php
$i = 1;  # counter variable (to note which row we're working with)
while ( ! feof($cnx)) {
    if ($i === 1) {
        // this is the first row - use <th> cells
    }
    else {
        // this is NOT the first row - use <td> cells
    }
    $i++;  # increment the counter
}
?>

 

I don't think I've implemented what you intended correctly.  Here's what I have:

 

<?php 
$cnx = fopen("data/Book1.csv", "r"); //open csv file
echo("<table>\n\t<tbody>\n"); // echo the table
while (!feof ($cnx)) { // while not end of file
$buffer = fgets($cnx); // get contents of file (name) as variable
$values = explode(",", $buffer); //explode "," between the values within the contents
echo "\t\t<tr>\n";
$i = 1; // # counter variable (to note which row we're working with)
	while ( ! feof($cnx)) {
		if ( $i === 1 ) {
			echo("\t\t\t<th>$values[$i]</th>\n"); // this is the first row - use <th> cells
		}
		else {
			echo("\t\t\t<td>$values[$i]</td>\n"); // this is NOT the first row - use <td> cells -- This is line 38
    		}
	$i++;  # increment the counter
	}
echo"\t\t</tr>\n";
};
echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

The error message I get is:

 

Notice: Undefined offset: 3 in C:\xampp\htdocs\tools\PHP_Excel_Reader\v1\index2.php on line 38

 

Thoughts?

[jcollier]

Here's another way I was working on this yesterday:

 

<?php 
$cnx = fopen("data/Book1.csv", "r"); //open csv file
echo("<table>\n\t<tbody>\n"); // echo the table
while (!feof ($cnx)) { // while not end of file
$buffer = fgets($cnx); // get contents of file (name) as variable
$values = explode(",", $buffer); //explode "," between the values within the contents
echo "\t\t<tr>\n";
for ( $i = 0; $i < count($values); $i++ ) {  //
	if ( $i === 0 ) {
		echo("\t\t\t<th>$values[$i]</th>\n");
	} else {
		echo("\t\t\t<td>$values[$i]</td>\n");
	}
}
echo"\t\t</tr>\n";
};
echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

Which worked, except that it made the first "column" <th> cells rather than the first "row".

[codebyren]

The $i variable is just a counter for determining which ROW you're on whereas the $j variable you had in your for-loop indicates which CELL you're on.  I didn't mean for you to replace the $j variable with the $i variable.  Also, you shouldn't have placed the while statement snippet I gave you within the in the while statement that was already there.  The logic was just meant to be integrated into it.  Like so:

 

<?php 	
$cnx = fopen("data/Book1.csv", "r"); //open example.csv
echo("<table>\n\t<tbody>\n"); // echo the table

$i = 1;  # counter variable (to note which row we're working with)
while (!feof ($cnx)) { // while not end of file
$buffer = fgets($cnx); // get contents of file (name) as variable	
$values = explode(",", $buffer); //explode "," between the values within the contents
echo "\t\t<tr>\n";
for ( $j = 0; $j < count($values); $j++ ) {  //
	if ($i === 1) {
	// This is the first row - use <th> cells
		echo("\t\t\t<th>$values[$j]</th>\n");
	}
	else {
		// This is NOT the first row - use <td> cells
		echo("\t\t\t<td>$values[$j]</td>\n");
	}		
}
echo"\t\t</tr>\n";
$i++;  # increment the counter
};

echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

Hopefully that makes more sense...

[jcollier]

This works great!

 

Just one last question...

 

When the script loops through the data in the spreadsheet, it completes the loop with an empty cell when it reaches the end.  Here an example of the HTML output...

 

<table>
<tbody>

	<tr>
		<th>Column 1</th>
		<th>Column 2</th>
		<th>Column 3
</th>
	</tr>
	<tr>
		<td>r1c1</td>

		<td>r1c2</td>
		<td>r1c3
</td>
	</tr>
	<tr>
		<td>r2c1</td>
		<td>r2c2</td>
		<td>r2c3

</td>
	</tr>
	<tr>
		<td>r3c1</td>
		<td>r3c2</td>
		<td>r3c3
</td>
	</tr>
	<tr>

		<td></td>   // The blank cell
	</tr>
</tbody>
</table>

 

Is there a way to not include the trailing blank data cell?

[codebyren]

The code is reading the CSV on a line-by-line basis so it's probably something to do with the last line of the CSV file that's causing the empty cell.

 

Can you post the sample CSV?

[xyph]

You should check if $values is empty, and if it is, use continue to skip to the next iteration of the loop.

[jcollier]

The code is reading the CSV on a line-by-line basis so it's probably something to do with the last line of the CSV file that's causing the empty cell.

 

Can you post the sample CSV?

 

You were absolutely correct.  Attached is my sample (data files are in the data folder).

 

Book1.csv with the trailing return

Book2.csv without the trailing return

18270_.zip

[codebyren]

As xyph suggested, you can add a conditional to check whether $values has any data in it before rendering the row in the table.

 

Then, as a side note, you should use the fgetcsv function for reading lines in a CSV file.  This is preferable to explode() as it can handle cases where a cell in an apropriately formatted CSV line contains commas.  For example, this line has 4 values but 4 commas too (which explode would return as 5 values):

 

superman,spiderman,"batman,robin",hulk

 

In the above, "batman,robin" should be interpreted as one value.  So, working from your initial code:

 

<?php
    // Not ideal...
    $buffer = fgets($cnx); // get contents of file (name) as variable	
    $values = explode(",", $buffer); //explode "," between the values within the contents

    // Better
    $values = fgetcsv($cnx); // parse CSV line into an array
?>

[jcollier]

So I'm probably implementing XYPH's recommendation incorrectly.  But the way I have it, I loose the first column:

 

<?php 	
$cnx = fopen("data/Book1.csv", "r"); //open example.csv
echo("<table>\n\t<tbody>\n"); // echo the table

$i = 1;  # counter variable (to note which row we're working with)
while (!feof ($cnx)) { // while not end of file
$values = fgetcsv($cnx); // parse CSV line into an array
echo "\t\t<tr>\n";
for ( $j = 0; $j < count($values); $j++ ) {  //
	if (empty($j)) {
		continue;
	}
	else {
		if ($i === 1) {
		// This is the first row - use <th> cells
			echo("\t\t\t<th>$values[$j]</th>\n");
		}
		else {
			// This is NOT the first row - use <td> cells
			echo("\t\t\t<td>$values[$j]</td>\n");
		}
	}
}
echo"\t\t</tr>\n";
$i++;  # increment the counter
};

echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

You can tell I'm a novice at this, but it fun to keep trying.

 

Thanks for the advice.

 

[xyph]

You want to check if $values is empty. If there's a blank line, it should be empty. It's been mentioned specifically twice.

[jcollier]

You want to check if $values is empty. If there's a blank line, it should be empty. It's been mentioned specifically twice.

 

Sorry for missing that.  I've got it now.  Here's the final and it works great!

 

<?php 	
$cnx = fopen("data/Book1.csv", "r"); //open example.csv
echo("<table>\n\t<tbody>\n"); // echo the table

$i = 1;  # counter variable (to note which row we're working with)
while (!feof ($cnx)) { // while not end of file
$values = fgetcsv($cnx); // parse CSV line into an array
echo "\t\t<tr>\n";
for ( $j = 0; $j < count($values); $j++ ) {  //
	if (empty($values)) {
		continue;
	}
	else {
		if ($i === 1) {
		// This is the first row - use <th> cells
			echo("\t\t\t<th>$values[$j]</th>\n");
		}
		else {
			// This is NOT the first row - use <td> cells
			echo("\t\t\t<td>$values[$j]</td>\n");
		}
	}
}
echo"\t\t</tr>\n";
$i++;  # increment the counter
};

echo("\t</tbody>\n</table>");
fclose($cnx); //close filename variable
?>

 

Thanks for all your help.