I have a defined field that I can't get rid of the extra spaces. Here is the code:

COURSE_ID/A27=CSPRDSC_CLASS_TBL.CSPRDSC_CLASS_TBL.SUBJECT || CSPRDSC_CLASS_TBL.CSPRDSC_CLASS_TBL.CATALOG_NBR ||(' '| CSPRDSC_CLASS_TBL.CSPRDSC_CLASS_TBL.CLASS_SECTION )|| '-' || CSPRDSC_CLASS_TBL.CSPRDSC_CLASS_TBL.SESSION_CODE;
  

It leaves extra spaces to fill up the 27 allowed characters after the session code. I want to get rid of those. I tried putting a || after the session code and the program errors telling me that I am missing an arguement.

Malinda

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

I mentioned in a similar post yesterday - when you define a field as A27, it will ALWAYS have 27 characters, so it will always pad with trailing spaces to fit the length specified. You can play with an A27V format which elminates those, but then you have 5 characters at the beginning of the field that specify how long the field actually is. You can also look at TRUNC and SQUEEZ, but as long as the format is A27, it will pad with spaces.

One way to solve this would be to COMPUTE only one output field, concatenating all the fields you require into that one field, including the commas and quotes. Something like this:

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA

FILEDEF HCOMM1 DISK baseapp/hcomm1.prn

TABLE FILE CAR
PRINT
COMPUTE
OUTPUT1/A200 = '"' || COUNTRY || '","' || CAR || '","' || MODEL || '"';
ON TABLE HOLD AS HCOMM1 FORMAT ALPHA
END

You still will have some trailing blanks at the end of the data row.

Or magically, like this, with no trailing blanks:

-*-- trailing2.fex - Create a file with variable length records, no trailing blanks

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA

FILEDEF H001 DISK baseapp/h001.prn
-RUN

TABLE FILE CAR
SUM
COMPUTE COSTX/A7 = FTOA(DEALER_COST, '(D7Lc)', 'A7'); NOPRINT
COMPUTE SALESX/A6 = EDIT(SALES); NOPRINT

COMPUTE ALPHA_LINE/A200 = '"' || COUNTRY || '","' || COSTX || '","' || SALESX || '"';

BY COUNTRY NOPRINT
ON TABLE HOLD AS H001 FORMAT TAB
END
-RUN

It appears that TAB delimited does not produce trailing blanks and since there is only one output field, there are no tabs either.

So if I try to SQUEEZ a field first in order to only leave 1 blank then I can TRIM the last blank.....my problem is that my field lengths vary so when I format my field to a certain length I have to go with the longest..... How would this work. In my term field, some are just Fall 2010 and others are Fall 2010 1st 8 Weeks so the Fall 2010 has a lot more trailing spaces than the Fall 2010 1st 8 Weeks.

Malinda

Try ARGLEN

Malinda,

Have you tried my suggestion?

-*-- trailing2.fex - Create a file with variable length records, no trailing blanks

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA

FILEDEF H001 DISK baseapp/h001.prn
-RUN

TABLE FILE CAR
SUM
COMPUTE COSTX/A7 = FTOA(DEALER_COST, '(D7Lc)', 'A7'); NOPRINT
COMPUTE SALESX/A6 = EDIT(SALES); NOPRINT

COMPUTE ALPHA_LINE/A200 = '"' || COUNTRY || '","' || COSTX || '","' || SALESX || '"';

BY COUNTRY NOPRINT
ON TABLE HOLD AS H001 FORMAT TAB
END
-RUN

Francis,

Yes I tried combining everything into 1 line with my commas included but it still is leaving a bunch of trailing spaces at the very end. And when i tell it format tab it says no query information to decode.

Malinda

When I try this:

 SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
DEFINE FILE CALLHLD
SITE_ID/A11=IF CALLHLD.SEG01.SITE_ID_SEQ EQ ' ' THEN CALLHLD.SEG01.SITE_ID_CLASS ELSE CALLHLD.SEG01.SITE_ID_SEQ;
START_DATE/A10=DATETRAN(START_DT,'(YYMD)','(-)','EN', 10,START_DATE);
END_DATE/A10=DATETRAN(END_DT,'(YYMD)','(-)','EN', 10,END_DATE);
-*DELIM/A01=',';
-*TERM_OUT/A51= CALLHLD.SEG01.TERM | DELIM;
LINE_OUT/A66 = '"' ||CALLHLD.SEG01.TERM|| '","' ||CALLHLD.SEG01.SITE_ID|| '"';
END
TABLE FILE CALLHLD
PRINT
       LINE_OUT
