Understanding Exporting to CSV
CSV files are used to output tabular data. In addition to viewing the report in a CSV file, you can manipulate the report data after the report finishes processing. To view report data in a spreadsheet program, such as Microsoft Excel or Lotus 123, select the export to a CSV option. You can select the CSV option using these methods, each with a different result:
In RDA for the report template.
Use this option to ensure that the report is output to a CSV file every time any of the associated batch versions are run.
Select a report template and in RDA, select the Export to CSV option in Print Setup.
The Export to CSV option is selected by the system at runtime. When a report template is defined to export to CSV for every instance, you can clear the Export to CSV option at runtime if you do not want the batch version to export to a CSV file for a single submission.
In RDA for the batch version.
Use this option to ensure that the report is output to a CSV file every time this specific batch version is run.
Select a batch version and in RDA, select the Export to CSV option in Print Setup. The batch version specifications will include information to export the output to a CSV file.
The Export to CSV option is selected by the system at runtime. When a batch version is defined to export to CSV for every instance, you can clear the Export to CSV option at runtime if you do not want the batch version to export to a CSV file for a single submission.
At runtime.
Use this option to output batch versions to a CSV file for a single submission only.
When running batch versions locally, select the Export to CSV option to submit the batch version.
When running batch versions on the server, select Export to CSV (Comma Delimited) on the Document Setup tab of the Printer Selection form.
Before exporting report data to CSV, you should review the report and follow these recommendations:
Recommendation |
Description |
|---|---|
Set the horizontal grid alignment to 52 and select the snap to grid option. |
The default column width in spreadsheet programs is equivalent to 52 units in RDA. For best results, use this grid guideline so that each column included in the report template is equal to a column in the spreadsheet program. |
Ensure that no fields of the report overlap. |
If a data field overlaps into the next column, the data in the spreadsheet displays in discrete columns. You can wrap the text in a cell after the data is exported to the spreadsheet. Delete unused columns in the spreadsheet and reformat information as needed. |
Align data fields vertically. |
If data fields are not aligned vertically, they display in separate rows in the spreadsheet. If more than one data field with the same vertical and horizontal alignment displays in a column, only one of these fields displays in the CSV file. The first field output during the export process occupies the cell in the spreadsheet. |
Format dates properly. |
Spreadsheet programs typically use the same date format used in the report. |
Use the Auto Format feature. |
After the report is exported cleanly, use the Auto Format feature in the spreadsheet program to further format the report. |
Countries that use a comma as a decimal marker. |
In these countries, the decimal separator is recognized as a comma when the report exports. Tabs are stripped out instead of commas and a tab-separated file with a .txt extension is created. The information transfers as flat text, so totaling columns display only text. You must then set up totaling in the spreadsheet program. |
When you export batch versions to CSV:
A CSV file is created in the PrintQueue directory.
A PDF file is created in the PrintQueue directory.
The CSV file is displayed by a spreadsheet program such as Microsoft Excel or Lotus 123, which launches automatically when you run the batch version locally.
When you run the batch version on a server, select View CSV from the Submitted Job Search form to launch the spreadsheet application and view the file. Only single spacing and portrait orientation is supported for CSV files. Drill-down links are ignored in CSV generation.
The Export to CSV option recognizes when the decimal separator is a comma, and rather than creating a comma delimited CSV file, it creates a tab-separated file with a .txt extension that can be opened in Notepad. To create a CSV file when the comma is a decimal separator, make the following changes in the jde.ini.
[UBE] prtCSVExtension=.csv PRTCSVSeparator=,
Other file extensions and separators can be used by changing the jde.ini settings.
Reports that are processed on the enterprise server use the settings in the jde.ini of the enterprise server rather than 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.