Working with a file attached to a page

[joer80]

Ok, I am sure this is an easy question!

I have a page with a csv file attached, and I want to know how I should work with that file!

If I do:   $matches = $this->pages->find("parent=1082");   (This Finds one page so we are so far so good)

(I made a hidden "csv page" in root, and am putting all of the csv files as children with a file field for the file, and a text field for the database table name.)

 

When I loop through and access the page that it finds, I am able to get the filename of the csv file with this:  $m->csvFile (gives me filename.csv)

However, I am not sure the way I should read the file to work with it!  

Or should I not, and just use this filename I have, add on the rest of the path, and pass it to fopen?  Like this:  

$file = fopen($csvfilepath,"r");

(I am wanting to read the csv file and insert it into a database table.)

Thanks!

[adrian]

From your description, I think you want to make use of fopen and also fgetcsv or str_getcsv:

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

http://www.php.net/manual/en/function.str-getcsv.php

Both those links have examples on how to use them.

You might also learn a lot from Ryan's CSV importer:

https://github.com/ryancramerdesign/ImportPagesCSV/blob/master/ImportPagesCSV.module#L299

[joer80]

Ah...  awesome!

It does look like he just passed a filename to fopen on a file uploaded with a form!   I could not find much documentation on working with the files stored on a page and just wanted to make sure I was doing it right!  

What is the $files and $file->url variable used for I see on the cheat sheet?  (I see it has things like $files->path available.)

Thanks!

[adrian]

The $files variable is for accessing just about everything else about a file, but not its content. Of course the contents of files vary so much from type to type. There might not be much value in extracting the content of a binary file, but it might be a nice addition to have an easy way to extract the content of text based files.

$file->url would be used, for example, for making a link to view a PDF or display an image. $file->filename is what you would want to pass to fopen. $files->path is the full disk path to the files directory for the page, eg /home/public_html/site/assets/files/1001/

Here's something else that might be useful for you to read - the difference between fopen and fread:

http://www.tuxradar.com/practicalphp/8/1/3

[joer80]

That is really great reading, and really helpful!  I have found examples online using both methods!

Thank you!

[joer80]

This is my first module, so be easy on me! I am sure there was a faster and better way to do it, but this is what I have so far and it works pretty well!  I figure by showing it, one of you may see a way to make it better!

To use it, just make a hidden "csv" page in your root and add at least one csv item as a child.  

The children will need to have a "csv" template that has a csvFile field, and a csvDatabaseTable field.  (I am thinking about adding a third one for csvDelimeter.)

Also, I added a new admin page called "Read CSV files", and selected this module as the process.

This reads all the children of that csv page, right now I just look up the csv page by id, so be sure and change that to hook it up to the correct page, and puts the content of the csv files into the database table listed on the child.  (emptys the table first.)

As of right now, the database table needs to be created in advance with the correct number of columns listed in the csv file.

How much more work do you think I need to add in before it is worth listing as a public module?  I just have never done one and was not sure!

Thanks!

Here is the page output:

Importing All CSV files under csv page (id=1082)

 

Searching for available files:

 

Found: 1

 

Importing (csvFile.csv) into (csvDatabaseTable)

 

imp row 2, imp row 3, imp row 4, imp row 5, imp row 6

 

Import Complete!

 

 

Here is the code:

<?

class ReadCSV extends Process {

    public static function getModuleInfo() {
        return array(
            'title' => 'Read CSV',
            'summary' => 'This Module Reads the csv files under the csv page and inserts them into a database table. (Finds children of page id=1082)',
            'version' => 001,
            );
    }

    public function execute() {
		
		$ReturnHTML = '<p><b>Importing All CSV files under csv page (id=1082)</b></p>';
		$ReturnHTML .= '<p>Searching for available files:</p>';
		
		
		 //find all csv files that are below the csv page.  (Have it's id as their parent)
		$matches = $this->pages->find("parent=1082"); //Must spesify $this in a module.  The following method also works: $matches = wire("pages")->find("parent=1082");
		$count = count($matches); 
		  
		 if($count) {
			 
			  $ReturnHTML .= "<p>Found: $count</p>";
	  
			  foreach($matches as $m) {
				  $csvDelimeter = ',';
				  $csvEnclosure = '"';
				  $csvPath = '../..' . $this->config->urls->files . $m->id . '/'; //Format:  ../site/assets/files/1083/   can also use $config->paths->files.  (I added the ../.. to get out of the modules and sites folder.)
				  $csvFullPath = $csvPath . $m->csvFile;
				  $ReturnHTML .= "<p>Importing $m->csvFile into $m->csvDatabaseTable</p>"; 
				  
				  //clear out the current database
				  $sql = "TRUNCATE TABLE `$m->csvDatabaseTable`";
				  $database = $this->wire('database');	
				  $database->exec($sql);
				  
				  //open csv	  
				  ini_set("auto_detect_line_endings", true); //this is required.  I think they are creating the csv on a mac or linux is the reason.
				  $row = 0;
				  $autoid = 1;
				  if (($handle = fopen($csvFullPath, "r")) !== FALSE) {
					  while (($data = fgetcsv($handle, 0, $csvDelimeter, $csvEnclosure)) !== FALSE) {
						  $num = count($data);
						  $row++;
						  //$ReturnHTML .= "<p> $num fields in line $row: <br /></p>\n";
						  $allData = ''; //this holds the value string.  (cell data.)
						  for ($c=0; $c < $num; $c++) {
							  $allData .= '"' . $data[$c] . '",';
						  }
						  
						  //insert the data
						  if($row == 1){ //skip row 1.  Instead of reading the column names, I am just inserting the data so order is important.
							//echo "<p>Skipping the first row.</p>";  
						  } else {
							$allData = trim($allData, ','); //trim the trailing , off.
							$allData = $autoid . ',' . $allData; //add an extra column for the auto id column
							
							//insert into db
							$sql = "INSERT INTO $m->csvDatabaseTable VALUES ($allData)";
							$database = $this->wire('database');	
							$database->exec($sql);
							$ReturnHTML .= 'imp row ' . $row . ', ';
							$autoid++;

						  }
						  
						  
					  } //end loop through spreadsheet rows
					  
					  fclose($handle);
					  
					  $ReturnHTML = rtrim($ReturnHTML, ', ');
					  $ReturnHTML .= '<p>Import Complete!</p>';
					  
				  } //close open csv

					  			
			  } // end for loop through pages
	  
	  
		  } else {
			  $ReturnHTML .= "<b>Sorry, no results were found.</b>";
		  }
		
        return $ReturnHTML;
    } // end of execute function

} //end of class

?>

[joer80]

This would have also worked:
 

$csvPath = '../..' . $m->csvFile->url;

Instead of 

$csvPath = '../..' . $this->config->urls->files . $m->id . '/' . $m->csvFile;

Its just important that you add the '../..' since the fopen will not find the file without it.