-*COMPUTE TERM_SQ/A51 = SQUEEZ(51, TERM_OUT, 'A51');
-*     'CALLHLD.SEG01.TERM'
-*     'CALLHLD.SEG01.SITE_ID'
-*     START_DATE
-*     'CALLHLD.SEG01.TITLE'
-*     'CALLHLD.SEG01.COURSE_ID'
-*     'CALLHLD.SEG01.DESCR'
-*     'CALLHLD.SEG01.PUBLISH'
-*     END_DATE
-*     'CALLHLD.SEG01.TEMPLATE'
BY CALLHLD.SEG01.TERM NOPRINT
BY CALLHLD.SEG01.COURSE_ID NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT COMMA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

I get this:

""Fall 2010","1108.12754" "
""Fall 2010","1108.10929" "
""Fall 2010","1108.10929" "
""Fall 2010","1108.10493" "
""Fall 2010","1108.10649" "
""Fall 2010","1108.10666" "
""Fall 2010","1108.11651" "
""Fall 2010","1108.12902" "
""Fall 2010","1108.11956" "
""Fall 2010","1108.12767" "
""Fall 2010","1108.11968" "
""Fall 2010","1108.11282" "

Except when I look at it in notepad there are a bunch of blanks between the 2 " at the end of each line.

You cannot use FORMAT COMMA, because it will have trailing blanks - that's what you're trying to avoid!

It must be FORMAT TAB. If you're getting "no query information to decode", it's because of some other error.

Why are you using ON TABLE PCHOLD instead of ON TABLE HOLD? Are you expecting to have the file returned to your web browser session?

My example works in conjunction with the FILEDEF command and the ON TABLE HOLD command to save the file on the server.

I guess I was trained to use pchold.....Here is what I switched it to:

 SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
FILEDEF SAKAI DISK c:/sakai.csv
DEFINE FILE CALLHLD
SITE_ID/A11=IF CALLHLD.SEG01.SITE_ID_SEQ EQ ' ' THEN CALLHLD.SEG01.SITE_ID_CLASS ELSE CALLHLD.SEG01.SITE_ID_SEQ;
START_DATE/A10=DATETRAN(START_DT,'(YYMD)','(-)','EN', 10,START_DATE);
END_DATE/A10=DATETRAN(END_DT,'(YYMD)','(-)','EN', 10,END_DATE);
-*DELIM/A01=',';
-*TERM_OUT/A51= CALLHLD.SEG01.TERM | DELIM;
LINE_OUT/A66 = CALLHLD.SEG01.TERM|| '",' ||CALLHLD.SEG01.SITE_ID;
END
TABLE FILE CALLHLD
PRINT
       LINE_OUT
-*COMPUTE TERM_SQ/A51 = SQUEEZ(51, TERM_OUT, 'A51');
-*     'CALLHLD.SEG01.TERM'
-*     'CALLHLD.SEG01.SITE_ID'
-*     START_DATE
-*     'CALLHLD.SEG01.TITLE'
-*     'CALLHLD.SEG01.COURSE_ID'
-*     'CALLHLD.SEG01.DESCR'
-*     'CALLHLD.SEG01.PUBLISH'
-*     END_DATE
-*     'CALLHLD.SEG01.TEMPLATE'
BY CALLHLD.SEG01.TERM NOPRINT
BY CALLHLD.SEG01.COURSE_ID NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS SAKAI FORMAT TAB
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

and I get this:

0 NUMBER OF RECORDS IN TABLE= 414 LINES= 207
0 NUMBER OF RECORDS IN TABLE= 22 LINES= 11
0 NUMBER OF RECORDS IN TABLE= 207 LINES= 207
0 NUMBER OF RECORDS IN TABLE= 207 LINES= 207
0 ERROR AT OR NEAR LINE 411 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC350) ERROR WRITING OUTPUT FILE: SAKAI

Regarding the removal of trailing blanks, you could try using the technique of specifying output format with a field value. Here function ARGLEN is used to determine the non-blank length of each COUNTRY/CAR/MODEL string. The string length is attached to the letter 'A' to become the output format for the PRINTed string.

