comma delimited files

W

whwar9739

Member
Hey all,

I am trying to find a nice way of creating a header row for an export file. Currently for any of the programs that I have written, I have been just hard coding either a put statement with the commas in it or using an export delimiter ',' statement with each of the headers in quotes.

Code: 
PUT STREAM rpt UNFORMATTED "Account Number,Statement Type,New Statement Type".

I would like to be able to use the actual definition for the field instead to create a header row rather than just typing it all the way out.

To try to help describe here is some psuedo code:
Code: 
EXPORT DELIMITER ',' _field.title _field2.title _field3.title...

Any help would be appreciated

Thanks,
 
Here's what i use to do my comma delimited files. I don't think this is what you are looking for but this way i find pretty simple. If this isn't what you are looking for give me a better description.
Code: 
DEFINE VARIABLE OutString AS CHARACTER   NO-UNDO.

OUTPUT TO N:\PRO400\TC\TC.csv.

OutString =
    "Label1"             + CHR(44) +
    "Label2"             + CHR(44) +
    "Label3"             + CHR(44) +
    "Label4"             + CHR(44) +
    "Label5"             + CHR(44) +
    "Label6"             + CHR(44) +
    "Label7".

PUT UNFORMATTED OutString SKIP(0).

FOR EACH tablename (WHERE Condition <-Here) NO-LOCK BREAK BY Tablename.TablenameID:

    OutString =
        TRIM(FIELD1)                   + CHR(44) +
        TRIM(FIELD2)                   + CHR(44) +
        TRIM(FIELD3)                   + CHR(44) +
        TRIM(FIELD4)                   + CHR(44) +
        TRIM(FIELD5)                   + CHR(44) +
        TRIM(FIELD6)                   + CHR(44) +
        TRIM(FIELD7).
        
    PUT UNFORMATTED OutString SKIP(0).

END.

OUTPUT CLOSE.
 
If you want something simple. I have an include i got from someone on here that you send a browser handle to and it will generate an excel file. You can reverse it i guess and make an include that you can pass a table and it will make a comma delimited file.

Code: 
/* export.i - general Routine to export a Browser to Excel */
/* call: Run excel-export (browser-handle). */

procedure Excel-Export:
   def input parameter p-browse as handle no-undo.
   def var h-excel as com-handle no-undo.
   def var h-book as com-handle no-undo.
   def var h-sheet as com-handle no-undo.
   def var v-item as char no-undo.
   def var v-alpha as char extent 52 no-undo init ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"].
   def var i as int no-undo.
   def var v-line as int no-undo.
   def var v-qu as log no-undo.
   def var v-handle as handle no-undo.
   v-qu = session:set-wait-state("General").
   CREATE "Excel.Application" h-Excel.
   h-book = h-Excel:Workbooks:Add().
   h-Sheet = h-Excel:Sheets:Item(1).
   
   do i = 1 to p-browse:num-columns:
      v-handle = p-browse:get-browse-column(i).
      v-item = v-alpha[i] + "1".
      h-sheet:range(v-item):value = v-handle:label.
   end.
   v-line = 1.
   repeat:
      if v-line = 1 then 
         v-qu = p-browse:select-row(1).
      else v-qu = p-browse:select-next-row().
      if v-qu = no then leave.
      v-line = v-line + 1.
      do i = 1 to p-browse:num-columns:
         v-handle = p-browse:get-browse-column(i).
         v-item = v-alpha[i] + string(v-line).
         if v-handle:data-type begins "dec" then assign
            h-sheet:range(v-item):value = dec(v-handle:screen-value)
            h-sheet:range(v-item):Numberformat = "###,###,##0.00"
            h-sheet:range(v-item):HorizontalAlignment = -4152.
         else if v-handle:data-type begins "int" then assign
            h-sheet:range(v-item):value = int(v-handle:screen-value)
            h-sheet:range(v-item):Numberformat = "###,###,##0"
            h-sheet:range(v-item):HorizontalAlignment = -4152.
         else h-sheet:range(v-item):value = v-handle:screen-value.

      end.
   end.
   do i = 1 to p-browse:num-columns:
      v-qu = h-sheet:Columns(i):AutoFit.
   end.
   h-excel:visible = yes.
   release object h-sheet no-error.
   release object h-book no-error.
   release object h-excel no-error.
   v-qu = session:set-wait-state("").
end procedure.
 
What I am looking for is a way to use the defined column label for the header information. Kind of like what would show up when doing a normal display function only I want it comma delimited rather than fixed length.
 
Are you trying to export column labels defined in the database? If so, and are version 9 or higher you could try something like this:

