csv import woes...

[showdown]

Hello,

I'm a total noob and I'm running up against a headbanging problem that I just can't fix.. .of course. I have a script to allow the upload of a csv and insertion into the database. The csv uploads fine, the csv gets read fine and the first line is inserted a-ok but nothing after that. The other lines aren't inserted. To make matters more frustrating the script is properly counting the lines in the csv.

Help. :queasy:

This is the code that I've been using... It seems to work save a few issues:

1.) on my local testing server (mac 10.5, apache 2.2.9, php 5.2.6 ) only the first row of the csv is inserted into the table...
2.) Because my csv file was saved on a mac I have to set the line separator as a carriage return "\r" and don't know if that will cause problems with pc files (wasn't this supposed to be fixed in php 5?)
3.) I still haven't figured out how to ignore the first row...

<?php

//Begin upload process
$target = "files/";
$target = $target . basename( $_FILES['uploaded']['name']) ;
$ok=1;

//Here we check that $ok was not set to 0 by an error
if ($ok==0)
{
Echo "Sorry your file was not uploaded";
}

//If everything is ok we try to upload it
else
{
if (move_uploaded_file($_FILES['uploaded']['tmp_name'], $target))
{
echo "The file ". basename( $_FILES['uploaded']['name']). " has been uploaded<br/>";
}
else
{
echo "Sorry, there was a problem uploading your file.";
}
}

//Connect to the DB (will use Zikula here)
$databasehost = "localhost";
$databasename = "ica";
$databasetable = "zk_pagesetter_pubdata1";
$databaseusername ="ica";
$databasepassword = "sesame";
$fieldseparator = ",";
$lineseparator = "\r"; //how to check if the file was from a mac or windows wasn't this fixed in php 5??
$csvfile = $target;

//create var $timestamp with current time
$timestamp = date('Y-m-d H:i:s');

//create var $userid and get the user's id: Zikula AIP?
$userid = "2"; //currently set to admin but might be better set to anon?

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

//create var $exitstingPubs and get total number of existing pubs from pagesetter_counters for given tid X: pagesetter_getPubCount plugin
$pubCount = "SELECT `pg_count` FROM `zk_pagesetter_counters` WHERE `pg_name` = 'tid1'";

$existingPubsRaw = mysql_query($pubCount);
$row2 = mysql_fetch_row($existingPubsRaw);
$existingPubs = $row2[0];
$totalPubs = $row2[0];

/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/


//Look for uploaded file
if(!file_exists($csvfile)) {
   echo "File not found. Make sure you specified the correct path.\n";
   exit;
}

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

if(!$file) {
   echo "Error opening data file.\n";
   exit;
}

$size = filesize($csvfile);

if(!$size) {
   echo "File is empty.\n";
   exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

   $lines++;

   $line = trim($line," \t");

   $line = str_replace("\r","",$line);

   /************************************
   This line escapes the special character. remove it if entries are already escaped in the csv file
   ************************************/
   //$line = str_replace("'","\'",$line);
   /*************************************/

   $linearray = explode($fieldseparator,$line);

   $linemysql = implode("','",$linearray);

   if($addauto)
      $query = "insert into $databasetable values('','$totalPubs','$totalPubs','approved','1','0','1','-1','1','1','admin','2','$timestamp','$timestamp','$timestamp','NULL','x_all','$linemysql');";
   else
      $query = "INSERT INTO $databasetable values('$totalPubs','$totalPubs','approved','1','0','1','-1','1','1','admin','2','$timestamp','$timestamp','$timestamp','NULL','x_all','$linemysql');";

   $queries .= $query . "\n";

   @mysql_query($query);

   echo $linemysql."<br>";


//($insertedPubs = $insertedPubs ++) 
$insertedPubs = $insertedPubs + 1;

//($existingPubs = $existingPubs ++) 
$existingPubs = $existingPubs + 1;

//for each item inserted into the db insert a new line into pagesetter_pubheader with the data: "pg_tid (X), pg_pid ($existingPubs), pg_hitcount (0), pg_onlineid ($existingPubs), pg_deleted (0)"
$pubheader = "INSERT INTO zk_pagesetter_pubheader values('1','$existingPubs','0','$existingPubs','0')";
mysql_query($pubheader)  or die(mysql_error());

//for each line inserted into the db insert a new line into pagesetter_revisions with the data: "pg_tid (X), pg_id (1), pg_pid ($existingPubs), pg_prevversion (0), pg_user ($userid), pg_timestamp ($timestamp)"
$revisions = "INSERT INTO zk_pagesetter_revisions values('1','$existingPubs','$existingPubs','0','2','$timestamp')";
mysql_query($revisions)  or die(mysql_error());

}


