Author Topic: How to get the report data in CSV format in .txt file  (Read 161 times)

Offline Pranathi

  • Full Member
  • ***
  • Join Date: Feb 2019
  • Posts: 20
  • Forum Citizenship: +0/-0
How to get the report data in CSV format in .txt file
« on: 05 Sep 2019 02:52:01 pm »
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?

Offline Pranathi

  • Full Member
  • ***
  • Join Date: Feb 2019
  • Posts: 20
  • Forum Citizenship: +0/-0
Re: How to get the report data in CSV format in .txt file
« Reply #1 on: 05 Sep 2019 02:57:08 pm »
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?

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 419
  • Forum Citizenship: +20/-1
Re: How to get the report data in CSV format in .txt file
« Reply #2 on: 06 Sep 2019 10:50:54 am »
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.MIMETYPEapplicaton/csvYes, it's not spelled correctly. There's a typo in Cognos.
RSVP.CSV.DELIMITER,
RSVP.CSV.ENCODINGutf-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.

Offline Pranathi

  • Full Member
  • ***
  • Join Date: Feb 2019
  • Posts: 20
  • Forum Citizenship: +0/-0
Re: How to get the report data in CSV format in .txt file
« Reply #3 on: 09 Sep 2019 10:03:51 am »
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?

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 419
  • Forum Citizenship: +20/-1
Re: How to get the report data in CSV format in .txt file
« Reply #4 on: 09 Sep 2019 10:25:06 am »
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.

Offline Pranathi

  • Full Member
  • ***
  • Join Date: Feb 2019
  • Posts: 20
  • Forum Citizenship: +0/-0
Re: How to get the report data in CSV format in .txt file
« Reply #5 on: 09 Sep 2019 11:22:41 am »
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.

Offline Pranathi

  • Full Member
  • ***
  • Join Date: Feb 2019
  • Posts: 20
  • Forum Citizenship: +0/-0
Re: How to get the report data in CSV format in .txt file
« Reply #6 on: 13 Sep 2019 02:22:20 pm »
Any more Ideas/Suggestions please?

 



       
Twittear