CSV upload and download

[macmiller]

There was a thread around about a CSV upload which was tied into the batch pic upload process, but I can't find it now.  Anyway, what I needed was a stand alone batch upload and download process for the picture title, description and keywords -- so I provide these scripts which may be of interest to anyone wanting a batch update process to upload and download data info CSV format (which can be manipulated via Excel).

The upload process is a stand alone script: uploadcsvdata.php which looks at the file dataupload.csv, both placed in the default coppermine directory.  Execution of the script causes the title, description and keywords to be updated on matching records (based on file location and file name).

The download process can be used to get a current snapshot of what data is out there now.  The process is: downloadcsvdata.php and likewise is placed in the default directory.  The file datadownload.csv is created and or overwritten with each execution of the script.  The column layout is the same as for the upload process, so you can run it and then tweak the output and use it for updates with the upload process.

The only lines which should need to be changed in each script is the mysql_connect (with user name and password) and possibly the mysql_select_db (with database name).

upload script

Code Select Expand

<?PHP
   ini_set('display_errors', 1);
   error_reporting(E_ALL);
/***************************************************
  PHP Script to Upload CSV Data to Coppermine SQL DB
  **************************************************
  v1.0 originally written by K Miller
  script name = uploadscvdata.php
  ********************************************
  $Source$
  $Revision: 0001 $
  $Author: k miller $
  $Date: 2006-12-19 12:10:47 +0200
**********************************************/
/**********************************************
  This file reads information from a CSV file you upload to
  your default directory.  The name of the file is 'dataupload.csv'.
  The file contains 5 columns of data:
   a.)  filepath.  This corresponds with the path to the file. 
      example:  mypics/babymarina/zootripsandsuch/
   b.)  file name.  This is the name of the jpeg image file that
      you want to associate a title, comments and keywords with.
   c.)  title.  The title to the pic.
   d.)  description.  The desription.
   e.)  keywords
  The program process through the input CSV file looking for
  matching records to update.  If the field (title, description,
  or keywords) are blank they are ingored -- that is the pictures
  table is not updated the original field is left intact.  If you
  wish to clear a field place 'clearme' in the field and the contents
  will be spaced out.  If the field (title, description, or keywords)
  are the same as is in the file no update is made.  Summary statistics
  are output at the end if everything goes well.
..
***************************************************************/

   echo "starting CSV coppermine upload script ..... ",
        "<br>";
