Author Topic: DDF.DEFN using WORK variables  (Read 6343 times)

DonQuixote

  • Rock Star
  • *****
  • Posts: 205
  • To Dream the Impossible Dream...
DDF.DEFN using WORK variables
« on: May 18, 2012, 04:35:04 PM »
I am trying to take an existing Report Writer report and want to put the same information into a (csv) file.
I thought a nice method would be to use the DDF.DEFN utility.
The catch is the report entirely uses WORK (mv) fields and the report writer uses the CONTROL file.
I thought I could just do the same thing using DDF.DEFN. 
I get nothing.  Suggestions?

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: DDF.DEFN using WORK variables
« Reply #1 on: May 21, 2012, 07:17:25 AM »
If you already have the /RD report, the easiest thing to do is to output the data using the DIF (Data Interchange Format) output option.

In /RD, press F6-Params and change the "Output Device" to D

By default, the output will go to your xxWORK file as an item called SBDIF.<port>. (note the trailing dot!)  You can specify a file and item prefix if you wish.  

From the F6-Params screen, Press F6-Addit and change "File/DIF Destn: File" to the file of your choice, and "Item" to the prefix of your choice.  

For example, if you set the prefix to "CSV", the item will be "CSV.<port>."

Once the item is generated, you can transfer it to your Windows system with a CSV extension.

Note:  use TWO spaces between fields on detail and break lines to ensure that all fields are put in their own column.  You can include data in Header rows, but it's best to align those fields with detail fields of the same type.  

For example, I export sales reports on a monthly basis.  The End of Month date in the header is aligned with the Invoice Date in the detail.

--Tom

DonQuixote

  • Rock Star
  • *****
  • Posts: 205
  • To Dream the Impossible Dream...
Re: DDF.DEFN using WORK variables
« Reply #2 on: May 21, 2012, 05:49:37 PM »
Thank you for the information.  I just could not get the DDF.DEFN to give me output from WORK variable.
Your solution works! 
A strange observation; the output is in the CSV format and is named CSV.ppp.
ppp is the port number.  But, there are also what appears to be 'work areas' created.
SPREADER.ppp  is the output format from the /RD with fieldnames, size, formats, etc separated by vm.
and then there is a series of files SPROUT.ppp.nn
which contain the output records using standard delimiters.

I get what I want so I'm happy... I just have to remember to take out the garbage.

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: DDF.DEFN using WORK variables
« Reply #3 on: May 22, 2012, 06:54:45 AM »
Two notes on this:

1) Older versions of SB+ would not create a record larger than 32K.  The sequence number on the SBROUT.port.seq item is a remnant of this.  The logic, however, is not entirely cleaned up.  On larger files, you may see a stray blank line added to your output (default name SBDIF.port.).

2) If you use double quotes in any of your fields (e.g., product descriptions with measurements in inches), the output may change them to single quotes.  For CSV format, the double quote is used to delimit fields that contain commas.  To include a double quote in such a field, that character should be doubled, e.g.,

Product,Description
NewBox,"Pine Box, 12"" x 15"" x 4"""


I have seen SB+ change this to:

Product,Description
NewBox,"Pine Box, 12' x 15' x 4'"


DonQuixote

  • Rock Star
  • *****
  • Posts: 205
  • To Dream the Impossible Dream...
Re: DDF.DEFN using WORK variables
« Reply #4 on: May 22, 2012, 12:12:04 PM »
Thanks again.
And for the ( " ) double quote warning.
I made sure the double quotes were double/double quotes before going into the report writer.
It made no difference.  Both double quotes were changed to single quotes.
Now I have something else to worry about.  :(

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: DDF.DEFN using WORK variables
« Reply #5 on: May 22, 2012, 01:43:28 PM »
The alternative is to rewrite the SPROUT... using CSV standards.  From BASIC, you could do this before you export the file.

CALL SB.OPEN("xxWORK",F.WORK,ERR)
IF NOT(ERR) THEN
   ID = "SPROUT.":PORT:".1"
   READU REC FROM F.WORK,ID THEN
      SWAP \"\ WITH \""\ IN REC
      { wrap values with double quotes or commas with double quotes }
      WRITE REC ON F.WORK,"SBDIF.":PORT:"."
   END
   RELEASE F.WORK,ID
END

There ought to be an SB+ setting for this, but if there is, I don't know where to find it.

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Re: DDF.DEFN using WORK variables
« Reply #6 on: June 15, 2012, 11:42:58 PM »
The DDF.DEFN will run from @WORK, but you need to be sure you have the same Process After Read.  There may be other considerations as well, but it is possible.
-Kevin
Accidents "happen"; success, however, is planned and executed.