if($save) {

   if(!is_writable($outputfile)) {
      echo "File is not writable, check permissions.\n";
   }

   else {
      $file2 = fopen($outputfile,"w");

  if(!$file2) {
     echo "Error writing to the output file.\n";
  }
  else {
     fwrite($file2,$queries);
     fclose($file2);
  }
   }

}

echo "Found a total of $lines records in this csv file.<br/>";

//actions preformed after all the results have been stored in the database
//insert $insertedPubs into pagesetter_counter for the given tid X
$counters = "UPDATE zk_pagesetter_counters SET pg_count = $existingPubs WHERE pg_name = 'tid1'";
mysql_query($counters)  or die(mysql_error());
echo "<br/>total number of pubs now: $existingPubs";


//Close DB connection
@mysql_close($con);

?> 

[NogDog]

I did not read through all that code, but one thing I'd suggest is using the [man]fgetcsv/man function to parse the file instead of reading it all in and the splitting it into lines and then each line into fields. I believe that it will handle the "\r" line endings OK, though I've not tried it.

[djjjozsi]

And in that case that the fgetcsv() did not use the line ends well,
as i saw the phpmyadmin csv import functions, that replaces the double escaped \r \t \n words into 'normal' \r \t \n version.

However csv is a good idea, XML might be a better method for database import.

[NogDog]

djjjozsi;10903943 wrote:

However csv is a good idea, XML might be a better method for database import.

Good point: you would then not need to worry about things like header rows, blank lines, etc. in the CSV file.

[jmbrink26]

Definitely agreed.

I did run accross one time of having to USE CSV, in which I did use fgetcsv(), but it's a real pain. XML is alot smoother to work with, definitely agreed.

Ah, the joys of working with data files... 😛

[mikewhy]

I ran into problems similar to this before. My problem occurred after I edited the original .csv in ms excel, after editing, only the first row would be imported. I was using phpmyadmin. You can try playing with the settings, for Fields Terminated by I used \t and Fields Escaped by \n.

Good Luck

[shawty]

XML is for sure the best way to go, however sometimes this is not an option and to be honest as long as there's nothing wierd in your CSV then it's far far easier to split up.

Looking at your code however, i dont think that its the fact your using CSV thats the problem.

Look closley at your main foreach loop.

foreach(split($lineseparator,$csvcontent) as $line) {

Your setting the amount of times you loop to what ever is returned from the split command which is actually an array of string parts rather than an array containing all the lines in the file.

If split only returns a line with 5 elements in, and you use all 5 elements up in the first iteration of the loop, then your loop is only going to execute once.

also when you read your CSV file in, your reading the entire thing in as one long string.

if you where to use

$filecontents = file($csvfile);

instead, then you'd get an array with one element for every line in the file.

You could then simply loop over each line in the file, splitting them as you go, as follows:

$filecontents = file($csvfile);
foreach($filecontents as $csvline)
{
  $temp = split($lineseparator,$csvcontent);

  //$temp[0] will hold CSV column 1
  //$temp[1] will hold CSV column 2
  // and so on......

}

// The loop will exit here, when there are no more lines left in the $filecontents array.

As a bonus, you also don't need to open a file handle, and keep it open while you process the file.

If you must load the entire file as a single string and not an array then "file_get_contents" will do the job in the same manner as "file".

The other advantage this has also, is that you don't need to worry about the line endings, beacuse "file" will take care of that for you, although you may still need to use the "trim" function on each line as you process it.

Cheers

Shawty