Title: Formatting a Report to Export to CSV

Title: Formatting a Report to Export to CSV

Abstract: This document details the steps to format a report within Report Design Aid (RDA) for export to CSV. It also includes tips on exporting to CSV including how to setup a report for one-time execution, setting up a report to always export to CSV, international considerations and web considerations.

Table of Contents

1. Formatting a Report for Export to CSV

Columnar/Tabular Sections

Group Sections

2. One-Time Execution of a Report to CSV

3. Changing Report to Always Execute to CSV

4. International Considerations

5. Web Considerations

Formatting a Report for Export to CSV

Without doing some reformatting within Report Design Aid, a report will not always export cleanly to Excel. This section details the necessary reformatting required to get a clean CSV output from Report Design Aid (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, within RDA, select the option for Layout/Grid Alignment. Set the horizontal grid alignment to 52 units and turn on the “snap to grid”.

Each column you see with this setting is equivalent to one column in the Excel spreadsheet. In order to have a report export cleanly, each column or variable on the report needs to correspond with a grid line that appears in RDA. Change the width of each column or the position of the variable so that they align with a grid line.

Columnar/Tabular Sections

In columnar and tabular sections, the width of each column needs to be adjusted so the column begins and ends with a corresponding grid line. If a column overlaps more then one column of grid lines in Report Design Aid, the data will create additional blank columns in Excel and result in additional commas between data in the Comma Delimited File. If no blank cells or additional commas are desired in the resulting CSV file, each column needs to be sized to be the same size as the grid lines that display in RDA when the horizontal spacing has been changed to 52. Although this will result in truncated data on the PDF report, the full data is exported to the CSV file and can be viewed by increasing the size of the columns in Excel. If the user needs to use a report for both PDF and Export to CSV, a version can be created just for exporting and all changes made at the version level. This is the standard recommendation. The following is an example of how a columnar section should look to submit cleanly to CSV.

Group Sections

In group sections, each variable needs to be moved so the variable begins at a grid line that appears in RDA. If no blank cells or additional commas are desired in the resulting CSV file, then the variables need to be overlaid on top of each other so that each variable starts at a sequential grid line. Although this will result in overlaid data on a PDF report, the full data is exported to the CSV file and can be viewed by increasing the size of the columns in Excel. If the user needs to use a report for both PDF and Export to CSV, a version can be created just for exporting and all changes made at the version level. This is the standard recommendation. The following is an example of how a group section should look to submit cleanly to CSV.

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 then one data field at the same vertical alignment is within 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.

Back to Top

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 Document Setup tab of the Printer Selection dialog.

Back to Top

Changing a 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. This is a version level setting.

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.

Back to Top

International Considerations

A comma separated value file will not work in a country using the comma as a decimal marker. Therefore, the Export to CSV option will recognize when the decimal separator is a comma, and rather then creating a comma delimited CSV file, it will instead create a tab-separated file with a .txt extension that can be opened in NotePad. If you still wish to have the output created as a CSV file, you can make the following changes in the jde.ini.

[UBE]
prtCSVExtension=.csv
prtCSVSeparator=x where x is the value of the desired separator character. (I.e. prtCSVSeparator=;)

NOTE: Since a .CSV file is by default opened with Microsoft Excel, it will not be formatted correctly unless the workstation has the appropriate regional setting. On the workstation, from Control Panel and Regional Options on the Numbers tab, the symbol for List Separator must match the INI setting for prtCSVSeparator.

Reports that are processed on the enterprise server use the settings in the jde.ini of the enterprise server rather then the workstation jde.ini. Therefore, when making the above jde.ini setting changes, they need to be made on both the workstation and enterprise server.

Back to Top

Web Considerations

When users view a CSV file from the HTML client, if the resulting Excel spreadsheet displays all the fields in the first column only, the workstation is missing a required registry setting.

HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/csvextension=.csv

This setting should be entered on the individual workstations (not on the web server).

In addition, verify that the MIME type is appropriately defined as application/csv and FileType to .csv.

Back to Top

Bottom of Form

Bottom of Form