[SOLVED] Need Spaces to be in excel concatenated file

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

New

Notify

Tools

Personal Zone	»
Member Directory
Focal Point Forums	»

Printer Friendly Format

Help

[SOLVED] Need Spaces to be in excel concatenated file

Paul M.

Silver Member

posted

Hi,

I have a dilemma that I need some assistance with. I have a fex file that I need to be able to show the spaces in the output. The scenario is a bit complicated. Here are the specifics.

WebFOCUS 8.2.0.2M is the version we are on.

The fex file below creates an excel spreadsheet with values from the CAR file, but to the right of each row is a concatenated line of all the fields in that row. The user will make changes to the fields on the left and they will automatically populate into the concatenated line to its right.

The problem I am having is that when there is a field that is blank or spaces, I need those spaces to be in the concatenated file as spaces and not squeezed together when it is blank (I made the SEATS field spaces when it was equal to 2 for this demonstration as this is not my real data). I want the number of characters (say 3) to show so we will see spaces where the field would be (column C is the one I am attempting to get spaces into when there is no value). Our users use this concatenated field to upload into their system. I have already tested this and it all worked (we have yet to put it into prod), but when it was tested again the spaces that I put into it ( ) no longer work; the system the file is uploaded into says the characters are invalid.

As a test one of our BA's copied the line from the excel spreadsheet to Word and with the checkbox on to see spaces between words, a real space shows up like a period but these are showing up as little o's, so something isn't correct.

I have tried using hexbyt with a number of different values, 10, 13, 32, 160, and 255. I searched this forum and those were the choices people had made to get spaces in excel output.

I have tried so many combinations of fields and spaces with the hexbyte parameter, plain spaces, just about everything I can think of. I also tried using the SET SHOWBLANKS = on as well but I am finding I get closer with it off.

So without further boring you, here is the code:

 
-*SET SHOWBLANKS=ON
SET PAGE-NUM=OFF
DEFINE FILE CAR
A_SEATS/A3=EDIT(SEATS);
SOME_BLANK_SEATS/A3=IF A_SEATS EQ '002' THEN HEXBYT(160,'A1') ELSE A_SEATS;
END
-RUN

TABLE FILE CAR
PRINT
CAR AS 'Car'
MODEL AS 'Model'
SOME_BLANK_SEATS AS 'Seats'
DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES AS 'Sales'
LENGTH AS 'Length'
WIDTH AS 'Width'
HEIGHT AS 'Height'
COMPUTE SPACES/A10='          ';

COMPUTE SPACES_2/A10='          '; NOPRINT
COMPUTE SPACES_3/A10='          '; NOPRINT
COMPUTE REC_CNT/D20.2 = LAST REC_CNT + 1; NOPRINT
COMPUTE REC_CNTX/D20.2 = REC_CNT + 2; NOPRINT
COMPUTE ROW_NUMBERY/A20=EDIT(REC_CNTX); NOPRINT
COMPUTE RN_TRIM/A5=TRIM('L',ROW_NUMBERY,20,'0',1,RN_TRIM); NOPRINT
COMPUTE ONE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE TWO/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE THREE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE NBSP3/A9=ONE|TWO|THREE; NOPRINT

COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',LEFT("'|NBSP3|'",3-LEN(C' || RN_TRIM || '))' || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'

BY CAR NOPRINT
BY MODEL NOPRINT

HEADING
"Car Test"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD AS REP001 FORMAT HTMTABLE
ON TABLE SET STYLE *
 UNITS=IN, SQUEEZE=ON, GRID=ON,$
TYPE=HEADING,HEADALIGN=BODY,JUSTIFY=CENTER,COLSPAN=11,$
TYPE=DATA,COLUMN=KONKAT,JUSTIFY=LEFT,WRAP=OFF,$
TYPE=TITLE,CLASS=rotate,$
TYPE=DATA,CLASS=border,$
ENDSTYLE
END
-RUN

SET HTMLFORMTYPE=XLS
-RUN

-HTMLFORM BEGIN
<STYLE>
.border
{
font-size:9pt;
font-family: Arial;
text-align:center;
mso-number-format:"\@";
}

.rotate
{
mso-rotate:90;
vertical-align:center;
height:auto;
font-weight: bold;
text-align: center;
}
</STYLE>
<BODY>
!IBI.FIL.REP001;
</BODY>
-HTMLFORM END
-EXIT

Right now the code above shows the output as a period, a small o, and another period - I am not sure why I get two spaces that are valid and one that is not - just very weird.