DEFINE FILE CAR
 CCM/A200    = COUNTRY || '-' || CAR || ',' || MODEL ;
 CCM_FMT/A10 = 'A' || EDIT(ARGLEN(200,CCM,'I3'));
END
-*
TABLE FILE CAR
 PRINT CCM/CCM_FMT
END

Does the output file require quotes around text fields ?

If not try this.

TABLE FILE CAR
PRINT COUNTRY CAR MODEL
ON TABLE HOLD AS COMMA FORMAT DFIX DELIMITER ,
END
-RUN

I will try this.....on the ccm_ftm why the a10? and then then i3?

Waz,

With the on table hold as comma format dfix delimiter , it is just telling me how many lines there are and not actually outputing anything......

Malinda

If you PCHOLD a DFIX it will give you the master file.

Try this

TABLE FILE CAR
PRINT COUNTRY CAR MODEL
ON TABLE HOLD AS COMMA FORMAT DFIX DELIMITER ,
END
-RUN
SET HTMLFORMTYPE=COMMA
-*SET HTMLFORMTYPE=XLS
-HTMLFORM COMMA

I would agree with Waz, DFIX is probably your best solution. However remember that there are more parmaeters than just DELIMITER -

DEFINE FILE CAR
  LINEOUT/A100 = COUNTRY || (' ' | CAR) || '-' || MODEL;
END
TABLE FILE CAR
PRINT LINEOUT
   BY COUNTRY
   BY CAR
   BY MODEL
ON TABLE HOLD AS TESTDFIX FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO
END
CMD COPY TESTDFIX.* c:\somefolder\etc\etc

This will give you similar output as FORMAT COMMA and changing to HEADER YES will give you similar output as FORMAT COMT - however, you will not have trailing spaces.

T

You guys are making this too hard.

DEFINE FILE CAR
  LONGTXT/A200=MODEL || '-' || BODYTYPE || '-' || 'THIS IS A LONG FIELD';
END
TABLE FILE CAR
PRINT LONGTXT COUNTRY CAR
ON TABLE HOLD FORMAT COMT
END

In my envrionment (Linux), none of the fields or rows have trailing blanks.

HOLD FORMAT TABT and COMT remove trailing blanks in fields

jgelona,

I am on Windows and not Linux, will this make a difference?

Malinda

When I try the on table hold format comt I just get a window that tells me how many records are in it.....

malinda

Dan,

When I tried the ARGLEN I used this in my code

DEFINE FILE CALLHLD
SITE_ID/A11=IF CALLHLD.SEG01.SITE_ID_SEQ EQ ' ' THEN CALLHLD.SEG01.SITE_ID_CLASS ELSE CALLHLD.SEG01.SITE_ID_SEQ;
START_DATE/A10=DATETRAN(START_DT,'(YYMD)','(-)','EN', 10,START_DATE);
END_DATE/A10=DATETRAN(END_DT,'(YYMD)','(-)','EN', 10,END_DATE);
COMBINED_FIELDS/A230=CALLHLD.SEG01.TERM || ',' || CALLHLD.SEG01.SITE_ID || ',' || START_DATE || ',' || CALLHLD.SEG01.TITLE || ',' || CALLHLD.SEG01.COURSE_ID || ',' || CALLHLD.SEG01.DESC || ',' || CALLHLD.SEG01.PUBLISH || ',' || END_DATE || ',' || CALLHLD.SEG01.TEMPLATE;
LINE_OUT/A10='A' || EDIT(ARGLEN(230,LINE_OUT,'I3'));
END
TABLE FILE CALLHLD
PRINT
     COMBINED_FIELDS/LINE_OUT
BY CALLHLD.SEG01.TERM NOPRINT
BY CALLHLD.SEG01.COURSE_ID NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE HOLD FORMAT DFIX DELIMITER ,
ON TABLE PCHOLD FORMAT COMMA  

and all I get are ++++++++++++ with quotes around them.

Malinda

COMBINED_FIELDS/A230=CALLHLD.SEG01.TERM || ',' || CALLHLD.SEG01.SITE_ID || ',' || START_DATE || ',' ||
 CALLHLD.SEG01.TITLE || ',' || CALLHLD.SEG01.COURSE_ID || ',' || CALLHLD.SEG01.DESC || ',' || 
