Tips on Export to CSV

One-Time Execution of a Report to CSV

When executing the report, if executing it locally click on the “Export to CSV” from the submission dialog instead of “On Screen” or “To Printer". When running the report on the server, check the “Export to CSV” box on the “Advanced” tab of the “Printer Selection” dialog.

Changing Report to Always Execute to CSV

Within RDA, choose Report and Printer Setup, check the “Export to CSV” box to set the report to Export to CSV in the specifications.

You can uncheck the “Export to CSV” box at submission time to prevent a report from creating a CSV file when you have set it in the specs of the report. It will only affect the one run and “Export to CSV” will still be set in the specs.

Note:

·         Even though the CSV file is created and Excel pops up to view it, a PDF file is still created in the PrintQueue directory.

·         Cover pages will not go into the CSV file. Report Headers and Page Headers will only occur once in the CSV file. Report Footers and Page Footers will not appear in the CSV file. All of these will still be in the PDF file.

·         Fields that have wrapping text will appear all in one cell in the CSV file, instead of taking up multiple rows in the spreadsheet. This allows wrapping to be done in the cell by the spreadsheet application.

Considerations During Report Design

Not all reports will come through into Excel perfectly. Particularly problematic are group sections with lots of one-character data fields. This section is intended to assist in getting clean CSV output from RDA.

Excel’s default column width on startup is equivalent to about 52 units in RDA, and this value was built into the CSV code. For best results, set your horizontal grid alignment in RDA to 52 units and turn on the “snap to grid”. Each column you see with this setting is equivalent to one column in your Excel spreadsheet. Align the data so the right edge of each data field is in a column. It is okay if a data field overlaps into the next column in RDA, in Excel the data will be in discrete columns.

By following these rules, you should be able to design reports that export to Excel perfectly.

Note:

·         If the data fields are off by even one unit in the vertical direction in RDA, they will appear in separate rows in the spreadsheet.

·         If more than one data field at the same vertical alignment appears in a column, only one of these fields will be in the CSV file. The first one to get output “wins” the right to occupy the cell.

International Considerations

Obviously, a comma separated value file will not work in a country using the comma as a decimal marker. The Export to CSV enhancement will recognize when the decimal separator is a comma, and will instead create a tab-separated file with a .txt extension. Instead of commas being stripped out, tabs are stripped out of the data.

How to Design Reports for Printing Bar Codes

Barcode Overview

The barcode that was selected to be supported for OneWorld was BC C39 3 to 1 Medium. The basis for the selection was that there was a greater requirement for it from the customers.

What does BC C39 3 to 1 Medium stand for ?

3 to 1 – 3 to 1 implies that the width of the wide element in the 3 to 1 ratio barcode fonts are 3 times the width of the narrow elements.

Medium – the width of a single character is 65% its height.

Barcode Printer

Initially a Barcode Printer must be setup within OneWorld®. Please note before printing to either a PostScript or PCL printer the printer font name and true type font name need to be tied together with the physical printer in the Bar Code Support application. Please refer to the System Administration publication, and the section Printing OneWorld® Reports, Setting up a OneWorld® Printer to Use a Barcode Font for printer setup. Another help setup document is How to Print Bar Codes within OneWorld®.

Report Design

You may want to refer to the Enterprise Report Writing Publication and Working with Barcodes under the Advanced Reports Enhancements section.

Some important steps to follow are:

9. Launch the Report Design Aid (RDA) Tool for your report
10. Select the section where you wish to place the barcode
11. From the Insert menu, choose Constant Field, place the constant within the section
12. Double click on the new constant to launch the properties window.

Change the Font to BC C39 3 to 1 (Note: if this is not available in the list, please contact your System Administrator to load C39m3.ttf into your local WINNT\Fonts folder and the B7\system\Resource\truetype folder)

13. A barcode constant variable can be loaded in 2 ways as discussed in the following two scenarios:

Scenario 1:

If the barcode will always hold the same value, then you only need to enter the value into the Name for the Constant Properties. This value MUST be surrounded by asterisks to let the scanner know when to start and stop. Please see the following section about Barcodes and encoding characters.

Scenario 2:

If the barcode will change from row to row, then leave the Name for the Constant Properties as provided by the Tool or change it to be identifiable to the developer, but do NOT surround it by asterisks. Within the Do Section Event Rules for the section, use an assignment statement and assign the Constant variable the appropriate value. This value must be surrounded by asterisks to let the scanner know when to start and stop. If the value is a variable then you can use the concatenate string system function within the event rules to surround the value within the variable by asterisks.

Example:

RC Constant 000018 = concat([RC Constant 000018],"*")

RC Constant 000018 = concat("*", [RC Constant 000018])

Encoding: Programming a Barcode String

If you are using utilizing Scenario 1 above and your barcode is manually assigned then you must encode it (or program it) specifically for Extended Code 39. The Code 39 barcode consist of three segments and an area of white space before and after the barcode symbol.

The three segments are:

1. A start code ( * ).

2. The character string to be mapped to the barcode string.

3. A stop code ( * ).

For example to encode the string - ABCD1234 in Code 39, just add an asterisk to the beginning and end of the string so the result should be - *ABCD1234* and place this in the Name for the Constant Properties. Or to substitute with a Character as found in the chart below then substitute with the Barcode Characters column shown. For example to encode !"#$%&-. then place */A/B/C/D/E/F-.* in the Name for the Constant Properties.

The following table provides a chart of the supported Code 39 characters.

 Title: Database Output to CSV File 

How to perform a Database Output 

It is possible to send information from a UBE Batch Application into an Excel Worksheet for additional manipulation of the data.

The easiest option is to select the option for Export to CSV available both within Report Design and also at runtime.  However, the Report Design Aid (RDA) also has the functionality to export data from a section by using the Database Output option. This allows information to be exported from one section to a comma delimited, ASCII variable file. Users can then open the text file using Microsoft Excel’s functionality and format the data accordingly.

1.     Define Database Output in RDA

Note:  Set focus on the section that you want exported, select "Section" and select "Database Output"

3. Define the Database Output parameters. The Name field will be the name of the output file.  The Operation(s) can be Insert Only, Update, Insert or Update or Delete.  The Type can be a  Fixed length record file, or Comma delimited file. For a Fixed length record file or Comma delimited file, the file name must end in ".txt".  Database Output should not be used to update standard J.D. Edwards tables.  This can be accomplished by using Table I/O within the event rules.
4. Map the parameters of the input business view columns to the output file. The Source Section is the name of the Section and the Source Object will allow you to select the business view column or variable you wish to export (double click on the blank field).
5. Save and exit out of RDA. Run the new version that you have created locally and perform a Find file from within Windows Explorer.  If a path was not specified in the Name field, the default save location is the root directory B7. 

 The UBE will generate a .pdf and also a text file. You can suppress the .pdf file by selecting the "Suppress All Output" in Report Properties from within RDA. The text file will look similar to the one below. 

5.    Format the Text File in Microsoft Excel ®

     Open the text file in Excel and follow the text import Wizard.  You can select to have the data inserted into the second row (this is helpful if you wanted to include a column header for each column.