I tried to copy/paste it from Word to here but it removed all the periods and small o's - I can check when I am done here to see if we are allowed to attach files to our posts. I just wanted to attach a png file.

Anyway, thanks for listening, and any help would be appreciated.

Paul

This message has been edited. Last edited by: Paul M.,

WebFOCUS 8
Windows, All Outputs

Posts: 37 | Registered: March 09, 2016

Hallway

Master

posted

Hide Post

Since you are outputting it to html first, by default html reduces all multiple space characters down to one space. so if you'd like multiple spaces you'll need to use the   for a non-breaking space character in your code, with a pipe excape on the & character so IBI won't read it as an amper variable:

  
-*SET SHOWBLANKS=ON
SET PAGE-NUM=OFF
DEFINE FILE CAR
A_SEATS/A3=EDIT(SEATS);
SOME_BLANK_SEATS/A63=IF A_SEATS EQ '002' THEN '&|nbsp;&|nbsp;&|nbsp;' ELSE A_SEATS;
END
-RUN

TABLE FILE CAR
PRINT
CAR AS 'Car'
MODEL AS 'Model'
SOME_BLANK_SEATS AS 'Seats'
DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES AS 'Sales'
LENGTH AS 'Length'
WIDTH AS 'Width'
HEIGHT AS 'Height'
COMPUTE SPACES/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;';

COMPUTE SPACES_2/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;'; NOPRINT
COMPUTE SPACES_3/A63='&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;&|nbsp;'; NOPRINT
COMPUTE REC_CNT/D20.2 = LAST REC_CNT + 1; NOPRINT
COMPUTE REC_CNTX/D20.2 = REC_CNT + 2; NOPRINT
COMPUTE ROW_NUMBERY/A20=EDIT(REC_CNTX); NOPRINT
COMPUTE RN_TRIM/A5=TRIM('L',ROW_NUMBERY,20,'0',1,RN_TRIM); NOPRINT
COMPUTE ONE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE TWO/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE THREE/A3=HEXBYT(160,'A1'); NOPRINT
COMPUTE NBSP3/A21=TRIM_(BOTH, ' ',ONE||TWO||THREE); NOPRINT

COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',C' || RN_TRIM || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'

BY CAR NOPRINT
BY MODEL NOPRINT

HEADING
"Car Test"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD AS REP001 FORMAT HTMTABLE
ON TABLE SET STYLE *
 UNITS=IN, SQUEEZE=ON, GRID=ON,$
TYPE=HEADING,HEADALIGN=BODY,JUSTIFY=CENTER,COLSPAN=11,$
TYPE=DATA,COLUMN=KONKAT,JUSTIFY=LEFT,WRAP=OFF,$
TYPE=TITLE,CLASS=rotate,$
TYPE=DATA,CLASS=border,$
ENDSTYLE
END
-RUN

SET HTMLFORMTYPE=XLS
-RUN

-HTMLFORM BEGIN
<STYLE>
.border
{
font-size:9pt;
font-family: Arial;
text-align:center;
mso-number-format:"\@";
}

.rotate
{
mso-rotate:90;
vertical-align:center;
height:auto;
font-weight: bold;
text-align: center;
}
</STYLE>
<BODY>
!IBI.FIL.REP001;
</BODY>
-HTMLFORM END
-EXIT

	*Hallway*
	WF(Prod):	8202M
	WF(Test):	8202M
	OS/Platform:	Win 10
	Outputs:	All

Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015

Ignored post by Hallway posted

Show Post

Paul M.

Silver Member

posted

Hide Post

Hallway,

Thanks so much for your reply.

I completely understand why using "&|nbsp" would be appropriate, however when you get the final excel output and you copy line K4 from the spreadsheet to Word, you get a weird character to isn't really a space. I would have uploaded a screenshot but I don't think you can upload files to this platform.

In Word when you set the Display to show the spaces, an actual space is a period in the display. When you look at what comes out when adding the "&|nbsp", instead of a period it is a small zero or o.

We then take that data and upload it to the customers system but since the value isn't "really" a space it says it is an invalid character.

I am not sure what to do at this point to get actual spaces between the values when the value is blank.

I will continue trying.

Thanks.

Paul

This message has been edited. Last edited by: Paul M.,

WebFOCUS 8
Windows, All Outputs

Posts: 37 | Registered: March 09, 2016

Ignored post by Paul M. posted

Show Post

Hallway

Master

posted

Hide Post

