Riddle me this Focalpointers....

We have found here that an Excel report corrupts the phone numbers in the report.

This seems to happen in Excel 2003, and seems to be part fixed from SP1 to SP2.

If the compound Excel is opened in Excel 2003 SP1, both sheets are corrupt, but in SP2 and SP3 only the second sheet is corrupt.

SET COMPOUND=OPEN
TABLE FILE CAR
HEADING
"+6543210123"
PRINT COUNTRY
     COMPUTE ERROR/A20 = '+4567-890123';
COMPUTE ERR2/A20  = '+6543210123' ;
COMPUTE ERR3/A20  = '+67890123456' ;
ON TABLE PCHOLD FORMAT EXL2K
END
SET COMPOUND=CLOSE
TABLE FILE CAR
HEADING
"+6543210123"
PRINT
     'CAR.ORIGIN.COUNTRY'
     COMPUTE ERROR/A20 = '+4567-890123';
COMPUTE ERR2/A20  = '+6543210123' ;
COMPUTE ERR3/A20  = '+67890123456' ;
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

If anyone has any ideas on how to display the correct numbers, it would be appreciated.

My hair is getting thinner each hour.

Our only options are upgrade to 2007 or remove the + and - chars from the numbers.

This message has been edited. Last edited by: Kerry,

So far what I have figured out is you just have to remove the - character. For some reason it does not like a + and - together in a number.

What I discovered is that if you put a non numeric character after the + sign then everything works fine. Therefore you could do a replacement of all + signs with '+ ' (plus and space):

  
TABLE FILE CAR
HEADING
"+6543210123"
PRINT COUNTRY
     COMPUTE ERROR/A20 = STRREP (12, '+4567-890123', 1, '+', 2, '+ ', 20, ERROR);
COMPUTE ERR2/A20  = '+6543210123' ;
COMPUTE ERR3/A20  = '+67890123456' ;
ON TABLE PCHOLD FORMAT EXL2K
END
SET COMPOUND=CLOSE
TABLE FILE CAR
HEADING
"+6543210123"
PRINT
     'CAR.ORIGIN.COUNTRY'
     COMPUTE ERROR/A20 = STRREP (12, '+4567-890123', 1, '+', 2, '+ ', 20, ERROR);
COMPUTE ERR2/A20  = '+6543210123' ;
COMPUTE ERR3/A20  = '+67890123456' ;
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

I am running XL2K3 SP2 and in your example tab1 works fine and tab2 doesn't. In the above example if you do not do a strrep on the 1st request then it just gives garbage as well.

Hope this helps.

Try building your computes with a concatenation:

COMPUTE ERROR/A20 = '+' | '4567-890123';

This seem to work for me.

G

I tried the concatenation and the only way it worked was to add a non numeric character after the + sign. I figured the data Waz will be using will come from a field from the database. Hence the use of STRREP.

coding for '+' becomes '+ ' and '-' becomes ' - ' would appear tobe the best option.

Interestingly in 7.7 using format EXL07 it works fine

Except

It does not tab the reports, it works as if NOBREAK was specified on the COMPOUND OPEN.

New feature bug I suspect.

If you replace any single instance of '+' with '+ ' in the second report, all of the numbers display correctly. For example, if you make this change to the report heading, all of the numbers display correctly.

SET COMPOUND=OPEN
TABLE FILE CAR
 HEADING
  "+6543210123"
 PRINT COUNTRY
 COMPUTE ERROR/A20 = '+4567-890123';
 COMPUTE ERR2/A20  = '+6543210123';
 COMPUTE ERR3/A20  = '+67890123456';
 ON TABLE PCHOLD FORMAT EXL2K
END
-*
SET COMPOUND=CLOSE
TABLE FILE CAR
 HEADING
  "+ 6543210123"
 PRINT COUNTRY
 COMPUTE ERROR/A20 = '+4567-890123';
 COMPUTE ERR2/A20  = '+6543210123';
 COMPUTE ERR3/A20  = '+67890123456';
 ON TABLE PCHOLD FORMAT EXL2K
END

Thanks for the responses, we had worked out that either removing the '-' or adding a space after the + would work, but that would mean either manipulating the data in the program or in the database its coming from.

This is something that I don't like doing. Changing customer data with out there consent is in many ways a no no.

It is unfortunately what we are going to do, as there are only a few of these.

But it still raises the question, why is it happening.

I did find in the Office 2003 SP2 release notes this entry, that may be the reason why.

quote:

When you try to import data by using the Excel 2003 Data Import Wizard, Excel 2003 may incorrectly identify the code page (language setting) of the document that you are trying to import.

Either way its bizarre.

Well at least the issue is now out there.