[ic] XLS import failure - 255 character limit issue.

interchange-users@icdevgroup.org interchange-users@icdevgroup.org
Wed Sep 4 11:58:01 2002


Quoting Reid Sutherland (reid@vianet.ca):
> Hi,
> 
> I've having an issue where I'm trying to import an Excel 97+ spreadsheet 
>   into IC.  The problem is that IC takes a violent fit when the 
> description/comment field is over 255 characters.  It will start to push 
> MySQL errors.  Now I'm from me looking at the 
> UI/usertags/import_fields.tag, I don't see anything that would cause any 
> fields to be truncated/filled with upper ASCII chars by IC.  But then 
> again the code is very well written, making it painful for me to read :)

I have seen problems with XLS created by Excel 97 with long fields. They
are fine if generated by Gnumeric...

> 
> Now I've tested my install of Spreadsheet::ParseExcel and it reads the 
> long descriptions fine.  I'm not sure what else I can do to aid in this 
> matter.

What code are you using to verify that? We would need to compare
how it is done with the way IC does it.

       for( ; $iR <= $oWkS->{MaxRow}; $iR++) {
	  my $row = $oWkS->{Cells}[$iR];
	  @out = ();
	  for($iC = $mincol; $iC <= $maxcol; $iC++) {
	    if(! defined $row->[$iC]) {
		push @out, "";
		next;
	    }
	    push @out, $row->[$iC]->Value;
	  }
	  $sheets->{$sname} .= join "\t", @out;
	  $sheets->{$sname} .= "\n";
       }

That is the loop that actually does the read. If there are embedded
newlines or TABs it might cause a problem...we could add some
translation stuff, I suppose. You might change this in import_fields:

    push @out, $row->[$iC]->Value;

to;

    my $v = $row->[$iC]->Value;
    $v =~ tr/\n\t/\r /;
    push @out, $v;

That will kill any tabs, of course, but will prevent import problems.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Being against torture ought to be sort of a bipartisan thing.
-- Karl Lehenbauer