/** text file location and other static variables **/
   $csvfile = 'dataupload.csv';
   $fullyresolvedfilename = realpath($csvfile);

   if (file_exists($csvfile) == FALSE) {
      die ("convert file doesn't exist");
   }
   $newfile = fopen($csvfile, "r");
   
   $conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
   
   $sel = mysql_select_db ("14_coppermine");
   
   $textfilerowsprocessed = 0;
   $sqlfilerowsprocessed = 0;
   $sqlfilefieldsprocessed = 0;
   $filedata = file($csvfile);
   while (($filedataline = fgetcsv($newfile)) !== FALSE) {
      ++$textfilerowsprocessed;
      $numfields = count($filedataline);
      if ($numfields < 3) {
         die ("bad csv data line " . $textfilerowsprocessed);
      }
      $jpegfileprefix = '';
      $jpegfilename = '';
      $pictitle = '';
      $piccaption = '';
      $pickeywords = '';
      $deletepictitle = FALSE;
      $deletepiccaption = FALSE;
      $deletepickeywords = FALSE;
      if ($numfields > 0) {
         $jpegfileprefix = $filedataline[0];
      }
      if ($numfields > 1) {
         $jpegfilename = $filedataline[1];
      }
      if ($numfields > 2) {
         $pictitle = $filedataline[2];
      }
      if ($numfields > 3) {
         $piccaption = $filedataline[3];
      }
      if ($numfields > 4) {
         $pickeywords = $filedataline[4];
      }
      if ($pictitle == "clearme") {
         $deletepictitle = TRUE;
      }
      if ($piccaption == "clearme") {
         $deletepiccaption = TRUE;
      }
      if ($pickeywords == "clearme") {
         $deletepickeywords = TRUE;
      }

      $xpictitle = mysql_real_escape_string($pictitle);
      $xpiccaption = mysql_real_escape_string($piccaption);
      $xpickeywords = mysql_real_escape_string($pickeywords);
      $sql_command = "SELECT * FROM cpg1410_pictures WHERE filepath = '$jpegfileprefix' AND filename = '$jpegfilename' LIMIT 1";
      $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
      $sql_numrows = 0;
      $sql_numrows = mysql_num_rows($sql_result);
      if ($sql_numrows != 1) {
         echo "warning: ",
            $jpegfileprefix,
            $jpegfilename,
            " not found in pictures table",
            "<br>";
         break;
      }
/** the while loop executes only once **/
      $pfilepid = -1;
      $pfilepath = '';
      $pfilename = '';
      $pfiletitle = '';
      $pfilecaption = '';
      $pfilekeywords = '';
      while ($row = mysql_fetch_array($sql_result)) {
         $pfilepid = $row["pid"];
         $pfilepath = $row["filepath"];
         $pfilename = $row["filename"];
         $pfiletitle = $row["title"];
         $pfilecaption = $row["caption"];
         $pfilekeywords = $row["keywords"];
      }
      $xpfiletitle = htmlspecialchars_decode($pfiletitle,ENT_QUOTES);
      $xpfilecaption = htmlspecialchars_decode($pfilecaption,ENT_QUOTES);
      $xpfilekeywords = htmlspecialchars_decode($pfilekeywords,ENT_QUOTES);
      $updatepic = FALSE;
      $updatepictitle = FALSE;
      $updatepiccaption = FALSE;
      $updatepickeywords = FALSE;
      if ($deletepictitle) {
         if (trim($pfiletitle != '')) {
            $updatepic = TRUE;
            $updatepictitle = TRUE;
            $xpictitle = '';
         }
      } else {
         if (($pictitle != $pfiletitle) and (TRIM($pictitle) != '')) {
           $updatepic = TRUE;
           $updatepictitle = TRUE;
         }
      }
      if ($deletepiccaption) {
         if (trim($pfilecaption != '')) {
            $updatepic = TRUE;
            $updatepiccaption = TRUE;
            $xpiccaption = '';
         }
      } else {
         if (($piccaption != $pfilecaption) and (TRIM($piccaption) != '')) {
           $updatepic = TRUE;
           $updatepiccaption = TRUE;
         }
      }   
      if ($deletepickeywords) {
         if (trim($pfilekeywords != '')) {
            $updatepic = TRUE;
            $updatepickeywords = TRUE;
            $xpickeywords = '';
         }
      } else {
         if (($pickeywords != $pfilekeywords) and (TRIM($pickeywords) != '')) {
            $updatepic = TRUE;
            $updatepickeywords = TRUE;
         }
      }
       
      if ($updatepic) {
         ++$sqlfilerowsprocessed;
         $fieldsupdated = 0;
         
         if ($updatepictitle) {
            $sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.title = '$xpictitle' WHERE cpg1410_pictures.pid = $pfilepid";
            $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
            ++$fieldsupdated;
            ++$sqlfilefieldsprocessed;
         }
         if ($updatepiccaption) {
            $sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.caption = '$xpiccaption' WHERE cpg1410_pictures.pid = $pfilepid";
            $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
            ++$fieldsupdated;
            ++$sqlfilefieldsprocessed;
         }
         if ($updatepickeywords) {
            $sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.keywords = '$xpickeywords' WHERE cpg1410_pictures.pid = $pfilepid";
            $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
            ++$fieldsupdated;
            ++$sqlfilefieldsprocessed;
         }
         if ($fieldsupdated = 0) {
            echo "csv file row not processed ",
                 $jpegfileprefix,
                 $jpegfilename;
         }
      }
   }
   echo "script completed processing from filename-->",
        $fullyresolvedfilename,
        "<br>";
   echo $textfilerowsprocessed,
        " text file rows processed ",
        $sqlfilerowsprocessed,
        " sql file rows processed ",
        $sqlfilefieldsprocessed,
        " sql file fields processed",
        "<br>";