CALLHLD.SEG01.PUBLISH || ',' || END_DATE || ',' || CALLHLD.SEG01.TEMPLATE;
LINE_OUT/A10='A' || EDIT(ARGLEN(230,LINE_OUT,'I3'));
END

S/B

LINE_OUT/A10='A' || EDIT(ARGLEN(230,COMBINED_FIELDS,'I3'));

Well duh....thanks for catching that. Now it gives me output but I still have lots of blanks after my last field and before my last quotes.

Malinda

Tony I tried this....

quote:

I would agree with Waz, DFIX is probably your best solution. However remember that there are more parmaeters than just DELIMITER -


DEFINE FILE CAR
LINEOUT/A100 = COUNTRY || (' ' | CAR) || '-' || MODEL;
END
TABLE FILE CAR
PRINT LINEOUT
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS TESTDFIX FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO
END
CMD COPY TESTDFIX.* c:\somefolder\etc\etc

This will give you similar output as FORMAT COMMA and changing to HEADER YES will give you similar output as FORMAT COMT - however, you will not have trailing spaces.

And it mostly seems to work after all the report formatting and the end I say cmd copy sakaicrs.* c:\documents and it says that it has copied the files but nowhere can I acutally find it.

I tried defining my fields as variable length text fields:

 TERM_OUT/TX=CALLHLD.SEG01.TERM;
SITE_ID_OUT/TX=CALLHLD.SEG01.SITE_ID;  

and then printing:

      TERM_OUT
	 SITE_ID_OUT 

but it says

  (FOC709) INVALID POSITION OF THE TEXT FIELD DURING SAVE OR HOLD
 (FOC709) INVALID POSITION OF THE TEXT FIELD DURING SAVE OR HOLD
 

I finally got it to work!!! Since several colleges are sharing WebFocus servers so it isn't installed directly on my pc. So this is what I had to do to get it to output in a csv format with no padding. Thanks for the help everyone!!!

 DEFINE FILE CALLHLD
SITE_ID/A11=IF CALLHLD.SEG01.SITE_ID_SEQ EQ ' ' THEN CALLHLD.SEG01.SITE_ID_CLASS ELSE CALLHLD.SEG01.SITE_ID_SEQ;
START_DATE/A10=DATETRAN(START_DT,'(YYMD)','(-)','EN', 10,START_DATE);
END_DATE/A10=DATETRAN(END_DT,'(YYMD)','(-)','EN', 10,END_DATE);
COMBINED_FIELDS/A234= CALLHLD.SEG01.TERM || ',' || CALLHLD.SEG01.SITE_ID || ',' || START_DATE || ',' || CALLHLD.SEG01.TITLE || ',' || CALLHLD.SEG01.COURSE_ID || ',' || CALLHLD.SEG01.DESC || ',' || CALLHLD.SEG01.PUBLISH || ',' || END_DATE || ',' || CALLHLD.SEG01.TEMPLATE;
END
TABLE FILE CALLHLD
PRINT
     COMBINED_FIELDS
BY CALLHLD.SEG01.TERM NOPRINT
BY CALLHLD.SEG01.COURSE_ID NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO 

quote:

cmd copy sakaicrs.* c:\documents

One thing to remember when running this sort of command - it is relative to the server and not your local machine.

You are not alone in this slip as this is one of the most common misunderstandings and has occured many thimes in the forum.

Out of interest, why are you including a blank header and footer or is that just habit?

T

Just to confuse everyone, if you don't want to use DFIX, then you could use the function PUTDDREC.

This writes out the contents of a field to a specified DDName.

FILEDEF CSVOUT DISK csvout.csv

DEFINE FILE CAR
 CCM/A200    = COUNTRY || '-' || CAR || ',' || MODEL ;
 CCM_FMT/A10 = 'A' || EDIT(ARGLEN(200,CCM,'I3'));

END
-*
TABLE FILE CAR
 PRINT CCM
       COMPUTE
       OUT_CSV/I1 = PUTDDREC('CSVOUT',6,CCM,ARGLEN(200,CCM,'I3'),OUT_CSV) ;
END

-RUN

! copy csvout.csv c:\temp

Bored are you Waz?

T

quote:

Out of interest, why are you including a blank header and footer or is that just habit?

Tony,

I guess the blank header and footer are in there from the gui.

Malinda

That is correct and you can delete if you wish.