How to get the report data in CSV format in .txt file

[Kavya]

Hi,

I am trying to schedule a report to run and email daily in csv format. My expectation is to receive .txt file in the email and the data in it should be comma separated. I am using cognos 10.1.1 version

I was referring to this article:
https://www.ibm.com/support/pages/when-running-report-csv-format-interactively-or-scheduled-all-data-shown-single-column

As suggested in the article I did set the csv parameter values at the parent level and removed from the service level. But it did not work.
I am receiving the attachment in .xls format. When I open this file in notepad, I see the data in tab separated format rather than comma separated.
Setting these same parameters in cognos 10.2.1 version works fine for me. But just not in 10.1.1.

Can someone please advise?

[Kavya]

At the Batch Report Service and Report Service Level, I have these parameter values:

RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET set to TRUE
RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE set to 35000
RSVP.EXCEL.XLSXDATA set to TRUE
RSVP.EXCEL.NUMBEREDSHEETNAMES set to FALSE

Does any of these parameter values is stopping in generating csv file?

[dougp]

I think what you need is these:
http://www-01.ibm.com/support/docview.wss?uid=swg21586401
https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cra.doc/r_reportService_adv_settings.html#reportService_adv_settings

Here is the info I wrote up when investigating this:


When the user runs a report to the CSV output format, the default settings cause a tab-delimited text file to be created with a .xls extension. Tab-delimited is not what most people or applications expect when requesting a CSV. Also, Excel complains that the content (TSV) doesn't match the extension (XLS).

To get Cognos to produce a comma-delimited text file with a .csv extension:

• Cognos Administration | Status | System
• In Scorecard, change view from All servers to Report.
• In ReportService go to Set properties.
• On the Settings tab, find the Environment | Advanced settings item and click Edit....
• Add these key/value pairs:

RSVP.CSV.MIMETYPE	applicaton/csv	Yes, it's not spelled correctly. There's a typo in Cognos.
RSVP.CSV.DELIMITER	,
RSVP.CSV.ENCODING	utf-8

• Scroll to the bottom and click OK (twice).
• In Scorecard, change view from Report to Batch Report.
• In BatchReportService go to Set properties.
• On the Settings tab, find the Environment | Advanced settings item and click Edit....
• Add the same key/value pairs.

Cognos encodes comma-delimited CSV files as UCS-2 LE BOM. Excel prefers ANSI or UTF-8. Excel opens CSV files from Cognos and displays all of the data in column A.


This is a system-wide setting.  It will affect all reports.

The problem I had was getting stakeholder buy-off.  When I discovered the problem, users had already created processes that relied on Cognos's bad default behavior and Excel not being capable of handling any variation of CSV reliably that changing CSV to be comma-delimited text would be disruptive.

[Kavya]

Thanks Dougp.

I have followed the same steps as you have mentioned. I have scheduled the report to run and email to me.  I got the report in an email with .csv extension. This file opens up in Excel by default. When I try to open this file in notepad, I see the data with tab separated but not comma.

I was expecting to receive the file with .txt extension. If that is not possible then I can live with that. But, when I view the data in the file it has to open in a notepad or text document and the data has to be comma separated. Cognos version I am using is 10.1.1 on Linux servers.

Any advice?

[dougp]

If you had followed the instructions (for the Report service), you would get a .csv file that contains comma-delimited text in utf-8 encoding.  Perhaps some of your other settings for the Batch Report service, the Query service, or the Report service are interfering.

By default, Windows will want to open a .csv file in Excel.  Unfortunate since Excel isn't good at handling them.

[Kavya]

I do not have any parameter settings in Query Service.

Other parameter settings I have in Report and Batch Report Services are:
RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET set to TRUE
RSVP.EXCEL.EXCEL_2007_OUTPUT_FRAGMENT_SIZE set to 35000
RSVP.EXCEL.XLSXDATA set to TRUE
RSVP.EXCEL.NUMBEREDSHEETNAMES set to FALSE

I also tried with saving these same settings (RSVP.CSV.MIMETYPE , RSVP.CSV.DELIMITER and
RSVP.CSV.ENCODING only) at the parent level (i.e in Configuration > Dispatcher and Services > Set Properties). But still, no luck.

[Kavya]

Any more Ideas/Suggestions please?

[Cape Cod Gunny]

I am able to get .txt file output using "Advanced Settings". However only "Tab" and "Pipe" produce .txt files. "Comma" and "Semicolon" produce .csv files.
See attached cognos-txt-files.png

[Kavya]

My requirement is to auto email the report rather than saving the report file in a file system. Currently, I do not have the option to save the file to a file system in my current Cognos Configurations settings.

In the email delivery options, I do not see the Advanced options. Attached is the image.

[adam_mc]

It should absolutely work if you have the following environment variables set (and then not superseded):

RSVP.CSV.DELIMITER = ,
RSVP.CSV.TERMINATOR = CRLF

Also, if you have a distributed environment, you need to make sure that these are set on all servers/dispatchers.

Hope this helps.
Thanks,
Adam.