I'm thinking that you will run into this problem every time when outputting it to an HTMTABLE. The HTML parser converts all spaces ( HEXBYT(32,'A1') ) to a   before exporting to XLS.

Try this code using EXL07 FORMULA and see if that gets you ehat you need:

 
DEFINE FILE CAR
A_SEATS/A3=EDIT(SEATS);
A_DEALER_COST/A20V = PTOA(DEALER_COST, '(P12)', 'A20V') ;
SPC/A1 = HEXBYT(32,'A1');
END
TABLE FILE CAR
PRINT
CAR AS 'Car'
MODEL AS 'Model'
COMPUTE SOME_BLANK_SEATS/A10V=IF A_SEATS EQ '002' THEN SPC | SPC | SPC ELSE A_SEATS; AS 'Seats'
A_DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES AS 'Sales'
LENGTH AS 'Length'
WIDTH AS 'Width'
HEIGHT AS 'Height'
COMPUTE KONKAT/A1700= CAR||MODEL||SOME_BLANK_SEATS||A_DEALER_COST; AS 'Concatenated Line'
BY CAR NOPRINT
BY MODEL NOPRINT
HEADING
"Car Test"
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE SET DROPBLNKLINE HEADING
ON TABLE PCHOLD FORMAT EXL07 FORMULA
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=ON,$
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=CENTER, COLSPAN=10, $
TYPE=TITLE, JUSTIFY=CENTER, FONT='ARIAL', SIZE=11, BORDER-TOP=LIGHT, BORDER-RIGHT=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-LEFT=LIGHT, STYLE=BOLD, $
TYPE=DATA, JUSTIFY=CENTER, FONT='ARIAL', SIZE=9, BORDER-TOP=LIGHT, BORDER-RIGHT=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-LEFT=LIGHT, $
TYPE=DATA, COLUMN=KONKAT, JUSTIFY=LEFT, WRAP=OFF, $
ENDSTYLE
END
-RUN

	*Hallway*
	WF(Prod):	8202M
	WF(Test):	8202M
	OS/Platform:	Win 10
	Outputs:	All

Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015

Ignored post by Hallway posted

Show Post

Paul M.

Silver Member

posted

Hide Post

Hallway,

Once again, thanks so much for your help.

Unfortunately, now there are 8 spaces where there should only be 3 - I tried to make that field A3 but it still put in 8 spaces.

Part of the reason I use the HTMTABLE and the SET HTMLFORMTYPE=XLS is for the ability to manipulate the titles and borders and the like in my original post because we are not able to get our macro to work in WF 8.2.0.2m - so the manipulation we do is what gets us the correct titles and such(the real one is the rotating of the titles).

So apart from your awesome help with trying EXL07 FORMULA, I should have said why we were doing these things.

We currently have our report in prod with something a little more clunky than what we see here. I was trying to eliminate some of the steps for our customer.

It looks like we may have to leave it as it is until we can either resolve this code or get our macro to work that stopped working in the new version we upgraded to 8.2.0.2m

Thanks.

Paul

WebFOCUS 8
Windows, All Outputs

Posts: 37 | Registered: March 09, 2016

Ignored post by Paul M. posted

Show Post

Paul M.

Silver Member

posted

Hide Post

I wanted to mention that I resolved this by making a change to the CONCATENATE field. I removed the LEFT parameter and replaced it with the REPT (repeat) function and removed the field NBSP and the ticks (') surrounding it, and when you cut/pate the line into Word it shows the correct number of spaces - much simpler than I was trying to make it.

 COMPUTE KONKAT/A1700='=CONCATENATE(A' || RN_TRIM || ',B' || RN_TRIM || ',REPT(" ",3-LEN(C' || RN_TRIM || '))' || ',D' || RN_TRIM || ')'; AS 'Concatenated Line'

I don't recall how to close this however.

Thanks,

Paul

WebFOCUS 8
Windows, All Outputs

Posts: 37 | Registered: March 09, 2016

Ignored post by Paul M. posted

Show Post

Please Wait. Your request is being processed...

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[SOLVED] Need Spaces to be in excel concatenated file

	View $GS_USERNAME's Public Profile
	Add $GS_USERNAME to my Buddies
	Add $GS_USERNAME to my Ignore ListRemove $GS_USERNAME from my Ignore List
	Invite $GS_USERNAME to a Private Topic
	View Recent Posts by $GS_USERNAME
	Notify me of New Posts by $GS_USERNAME

Quick Reply to: [SOLVED] Need Spaces to be in excel concatenated file
Guest Name

Close \| Use Full Posting Form \| Quick Quote