csv upload working but get error.

rikmoncur

This is odd - I upload a csv file and it loads into the database ok and displays on the page ok but it still gives this error when uploading:
"Column count doesn't match value count at row 1"

What I'm working on:
I'm uploading a flower catalogue as a csv file - it uploads fine (without the error)
then I can download the catalogue as a csv file but it doesn't escape the ' characters (ie it'll have something like "victoria's" with the apostrophe unescaped).

If I remove the apostrophes from my downloaded csv file and upload it again that's when the false error occurs.

Using fgetcsv and fputcsv - and still trying to figure out how to escape apostrophes.

rikmoncur

UPDATE:

This is odd - I upload a csv file and it loads into the database ok and displays on the page ok but it still gives this error when uploading:
"Column count doesn't match value count at row 1"

What I'm working on:
I'm uploading a flower catalogue as a csv file - it uploads fine (without the error)
then I can download the catalogue as a csv file .

When I upload it again as a csv file, that's when the false error occurs.

(Figured how to escape characters with htmlspecialchars on upload and htmlspecialchars_decode on download.)

NogDog

It's going to be pretty difficult to debug without any idea what the code looks like. However, the use of htmlspecialchars() seems wrong to me since there is apparently no HTML in the equation. More likely you need to be using mysql_real_escape_string() on the data being used in your database query (or the applicable function for whichever DBMS interface you are using), and then there should be no need to "unescape" anything when retrieving it back from the DB.

PS: My guess is that the error is due to the CSV file end with a newline and therefore the upload is processing a blank line at the end, which is causing a SQL error due to there being no data for that "line". You may therefore want to add some logic to check if the fgetcsv() result has too few fields before using it in a query. (IIRC, it will return a one-element array for an empty line.)

rikmoncur

NogDog;10896822 wrote:
You may therefore want to add some logic to check if the fgetcsv() result has too few fields before using it in a query.

Thanx for the reply NogDog.
I'm not too sure how to check as you've suggested. Any pointers?

I've noticed that the original file is 102k when I upload it at first but only 101k when downloaded again. That 1k must be doing something...

UPDATE:
Yes, there is an extra line getting added on to the end of the csv file when it's downloaded. Any ideas on how to get rid of that?
I could post some code - there's not too much...

NogDog

Assuming you're doing a loop on fgetcsv() for each row of data to be inserted:

while(($line = fgetcsv($fh)) !== false)
{
   if(count($line) == 0 or (count($line == 1) && trim($line[0] === '')
   {
      continue; // nothing on this line
   }
   // rest of your processing for $line here
}

rikmoncur

I think Ponder Stibbons will have to bung some Lancre chedder at Hex - we're getting an 'out of cheese' error...

this line hasn't got enough brackets: 😃

if(count($line) == 0 or (count($line == 1) && trim($line[0] === '')

I shall poke around at it and see what I can do.

UPDATE:
I tried this -
if((count($data) == 0) or (count($data == 1) && trim($data[0] === '')))

On the upload, unfortunately, it prevents any data getting entered into the database.
On the download, it doesn't do anything.

For the moment I've taken off the mysql_error() from my script but it would be nice to find a solution to this.

NogDog

That's what happens when I type code without my auto-parse-checking IDE.

while(($line = fgetcsv($fh)) !== false)
{
   if(count($line) == 0 or (count($line == 1) && trim($line[0]) === ''))
   {
      continue; // nothing on this line
   }
   // rest of your processing for $line here
}

That should make the ants happier.