Most of you know I have been working on getting one of my files to output in csv correctly. That I got....thanks again. The company we send this to suggested adding in some quotes around my title and desc of the course just in case there are commas in those to eliminate confusion. I tried doing it here:

 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/A238= 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 

But in the output I get this......

"Fall 2010,1108.12754,2010-08-23,""AGRI 431 0089-1-INTERNATIONAL FOOD POLICY"",AGRI 431 0089-1,""INTERNATIONAL FOOD POLICY"",0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10929,2010-08-23,""AGRI 438 0089-1-LAND RESOURCE MGMT PLANNING"",AGRI 438 0089-1,""LAND RESOURCE MGMT PLANNING"",0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10929,2010-08-23,""AGRI 538 0089-1-LAND RESOURCE MGMT PLANNING"",AGRI 538 0089-1,""LAND RESOURCE MGMT PLANNING"",0,2010-12-17,template.webenhanced"

It puts in the " but there should only by 1 set around each....can anyone tell my why it is doing this?

Malinda

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

The double-quote is always added when saving as comma delimited format. It's only "extra" because you're adding your own.

How come it doesn't have "" around each field then....so since I am adding the "" and outputting as csv it sees the differentiation and adds it again.....Is there a way to do what I am wanting?

Thanks,
Malinda

Malinda,

Can you post the results you'd obtain for COMBINED_FIELDS *without* the intermediate quotes you added to it? I'm particularly interested in knowing what value you have for CALLHLD.SEG01.TITLE.

Can you also post the complete TABLE FILE CALLHLD request (please exclude HEADING/FOOTING ans styling as it's not relevant). I'd like to see your ON TABLE [PC]HOLD format as well.

Thanks.

Why do you also have initial and final quotes around your resulting record?

quote:

"Fall ... webenhanced"

Which format is your output? if you're creating your own combination of fields, you should be doing an ON TABLE SAVE and/or ON TABLE HOLD FORMAT ALPHA or something similar to that effect.

Here is what is generated for the combined_fields withouth the intermediate quotes......

"Fall 2010,1108.12754,2010-08-23,AGRI 431 0089-1-INTERNATIONAL FOOD POLICY,AGRI 431 0089-1,INTERNATIONAL FOOD POLICY,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10929,2010-08-23,AGRI 438 0089-1-LAND RESOURCE MGMT PLANNING,AGRI 438 0089-1,LAND RESOURCE MGMT PLANNING,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10929,2010-08-23,AGRI 538 0089-1-LAND RESOURCE MGMT PLANNING,AGRI 538 0089-1,LAND RESOURCE MGMT PLANNING,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10493,2010-08-23,ART 131 0001-1-DRAWING I,ART 131 0001-1,DRAWING I,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10649,2010-08-23,ART 325 001-1-PRINTMAKING II,ART 325 001-1,PRINTMAKING II,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.10666,2010-08-23,ART 339 0001-1-ART HISTORY SURVEY II,ART 339 0001-1,ART HISTORY SURVEY II,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.11651,2010-08-23,BIOL 221 0079-1-BIOTERMINOLOGY,BIOL 221 0079-1,BIOTERMINOLOGY,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.12902,2010-08-23,BIOL 458 0079-1-EVOLUTION,BIOL 458 0079-1,EVOLUTION,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.11956,2010-08-23,CHEM 131 0001-1-COLLEGE CHEMISTRY I,CHEM 131 0001-1,COLLEGE CHEMISTRY I,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.12767,2010-08-23,CHEM 231 0001-1-INTRO TO ORGANIC CHEMISTRY,CHEM 231 0001-1,INTRO TO ORGANIC CHEMISTRY,0,2010-12-17,template.webenhanced"
"Fall 2010,1108.11968,2010-08-23,CHEM 233 0001-1-ORGANIC CHEMISTRY I,CHEM 233 0001-1,ORGANIC CHEMISTRY I,0,2010-12-17,template.webenhanced"

Here is the complete table file callhld request....

 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
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO
ON TABLE SET HTMLCSS ON 

As for initial and final quotes around "Fall.....webenhanced" that is just how it formatted it when I added in the csv wording. I need it to output in csv format...from a previous discussion I was having problems getting the csv without extra padding and using the ON TABLE PCHOLD FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO got rid of this. Looking at it now.....if I get rid of the ENCLOSURE " and make it like ENCLOSURE NO (or whatever this commands need to be) that should fix the initial and final quotes....

Thanks,
Malinda

If I include the "" in my combined field formatting like this and take out the enclosure command so my code would look like this:

 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
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT DFIX DELIMITER , HEADER NO
ON TABLE SET HTMLCSS ON 

I get the following output which seems to be exactly what I want.....

Fall 2010,1108.12754,2010-08-23,"AGRI 431 0089-1-INTERNATIONAL FOOD POLICY",AGRI 431 0089-1,"INTERNATIONAL FOOD POLICY",0,2010-12-17,template.webenhanced
Fall 2010,1108.10929,2010-08-23,"AGRI 438 0089-1-LAND RESOURCE MGMT PLANNING",AGRI 438 0089-1,"LAND RESOURCE MGMT PLANNING",0,2010-12-17,template.webenhanced
Fall 2010,1108.10929,2010-08-23,"AGRI 538 0089-1-LAND RESOURCE MGMT PLANNING",AGRI 538 0089-1,"LAND RESOURCE MGMT PLANNING",0,2010-12-17,template.webenhanced
Fall 2010,1108.10493,2010-08-23,"ART 131 0001-1-DRAWING I",ART 131 0001-1,"DRAWING I",0,2010-12-17,template.webenhanced
Fall 2010,1108.10649,2010-08-23,"ART 325 001-1-PRINTMAKING II",ART 325 001-1,"PRINTMAKING II",0,2010-12-17,template.webenhanced
Fall 2010,1108.10666,2010-08-23,"ART 339 0001-1-ART HISTORY SURVEY II",ART 339 0001-1,"ART HISTORY SURVEY II",0,2010-12-17,template.webenhanced
Fall 2010,1108.11651,2010-08-23,"BIOL 221 0079-1-BIOTERMINOLOGY",BIOL 221 0079-1,"BIOTERMINOLOGY",0,2010-12-17,template.webenhanced
Fall 2010,1108.12902,2010-08-23,"BIOL 458 0079-1-EVOLUTION",BIOL 458 0079-1,"EVOLUTION",0,2010-12-17,template.webenhanced

Could it be possible that the DFIX .. ENCLOSURE format had some internal logic that encodes any embedded quotes found within the data therefore resolving it into a double quote?

I've never used DFIX before but to my original reply, if you are already have your COMBINED_FIELDS field which includes the internal commas and probably the quotes you need to separate the data, then why are you still using DFIX DELIMITER , ENCLOSURE " ?

Being the field as is right now, you should be able to produce a regular "text" output (ON TABLE SAVE or HOLD FORMAT ALPHA).

Did you try that?

Why are you combining the fields into one?

The DFIX command will put double-quotes around each field in the request, so why are you not using the individual fields instead?

APP HOLDDATA BASEAPP
DEFINE FILE CENTORD
SITE_ID/A20=IF STORE_CODE EQ ' ' THEN STORENAME ELSE STORE_CODE;
START_DATE/A10=DATETRAN(ORDER_DATE,'(YYMD)','(-)','EN', 10,START_DATE);
END_DATE/A10=DATETRAN(ORDER_DATE,'(YYMD)','(-)','EN', 10,END_DATE);

-*COMBINED_FIELDS/A234= 
-*ORDER_NUM || ',' || STORE_CODE || ',' || 
-*START_DATE || ',' || PLANT || ',' || PROD_NUM || ',' || 
-*PRODNAME || ',' || STATE || ',' || END_DATE || ',' || STORENAME;
END

TABLE FILE CENTORD
PRINT 
SITE_ID
ORDER_NUM
STORE_CODE
START_DATE
PLANT
PROD_NUM
PRODNAME
STATE
END_DATE
STORENAME

WHERE RECORDLIMIT EQ 100
ON TABLE HOLD FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO

END

This gives me a comma-delimited file with no trailing blanks between the commas or at the end of each row, and I get double-quotes around each alpha field. Isn't this what you're looking for?

quote:

I get the following output which seems to be exactly what I want

Good! I'm glad you fixed it.

Why would you want

quote:

Fall 2010,1108.12754,...

and not

quote:

"Fall 2010",1108.12754,...

quote:

The company we send this to suggested adding in some quotes around my title and desc of the course just in case there are commas in those to eliminate confusion.

Maybe that's the reason why but it does not answer Francis' question entirely. What if "someone" decides to change the TERM description and squeeze a comma in there so instead of "Fall 2010" your database now has "Fall, 2010". That comma there would render the CSV file a bit unusable; well, not really, one can always write some parser/fixer on top of it to account for such cases but why make life more difficult?

Enclose every string field in quotes and save yourself some future trouble. No decent CSV parser utility would complain about that!

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

quote:

Could it be possible that the DFIX .. ENCLOSURE format had some internal logic that encodes any embedded quotes found within the data therefore resolving it into a double quote?

I've never used DFIX before but to my original reply, if you are already have your COMBINED_FIELDS field which includes the internal commas and probably the quotes you need to separate the data, then why are you still using DFIX DELIMITER , ENCLOSURE " ?

That was just a suggestion from another user and it seemed to work but you are right, seems like I am duplicating efforts. But from what I have been able to tell, you can't just to format dfix, you have to include the rest of the statement.....

quote:

Being the field as is right now, you should be able to produce a regular "text" output (ON TABLE SAVE or HOLD FORMAT ALPHA).

Doing the ON TABLE PCHOLD FORMAT ALPHA did work.

Malinda

quote:

The DFIX command will put double-quotes around each field in the request, so why are you not using the individual fields instead?
ON TABLE HOLD FORMAT DFIX DELIMITER , ENCLOSURE " HEADER NO

This does work for me too.....I think most of the problem was there were many good ideas and I just have to find the one that works the best for what I need! And like njsden said, there shouldn't be a problem with this file.....

Thanks for the help again...I believe doing it this way is now what I really needed and the best solution!

Malinda