Code: 
DEFINE VARIABLE i AS INT.
DEFINE VARIABLE bh AS HANDLE.
DEFINE VARIABLE fh AS HANDLE.
 
bh = BUFFER Customer:HANDLE.
 
PUT STREAM rpt UNFORMATTED '"'.
DO i = 1 TO bh:NUM-FIELDS:
   ASSIGN fh = bh:BUFFER-FIELD(i).
   IF i > 1 THEN
      PUT STREAM rpt UNFORMATTED ",".
   PUT STREAM rpt UNFORMATTED fh:COLUMN-LABEL.
END.
PUT STREAM rpt UNFORMATTED '"' SKIP.
 
You beat me to it.

Code: 
FUNCTION BufferFieldLabel RETURNS CHAR (pi_cTable AS CHAR, pi_cField AS CHAR):
    DEF VAR hBuffer AS HANDLE NO-UNDO.
    DEF VAR hField AS HANDLE NO-UNDO.
    DEF VAR cBufferFieldLabel AS CHAR NO-UNDO.
 
    CREATE BUFFER hbuffer FOR TABLE pi_ctable .
 
    IF VALID-HANDLE (hbuffer) THEN DO:
        ASSIGN hField = hbuffer:BUFFER-FIELD(pi_cfield).
        IF VALID-HANDLE (hfield) THEN ASSIGN cBufferFieldLabel = hField:LABEL.
    END.
    RETURN cBufferFieldLabel.
END FUNCTION.
FUNCTION TitleLabels RETURNS CHAR (pi_cList AS CHAR):
    DEF VAR iTitleLabelsloop AS INT NO-UNDO.
    DEF VAR cTitleLabels AS CHAR NO-UNDO.
    DEF VAR cTitleLabelsEntry AS CHAR NO-UNDO.
    DO  iTitleLabelsloop = 1 TO NUM-ENTRIES (pi_cList):
        ASSIGN cTitleLabelsEntry = ENTRY (iTitleLabelsloop,pi_cList)
               cTitleLabels = cTitleLabels + (IF cTitleLabels = "" THEN "" ELSE ",") + BufferFieldLabel (ENTRY (1,cTitleLabelsEntry,"."),ENTRY (2,cTitleLabelsEntry,".")).
    END.
    RETURN cTitleLabels.
END FUNCTION.
 
MESSAGE TitleLabels ("Table1.Field1,Table2.Field2,Table3.Field3") SKIP
        TitleLabels ("Table4,Field4,Table5,Field5,Table6.Field6")
    VIEW-AS ALERT-BOX.

Just plug in your values for tables/fields and away you go.
 
Thanks Osborne and sphipp that was exactly what I was looking for. I think sphipp's will work best for me.

I do have one question though. Would their possibly be a way I could change sphipp's to work as an include file and then use a &SCOPED-DEFINE to create the list of fields? This way I could use it similar to the following:

Code: 
&SCOPED-DEFINE fields "Table1.Field1,Table1.Field2,Table1.Field3".
{TitleLabels.i}
 
whwar9739 said:
Thanks Osborne and sphipp that was exactly what I was looking for. I think sphipp's will work best for me.

I do have one question though. Would their possibly be a way I could change sphipp's to work as an include file and then use a &SCOPED-DEFINE to create the list of fields? This way I could use it similar to the following:

Code: 
&SCOPED-DEFINE fields "Table1.Field1,Table1.Field2,Table1.Field3".
{TitleLabels.i}
Click to expand...

Thanks Everyone. I have actually figured out how to modify the code that sphipp gave me to work as an include file similar to above. Here is what I changed.
From:
Code: 
MESSAGE TitleLabels ("Table1.Field1,Table1.Field2,Table1.Field2")
    VIEW-AS ALERT-BOX.
To:
Code: 
MESSAGE TitleLabels ({&FieldList})
    VIEW-AS ALERT-BOX.

Again thanx everyone for all the help.
 
No problem.

The trouble with using a SCOPED DEFINE is that it limits you as you are hard-coding the fields in the report.

So, if you want to add/change/delete fields then you have to change the program.

If you stored the field list in a database then all you need to do is to FIND the record and use the field, so you can update the field list without changing/recompiling/distributing the program.
 
sphipp said:
No problem.

The trouble with using a SCOPED DEFINE is that it limits you as you are hard-coding the fields in the report.

So, if you want to add/change/delete fields then you have to change the program.

If you stored the field list in a database then all you need to do is to FIND the record and use the field, so you can update the field list without changing/recompiling/distributing the program.
Click to expand...

Thanks sphipp, fortunately the programs that would use it as an include program would all be simple, one-time use programs for changing data in the database and creating a report of primary key and old and new values of changed fields.
 
You must log in or register to reply here.
Back
Top