I am creating a CSV file out of Webfocus 7.6.9. Except for the numeric columns, the rest are all enclosed within double quotes. I am using COMT format.

Can any of you kindly let me know what should I have to do if i want even the numeric fields also to be enclosed within double quotes in the generated CSV file.

Please note the columns I choose in the SELECT clause is dynamic and thereby i wouldnt know the datatype of the column before itself.

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

You could convert the number to text before writing to the CSV file.

You will have to determine the format of the column first.

This can be done by either:
1. Check SYSCOLUM to get the format for thereturned hold file.
2. Use CHECK FILE ... HOLD to get the columns and formats.

With the list of columns and formats you can use DM to build computes in the TABLE...HOLD FORMAT COMT.

quote:

With the list of columns and formats you can use DM to build computes in the TABLE...HOLD FORMAT COMT

Waz,

we could read the file format but how can we replace the numeric data-type to alpha. So that we can get format to text file where all the numeric values converted to alpha with double-quotes.

I would suggest reading the "Using Functions" book. Look up

EDIT
FTOA
PTOA

Those three should cover everything you need.

Except if there are any dates.

Hi,

I could read the integers and format it to alpha. But when I retrieve the data to Text file using COMMA, I see spaces in between quotes, this is due to specifying the alpha format to A50.
In between the code I couldn't use Trim function. Could you please help me in removing spaces by using Trim function or STRREP.

  
CHECK FILE CAR HOLD AS H1
-RUN
DEFINE FILE H1
FLAG/D2 = IF FORMAT OMITS 'A' THEN 1 ELSE 0;
END
 
TABLE FILE H1
PRINT
FIELDNAME
-*WHERE FIELDNAME EQ 'COUNTRY' OR 'CAR' OR 'SALES' OR 'DEALER_COST'
WHERE FLAG EQ 1
ON TABLE SAVE AS H1_SAVE
END
 
-RUN
-SET &RECS1 = &LINES ;
-SET &CNTR1 = 0;
-REPEAT :ENDREP1 &RECS1 TIMES
-SET &CNTR1 = &CNTR1+1;
-READ H1_SAVE &FLD_NM.&CNTR1.A66
-*-TYPE &FLD_NM.&CNTR1
-SET &NAME.&CNTR1 =&FLD_NM.&CNTR1 ||'_A';
-TYPE &NAME.&CNTR1
-:ENDREP1
-TYPE VALUE IS &FLD_NM1
-TYPE LOOP_CNT IS &RECS1
-RUN
 
TABLE FILE H1
PRINT
FORMAT
-*WHERE FIELDNAME EQ 'COUNTRY' OR 'CAR' OR 'SALES' OR 'DEALER_COST'
WHERE FLAG EQ 1
ON TABLE SAVE AS H2_SAVE
END
-RUN
-SET &RECS2 = &LINES ;
-SET &CNTR2 = 0;
-REPEAT :ENDREP2 &RECS2 TIMES
-SET &CNTR2 = &CNTR2+1;
-READ H2_SAVE &FMT.&CNTR2.A8
-TYPE &FMT.&CNTR2
-:ENDREP2
-TYPE VALUE IS &FMT1
-TYPE LOOP_CNT IS &RECS2
-RUN
 
 
 
 
-SET &CNTR3 = 0;
DEFINE FILE CAR
-REPEAT :ENDREP3 &RECS1 TIMES
-SET &CNTR3 = &CNTR3+1;
&NAME.&CNTR3/A50 = FTOA(&FLD_NM.&CNTR3, '(&FMT.&CNTR2)', &NAME.&CNTR3);
-:ENDREP3
END
-SET &CNTR4 = 0;
TABLE FILE CAR
PRINT
-REPEAT :ENDREP4 &RECS1 TIMES
-SET &CNTR4 = &CNTR4+1;
&NAME.&CNTR4
 
-:ENDREP4
-*ON TABLE SAVE
ON TABLE PCHOLD FORMAT COMMA
END

The issue will be the A50.

Here is something a little simpler you may be able to use.

CHECK FILE CAR HOLD AS CARFIELDS
-RUN

TABLE FILE CARFIELDS
PRINT COMPUTE CODE/A258 = IF EDIT(FORMAT,'9') EQ 'A' THEN FIELDNAME ELSE 'COMPUTE A_' || FIELDNAME || '/A' || EDIT(EXLEN) || '=FTOA(' || FIELDNAME || ',''(' || FORMAT || ')'',A_' || FIELDNAME || ');' ;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS FEX_FIELDS
END
-RUN

TABLE FILE CAR
PRINT
-INCLUDE FEX_FIELDS
ON TABLE PCHOLD FORMAT COMMA
END

Yes, this resolves our issue. You made our life easy and simple..

You are truly awesome and genius.
Thanks for all your help.

Oh dear, I think I'm getting a BIG HEAD.....

Not wishing to steal Waz's thunder, but you will need a slight change to the CODE evaluation for integers -

PRINT COMPUTE CODE/A258 = IF EDIT(FORMAT,'9') EQ 'A' THEN FIELDNAME
                     ELSE IF EDIT(FORMAT,'9') EQ 'I' THEN 'COMPUTE A_' || FIELDNAME ||'/A' || EDIT(EXLEN) || '=TRIM(''L'', EDIT(' || FIELDNAME || '), ' || EDIT(EXLEN) || ', ''0'',' || EDIT(EXLEN) || ', A_' || FIELDNAME || ');'
                     ELSE 'COMPUTE A_' || FIELDNAME || '/A' || EDIT(EXLEN) || '=FTOA(' || FIELDNAME || ',''(' || FORMAT || ')'',A_' || FIELDNAME || ');' ;

Of course you may find other formats that you need to cater for differently, but the important thing is Waz has given you an excellent starting point from which to build.

T

Aaahhh,

Big head deflated, thanks Tony, I needed that.

Waz,

You supplied the boat and the rod, all I did was apply a little caulking!

T