SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Remove quote marks from CSV fields for a table

    I'm fairly new to PHP and learning a few basic things. One thing I'm working on is displaying data from a CSV file in a HTML table using PHP. I have it working to an extent, but I cant get rid of the quote mark from the last field in each row.
    To start with, it filled in the table, but each field still had it's quote marks on either side, so I added trims to remove them. That worked except for the last field of each row, which still has a quote mark on the end.
    Where am I going wrong?
    You may notice I have a trim for each of the 4 entries in the array. Is there a smarter way to do that for the whole array in one go?

    Code:
    <html>
        <head>
    	<title>Reading Data from a CSV file</title>
        </head>
        <body>
    	<h1>Table of things from the CSV file</h1>
    	
    	<table border=1>
    	    <?php
    	
    	    // Open CSV file
    	    $f = fopen("csvdir/testdata.csv", "r");
    
    	    // Get an array of headers from first line
    	    $arrT = explode("," , fgets($f));
    	
    		// Remove Quotes
    	       $arrT[0] = trim($arrT[0], "\"") ;
    	       $arrT[1] = trim($arrT[1], "\"") ;
    	       $arrT[2] = trim($arrT[2], "\"") ;
    	       $arrT[3] = trim($arrT[3], "\"") ;
    	
    	    // Create table header row
    	    echo "<tr><th>" . $arrT[0] . "</th><th>" . $arrT[1] . "</th><th>" . $arrT[2] . "</th><th>" . $arrT[3] . "</th></tr>" ;
    	
    	    // Read line by line until end of file
    	    while (!feof($f)) { 
    
    	    // Make an array using comma as delimiter
    	       $arrM = explode("," , fgets($f));
    	   
    	    // Remove Quotes
    	       $arrM[0] = trim($arrM[0], "\"") ;
    	       $arrM[1] = trim($arrM[1], "\"") ;
    	       $arrM[2] = trim($arrM[2], "\"") ;
    	       $arrM[3] = trim($arrM[3], "\"") ;
    
    	    // Create table rows with data
    	       echo "<tr><td><a href=\"mailto:" . $arrM[0] . "\">" . $arrM[0] . "</a></td><td>" . $arrM[1] . "</td><td>" . $arrM[2] . "</td><td>" . $arrM[3] . "</td></tr>" ;
    	    }
    
    	    fclose($f);
    	    ?>
    	</table>
        </body>
    </html>

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Save yourself some trouble and use the prebuilt function to handle csvs, it will take care of the enclosures (quotes) for you:

    http://php.net/manual/en/function.fgetcsv.php

    A cool side effect is that if done properly, the entire file does not need to exist in memory to parse it.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    I did not know there was a function for CSV files. I'll look into it.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I have something that works. I started with the example on the page you linked and edited it to create a table instead of a series of line of data.
    I added a bit to the beginning to make the first line of data be Table Headers rather than Data. Because the file is created by a form script that writes the file in that way. I have yet to add the bit that prints the Email address as a mailto link.
    For anyone who finds it useful, or can suggest a better way, here is the code:-
    Code:
    <html>
        <head>
    	<title>Reading Data from CSV files</title>
        </head>
        <body>
    	<h1>Displaying Data From CSV Files in a Table</h1>
    	<table border="1">
    	<?php
    	$row = 1;
    	// Open the file
    	if (($handle = fopen("/home5/burtons1/csvdir/testdata.csv", "r")) !== FALSE) {
    	    
    	    // Read the heading line, Use this section if the first line on the file contains the column headers, otherwise skip it
    	    $heads = fgetcsv($handle, 1000, "," ) ;
    		// Count the number of fields
    		 $num = count($heads);
    		// Start the first Table Row
    		echo "<tr>" ;
    		// Write each field as a Table Header
    	        for ($c=0; $c < $num; $c++) {
    	            echo "<th>" . $heads[$c] . "<th/>\n" ;
    	        }
    		// End the first row
    		echo "</tr>" ;
    		// increment te row number
    		$row++;
    		
    	    // Read the rest of the data line by line, or all the data if you skipped the headers section
    	    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    		// Count the number of fields in the line
    	        $num = count($data);
    		// Start the new Row
    		echo "<tr>" ;
    		// Write each field as Table Data
    	        for ($c=0; $c < $num; $c++) {
    	            echo "<td>" . $data[$c] . "<td/>\n" ;
    	        }
    		echo "</tr>" ;
    		// increment te row number
    		$row++;
    	    }
    	 fclose($handle);
    	}
    	?>
    	</table>
        </body>
    </html>

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Good work. Usually theres no reason to keep an incrementer unless you are going to do acomparison to meta data to ensure you recieved the entire file, or if you are going to alternate row colors. Here might be a cleaner version:

    Code PHP:
    if(!$handle = fopen($filePath, "r")) {
        echo "unable to open $filePath";
    } else {
        echo "<table>\n"
        while ($row = fgetcsv($handle, 0, ",")) {
            echo "\t<tr>";
            if(!isset($headers)) {
                $headers = $row; //so you can use it later if you want
                $tag = "th";
            } else {
                $tag = "td";
            }
            foreach($row as $column) echo "<$tag>$column</$tag>";
            echo "</tr>\n";
        }
        echo "</table>\n";
    }

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works great, thanks.
    I just had to add the missing semi colon after the opening table tag. That was after I added the missing semi colon after an extra first line that I added ( $filePath = ). At least it's not just me that keeps forgetting to put semi colons on the end, then wondering why it doesn't work.
    Before I saw this, I did re-write mine. Because I know how many columns there are and what data they contain, I put in the headers manually with more user friendly names, rather than the variable names they are assigned by the form. Then add the fields as table data one at a time. That allows me to put the Email as a mailto link.
    Code:
    <table border="1">
    	    <!--Adding the table headings manually-->
    	    <tr><th>Name</th><th>Email</th><th>A Field</th><th>Another Field</th></tr>
    	<?php
    	// Open the file
    	if (($handle = fopen("/csvdir/testdata.csv", "r")) !== FALSE) {
    	    
    	    // Read the heading line, this doesn't do anything, just gets us past the headings
    	    $heads = fgetcsv($handle, 1000, "," ) ;
    		
    	    // Read the rest of the data line by line
    	    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    		echo "<tr>" ;
    		echo "<td>" . $data[1] . "</td>" ;
    		echo "<td><a href=\"mailto:" . $data[0] . "\" title=\"Send an Email to " . $data[1] . "\">" . $data[0] . "</a></td>" ;
    		echo "<td>" . $data[2] . "</td>" ;
    		echo "<td>" . $data[3] . "</td>" ;
    		echo "</tr>\n" ;
    	    }
    	 fclose($handle);
    	}
    	?>
    	</table>

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SamA74 View Post
    That works great, thanks.
    I just had to add the missing semi colon after the opening table tag. That was after I added the missing semi colon after an extra first line that I added ( $filePath = ). At least it's not just me that keeps forgetting to put semi colons on the end, then wondering why it doesn't work.
    Before I saw this, I did re-write mine. Because I know how many columns there are and what data they contain, I put in the headers manually with more user friendly names, rather than the variable names they are assigned by the form. Then add the fields as table data one at a time. That allows me to put the Email as a mailto link.
    Code:
    <table border="1">
            <!--Adding the table headings manually-->
            <tr><th>Name</th><th>Email</th><th>A Field</th><th>Another Field</th></tr>
        <?php
        // Open the file
        if (($handle = fopen("/csvdir/testdata.csv", "r")) !== FALSE) {
            
            // Read the heading line, this doesn't do anything, just gets us past the headings
            $heads = fgetcsv($handle, 1000, "," ) ;
            
            // Read the rest of the data line by line
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            echo "<tr>" ;
            echo "<td>" . $data[1] . "</td>" ;
            echo "<td><a href=\"mailto:" . $data[0] . "\" title=\"Send an Email to " . $data[1] . "\">" . $data[0] . "</a></td>" ;
            echo "<td>" . $data[2] . "</td>" ;
            echo "<td>" . $data[3] . "</td>" ;
            echo "</tr>\n" ;
            }
         fclose($handle);
        }
        ?>
        </table>
    I see you took out some of my code. You don't like error checking? What happens if testdata.csv wasn't saved correctly for some reason?

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see you took out some of my code. You don't like error checking? What happens if testdata.csv wasn't saved correctly for some reason?
    No, I wrote that code before I saw your previous post. Error handling is of course a good idea.
    I will take a closer look at your code, get my head around it, and see if I can adapt it to fit my needs.
    What I have above does the job for now, at this stage it's only experimental, but could be improved before I put it to use. I will probably add error handling to it.
    Thanks again.

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have another question about fgetcsv.
    How do a target just one specific line in the CSV file?
    For example I want an array of all the fields in line 5 of my CSV file. In practice the line number will be expressed with a variable like $id or something.

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SamA74 View Post
    I have another question about fgetcsv.
    How do a target just one specific line in the CSV file?
    For example I want an array of all the fields in line 5 of my CSV file. In practice the line number will be expressed with a variable like $id or something.
    The good thing about fgetcsv is that it does not store the entire file in memory, it iterates over each line. With that said, you have to use an incrementer to see which line you are on, and ignore output until you reach line 5.

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have to use an incrementer to see which line you are on, and ignore output until you reach line 5.
    OK, that's how I ended up doing it, with a while until the incrememter reached my target line. I just thought there may be a more direct way to say "Get line X" that I was missing.
    I have my tables on a working CSV file now. The main page show line entry, with just a few short fields,Line Number, Name, Email, Subject and Date. The Email field is a Mailto link, and the Subject field is a link to a page with full data for that line.
    So the main page has this code:
    Code:
    <table>
    					<tr><th>ID</th><th>Name</th><th>Email</th><th>Subject</th><th>Date</th></tr>
    					<?php
    						$filePath = "/path/contacts.csv" ;
    						if(!$handle = fopen($filePath, "r")) {
    							// Error message in case of file load error
    							echo "<tr><td colspan=\"5\" style=\"color:#d00;font-size:2em;\" align=\"center\">Unable to open Database!</td></tr>" ;
    							} else {
    								// Read the heading line
    								$heads = fgetcsv($handle, 100, "," ) ;
    								$row = 1 ; // row provides the ID number which is not contained in the database
    								// Read remaining lines
    								while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    									// Start the new Row
    									echo "<tr>\n" ;
    									echo "<td>" . $row . "</td>\n" ; // ID
    									echo "<td>" . $data[0] . "</td>\n" ; // Name
    									echo "<td><a href=\"mailto:" . $data[1] . "\" title=\"Send an Email to " . $data[0] . "\">" . $data[1] . "</a></td>\n" ; // Email
    									echo "<td><a href=\"message.php?ID=" . $row . "\" title=\"View this message\">" . $data[2] . "</a></td>\n" ; // Subject
    									echo "<td>" . $data[6] . "</td>\n" ; // Date
    									echo "</tr>\n" ;
    									$row++ ;
    								}
    							fclose($handle);
    						}
    					?>
    				</table>
    The link in the Subject field sets the row number as $ID which is passed to the message page. The message page has this code to display all the data from your chosen line.
    Code:
    <?php
    $messid = $_GET["ID"] ;
    					echo "<table>\n" ;
    						$filePath = "/path/contacts.csv" ;
    						if(!$handle = fopen($filePath, "r")) {
    							// Error message in case of file load error
    							echo "<tr><th colspan=\"2\" style=\"color:#d00;font-size:2em;\" align=\"center\">Unable to open Database!</th></tr>" ;
    							} else {
    								$row = 0 ;
    								while ( $row <= $messid-1 ) {
    									$data = fgetcsv($handle, 1024, "," ) ;
    									$row++ ;
    								}
    								$data = fgetcsv($handle, 1024, "," ) ;
    								echo "\t<tr><th>Name</th><td>$data[0]</td>\n" ;
    								echo "\t<tr><th>Email</th><td><a href=\"mailto:" . $data[1] . "\" title=\"Send an Email to " . $data[0] . "\">" . $data[1] . "</a></td>\n" ;
    								echo "\t<tr><th>Telephone</th><td>$data[3]</td>\n" ;
    								echo "\t<tr><th>Subject</th><td>$data[2]</td>\n" ;
    								echo "\t<tr><th>Message</th><td>$data[4]</td>\n" ;
    								$ftime = explode("+", $data[5]) ;
    								$stime = substr ($ftime[0], 0, 5) ;
    								if ( $ftime[1]=0) { $tz = "<acronym title=\"Greenwich Mean Time\">GMT</acronym>" ; } else { $tz = "<acronym title=\"British Summer Time\">BST</acronym>" ; }
    								echo "\t<tr><th>Time/Date</th><td>$stime $tz on $data[6]</td>\n" ;
    								echo "\t<tr><th>IP Address</th><td>$data[7]</td>\n" ;
    							}
    					echo "</table>" ;
    				?>
    It all seems to be working fine, thanks for your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •