Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need Spaces to be in excel concatenated file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Need Spaces to be in excel concatenated file
 Login/Join
 
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, 2016Report This Post
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 &nbsp; 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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
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, 2016Report This Post
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 &nbsp; 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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
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, 2016Report This Post
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, 2016Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need Spaces to be in excel concatenated file

Copyright © 1996-2020 Information Builders