Hi all,

A user has notcied that a field CUSTOMER_REF (A22) is loosing mulitple spaces when outputted in Excel or Html.

Example:
Data = 'abc_ _ _ _ def' (4 spaces)
Output = 'abc def'

I have found this old thread, but it appears to relate mainly to html. However I did try Tony's suggestion of inserting

<PRE>

as follows:

COMPUTE C_CRF/A50 = '<PRE>' | CUSTOMER_REF | '<PRE>';

but when outputting to Excel, my one row turns into two merged rows - not ideal at all.

Many thanks!

Mark

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

The new version 7.6 is supposed to have a new SET command, that preserves the spaces in HTML and Excel. But I haven't tried it yet.
SET SHOWBLANKS=ON

Mark,

Are you sure that the data field contains spaces and not carriage returns and/or linefeeds? That would explain the apparent split into two lines (unless you are using WIDTH and WRAP in your stylesheet ).

You can use STRREP to replace any possible combination of chars in a string, even of uneven length.

Unfortunately I do not know if a sample file contains a field with CRLF chars, but you can show the result easily using the CAR file -

DEFINE FILE CAR
  MODEL_CRLF/A50 = STRREP(50, MODEL, 1, ' ', 2, HEXBYT(13, 'A1')|HEXBYT(10, 'A1'), 50, 'A50');
  MODEL_NOCRLF/A50 = STRREP(50, MODEL_CRLF, 2, HEXBYT(13, 'A1')|HEXBYT(10, 'A1'), 1, ' ', 50, 'A50');
END
TABLE FILE CAR
PRINT MODEL_CRLF
      MODEL_NOCRLF
BY CAR
ON TABLE PCHOLD FORMAT EXL2K
END

A bit ugly but it shows you what I mean, and you can see that it's just a case of reversing the STRREP components to get what you want.

T

Hi guys,

Sorry for the delay in picking this back up, anyway have had a chance to revisit today.

quote:

Originally posted by N.Selph:
The new version 7.6 is supposed to have a new SET command, that preserves the spaces in HTML and Excel. But I haven't tried it yet.
SET SHOWBLANKS=ON

Thanks, guess I will just have to push for that upgrade!!

quote:

Originally posted by Tony A:
Mark,

Are you sure that the data field contains spaces and not carriage returns and/or linefeeds? That would explain the apparent split into two lines (unless you are using WIDTH and WRAP in your stylesheet ).

You can use STRREP to replace any possible combination of chars in a string, even of uneven length.

Unfortunately I do not know if a sample file contains a field with CRLF chars, but you can show the result easily using the CAR file -

DEFINE FILE CAR
  MODEL_CRLF/A50 = STRREP(50, MODEL, 1, ' ', 2, HEXBYT(13, 'A1')|HEXBYT(10, 'A1'), 50, 'A50');
  MODEL_NOCRLF/A50 = STRREP(50, MODEL_CRLF, 2, HEXBYT(13, 'A1')|HEXBYT(10, 'A1'), 1, ' ', 50, 'A50');
END
TABLE FILE CAR
PRINT MODEL_CRLF
      MODEL_NOCRLF
BY CAR
ON TABLE PCHOLD FORMAT EXL2K
END

A bit ugly but it shows you what I mean, and you can see that it's just a case of reversing the STRREP components to get what you want.

T

Hi Tony,

There are definitely no other characters in the field. However I do not know what I had done before, because on re-testing the method in my original post it does work ok! The only slight issue is that the field is in a fixed-pitch font - something I have found out happens when using PRE

My only problem now is the inclusion of the PRE tags means that my field length has to be increased by 11 in the COMPUTE statement. So I have now got rid of missing blanks in the middle of my string, but have gained extra spaces at the end that shouldn't be there!!