?>

download script

Code Select Expand

<?PHP
   ini_set('display_errors', 1);
   error_reporting(E_ALL);
/***************************************************
  PHP Script to Upload CSV Data to Coppermine SQL DB
  **************************************************
  v1.0 originally written by K Miller
  script name = downloadscvdata.php
  ********************************************
  $Source$
  $Revision: 0001 $
  $Author: k miller $
  $Date: 2006-12-19 12:10:47 +0200
**********************************************/
/**********************************************
  This script works in conjuntion with script uploadcsvdata. 
  This sript extracts data columns from the existing picture
  table and formats them in the following columns.
   a.)  filepath.  This corresponds with the path to the file. 
      example:  mypics/babymarina/zootripsandsuch/
   b.)  file name.  This is the name of the jpeg image file.
   c.)  title.  The title to the pic.
   d.)  description.  The desription.
   e.)  keywords.
  summary statistics are output at the end if everything goes well.
***************************************************************/
   echo "starting CSV coppermine download script ..... ",
        "<br>";

/** text file location and other static variables **/
   $csvfile = 'datadownload.csv';
   $newfile = fopen($csvfile, "w");
   if (file_exists($csvfile) == FALSE) {
      die ("download file not created");
   }
   $fullyresolvedfilename = realpath($csvfile);
   
   $conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
   
   $sel = mysql_select_db ("14_coppermine");
   
   $textfilerowsprocessed = 0;

   $sql_command = "SELECT * FROM cpg1410_pictures";
   $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty table");
   }
   $textfilerowscreated = 0;
   while ($row = mysql_fetch_array($sql_result)) {
      $pfilepid = $row["pid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilecaption = $row["caption"];
      $pfilekeywords = $row["keywords"];
      $xpfiletitle = htmlspecialchars_decode($pfiletitle,ENT_QUOTES);
      $xpfilecaption = htmlspecialchars_decode($pfilecaption,ENT_QUOTES);
      $xpfilekeywords = htmlspecialchars_decode($pfilekeywords,ENT_QUOTES);

      $csvdataline = array ($pfilepath, $pfilename, $xpfiletitle, $xpfilecaption, $xpfilekeywords);
      fputcsv($newfile, $csvdataline);
      ++$textfilerowscreated;
   }
   echo "script completed processing extract to filename-->",
        $fullyresolvedfilename,
        "<br>";
   echo $textfilerowscreated,
        " text file rows processed ",
        "<br>";

?>

[macmiller]

I found the related post here:

http://forum.coppermine-gallery.net/index.php?topic=15184.0

This had a method of uploading CSV data tied in to the batch upload process.

[JohannM]

Hi Mac

I get the following error when trying the php and cvs i created:

"starting CSV coppermine upload script .....

Warning: Wrong parameter count for fgetcsv() in /home/httpd/vhosts/youth-sport-fotos.com/httpdocs/uploadcsvdata.php on line 56
bad csv data line 1"

What can be wrong ?

[macmiller]

I believe your .csv file is incorrectly formatted.  Make sure that this file is created using Excel and saved as a CSV file. 

[JohannM]

I tried different way's with excel.  No luck.

Can you perhaps copy and paste me a sample of how the file must look like ?

I like this script, it's what I need.  I don't want to re-enter 8000 keywords all over again.
Thanx for your help.

[macmiller]

Here it is!

Code Select Expand

userpics/10084/ DSC00082.JPG 3/5/2004 10:58 The windows are the standard grade Thai wood windows.
userpics/10084/ DSC00081.JPG 3/5/2004 10:58 Side view of the house.
userpics/10084/ DSC00080.JPG 3/5/2004 10:57 Front view of the house a few months later.
userpics/10084/ 040101-Malees_foundation.JPG 1/1/2004 15:02 Soil has been layed and foundation pillars have been poured.  To get to this stage only takes a couple of days.
userpics/10084/ DSC00121.JPG 3/28/2004 16:47 Front view of house.
userpics/10084/ DSC00119.JPG 3/28/2004 16:46 Side view of house.
userpics/10084/ DSC00090.JPG 3/5/2004 11:02 Kitchen view.
userpics/10084/ DSC00089.JPG 3/5/2004 11:01 Kitchen counter done with cement and tiles.
userpics/10084/ DSC00088.JPG 3/5/2004 11:01 Tile work.
userpics/10084/ DSC00087.JPG 3/5/2004 11:01 Bathroom.
userpics/10084/ DSC00086.JPG 3/5/2004 11:00 Side of house.
userpics/10084/ DSC00085.JPG 3/5/2004 11:00 Tile work.
userpics/10084/ DSC00084.JPG 3/5/2004 10:59 Septics, one for solid and one for water run-off.
userpics/10084/ DSC00083.JPG 3/5/2004 10:58 Roof exhast fan.  These are prone to leaking.
userpics/10084/ DSC00141.JPG 3/28/2004 17:04 Entry room.
userpics/10084/ DSC00140.JPG 3/28/2004 17:04 Kitchen.
userpics/10084/ DSC00129.JPG 3/28/2004 16:49 Hand dug well, dug to about 7 meters.
userpics/10084/ DSC00128.JPG 3/28/2004 16:48 Inside view.

[Hein Traag]

[offtopic]

Here it is!

Code Select Expand


userpics/10084/ DSC00083.JPG 3/5/2004 10:58 Roof exhast fan.  These are prone to leaking.


macmiller, isn't that exhaust instead of exhast 

[/offtopic]

[JohannM]

OOoo, Ic ...

my problem is with  galerie/J099/

I'll try it again

Thanx

[JohannM]

Same problem : error code

my csv file looks like:


albums/J099/   J099-0001.jpg   J099-0001      Alpha

[JohannM]

Maybe I should explain what I have

I have 8000 pictures of which 2666 has keywords in

so my lines in my csv file will be

filepath:filename:title:caption:keywords

But in my case the caption is empty since I don't use it. So if I understand it right, the format should be:


albums/J099/  J099-0001.jpg J009-0001 clearme Alpha

is this correct ?

In my fields I already have the titles, all I need to add is keywords, without changing anything.

Suggestions ?

[JohannM]

When I try do download, i get this error:

starting CSV coppermine download script .....

Warning: fopen(datadownload.csv): failed to open stream: Permission denied in /home/httpd/vhosts/youth-sport-fotos.com/httpdocs/downloadcsvdata.php on line 32

Fatal error: Call to undefined function: htmlspecialchars_decode() in /home/httpd/vhosts/youth-sport-fotos.com/httpdocs/downloadcsvdata.php on line 59

[Nibbler]

Check your permissions.

htmlspecialchars_decode is a PHP 5.1 function, this should be noted in the mod announcement. See http://php.net/htmlspecialchars_decode for a workaround function.

[JohannM]

Permissions fixed, but still wount create file with download. Still getting errors with uploading.

[macmiller]

Can you post the most recent error message you are getting?

[JohannM]

Hi

I'm running Php 4.3.11 and mySQL 3.23.58-4 and Apache/2.0.50 (Fedora). Can this be the problem ?

[macmiller]

You would need to use the work around that nibbler linked to above for the htmlspecialchars_decode.  As far as the permissions error, select the folder where your datadownload.csv is and secure everything all read write execute.