Output a CSV File as Pipe Delimited rather than Comma Delimited?

[sjdig]

Hello again all, thank you for the help the other day with storing leading zeroes for Social Security Numbers in my report. It was quite beneficial.

However, I have a follow up question now that I'm producing the report as a CSV. Is there a way to set the separator to a pipe - | rather than a comma - ,?

The reason this need exists is that I'll be uploading the text file into our imaging system to facilitate the auto-filling of customer data upon the entry of an account number.

However, some customer full or last names stored in our system have commas within the value already.

For example:
John Doe, Jr.

Stored as:
First Name: John
Last Name: Doe, Jr.
Full Name: John Doe, Jr.

As you can see, this type of name presents a problem and using a comma as the separator will present problems from my text file as it will cut too early on these types of names.

Has anyone had to do something similar or have any advice on how to solve this issue?

Thank you in advance for any help or advice you can give.

sjdig

[MFGF]

Hello again all, thank you for the help the other day with storing leading zeroes for Social Security Numbers in my report. It was quite beneficial.

However, I have a follow up question now that I'm producing the report as a CSV. Is there a way to set the separator to a pipe - | rather than a comma - ,?

The reason this need exists is that I'll be uploading the text file into our imaging system to facilitate the auto-filling of customer data upon the entry of an account number.

However, some customer full or last names stored in our system have commas within the value already.

For example:
John Doe, Jr.

Stored as:
First Name: John
Last Name: Doe, Jr.
Full Name: John Doe, Jr.

As you can see, this type of name presents a problem and using a comma as the separator will present problems from my text file as it will cut too early on these types of names.

Has anyone had to do something similar or have any advice on how to solve this issue?

Thank you in advance for any help or advice you can give.

sjdig

Hi,

Does the below technote help?

http://www-01.ibm.com/support/docview.wss?uid=swg21338932

MF.

[Invisi]

My question: why do you bother to put a reporting tool in between your data warehouse and a CSV data dump? I assume this can be done faster and more efficiently by a DBA or an ETL tool.

[Boatsman]

Very good point

[rockytopmark]

While it may not be the best from a performance perspective, I can think of a few reasons why this is good to be done from a reporting tool, such as Cognos, as opposed to a DBA or ETL Tool:

• Data Security
• Leverage Calculations that are defined in the Framework Model
• Leverage Business Logic that may be adding value in the Framework Model
• Leverage Business term renaming done in Framework Model
• Single Source of the Truth mandates such as "All reporting is to be done out of Cognos" are common at bigger companies
• Most DBAs have more work than they can handle already, so using the BI tool prevents adding unnecessary work to their plates

Making your BI Tool, such as Cognos, the centralized place for Analysis is a good thing when all is said and done, IMNSHO

[Invisi]

In my opinion, the data warehouse can be your centralised place for analysis. If we go to the topic of analysis, why do you provide a CSV dump to your user instead of Analysis Studio on a usable framework? A CSV dump does not count as analysis or reporting to me. Either it's going into Excel, which means there is a tool adoption issue. If it's supposed to be about reporting, then we talk about a document with layout, etc., not CSV.

[sjdig]

Hi, I didn't realize this topic would gain such traction and spark a debate!

To be honest, I am not the database administrator for Cognos, but an end user of Cognos. I am an administrator for our imaging system though. I was just trying to accomplish a solution using the tools at my disposal. I wound up modifying an existing report from another user that had the settings already in place, which solved my issue. I still don't honestly know how he got those settings in that manner, but I have reached out to him regarding it.

I inquired in regards to querying the database directly and we deal with a third party that does not allow direct queries of that information. Hence, the need for using Cognos as a workaround to obtain the information.

[Invisi]

Clear about the third party.

[sjdig]

Just going to bump this to provide the answer if anyone else runs into this.

Let's say I want to run the report in this instance.

Click the run report button from the file system.

At Run with Options screen:
Click on advanced options to the right

At Run with advanced options screen:
Click box for Run in the background
Under Format:
Click box for Delimited text (CSV)
Under Delivery:
Click box for Save to the file system and click Edit the options

At Set the options when saving to a file system screen:
Click Advanced Options

At Set the options when saving to a file system screen:
Under Extended Formats section
Click option for Pipe with No Header No Quoted String

I have attached a PNG with the steps. I would have inserted images had I not currently been at my place of work. I'd recommend opening it with Paint as it looks a little nicer in that format.

Thanks for those that attempted to assist,
sjdig

[pacificbeavs]

Just going to bump this to provide the answer if anyone else runs into this.

Let's say I want to run the report in this instance.

Click the run report button from the file system.

At Run with Options screen:
Click on advanced options to the right

At Run with advanced options screen:
Click box for Run in the background
Under Format:
Click box for Delimited text (CSV)
Under Delivery:
Click box for Save to the file system and click Edit the options

At Set the options when saving to a file system screen:
Click Advanced Options

At Set the options when saving to a file system screen:
Under Extended Formats section
Click option for Pipe with No Header No Quoted String

I have attached a PNG with the steps. I would have inserted images had I not currently been at my place of work. I'd recommend opening it with Paint as it looks a little nicer in that format.

Thanks for those that attempted to assist,
sjdig

I'm using Cognos 11 and went through your tutorial... it appears that the option to choose a format (tab, semicolon, pipe, etc.) doesn't exist.  Can anybody confirm? 

Other documents online (and the one linked in this thread) say to change the setting at a high level (""Modify Properties for the CSV Output Format" section in the Administration and Security Guide").  However, if you change the delimiter type at a high level then all report CSV formats will be impacted, and that doesn't work so well when some users want tab, some want comma, and others want pipe.  Perhaps that's just a Cognos limitation..

Thanks!

[dougp]

Correct, that's done in Cognos Administration and affects every report run to CSV thereafter.

There appears to be some confusion earlier in this thread:  Cognos does not produce a comma-delimted file by default.  What it calls CSV is a tab-delimited file.  If Cognos is producing comma-delimited output when you select the CSV output format, your administrator has changed the setting to make that happen.

[pacificbeavs]

Correct, that's done in Cognos Administration and affects every report run to CSV thereafter.

There appears to be some confusion earlier in this thread:  Cognos does not produce a comma-delimted file by default.  What it calls CSV is a tab-delimited file.  If Cognos is producing comma-delimited output when you select the CSV output format, your administrator has changed the setting to make that happen.

Okay, thanks for confirming.

[RickHenderson]

...
There appears to be some confusion earlier in this thread:  Cognos does not produce a comma-delimted file by default.  What it calls CSV is a tab-delimited file.  If Cognos is producing comma-delimited output when you select the CSV output format, your administrator has changed the setting to make that happen.

This has always bugged me and I think it happens in Cognos 7 as well. To be pedantic, neither a tab-delimited file nor a pipe-delimited file is a CSV file, which stands for "comma separated values". I wish they would change the wording.

[dougp]

Hmmm...  Actually, there is no standard for CSV:  https://en.wikipedia.org/wiki/Comma-separated_values

It turns out that delimiting using tabs, spaces, pipes, the letter "Q", or whatever you want is still technically considered CSV.  You could even delimit values with \n and lines with a comma.  Wheee!

[aetcognos]

I am looking at the screenshot, and I don't see the "Advanced Options" At Set the options when saving to a file system screen:,  where you can select what delimiter can be used. Is that something that can be enabled in one f the xml files somewhere....

Just going to bump this to provide the answer if anyone else runs into this.

Let's say I want to run the report in this instance.

Click the run report button from the file system.

At Run with Options screen:
Click on advanced options to the right

At Run with advanced options screen:
Click box for Run in the background
Under Format:
Click box for Delimited text (CSV)
Under Delivery:
Click box for Save to the file system and click Edit the options

At Set the options when saving to a file system screen:
Click Advanced Options

At Set the options when saving to a file system screen:
Under Extended Formats section
Click option for Pipe with No Header No Quoted String

I have attached a PNG with the steps. I would have inserted images had I not currently been at my place of work. I'd recommend opening it with Paint as it looks a little nicer in that format.

Thanks for those that attempted to assist,
sjdig

[dougp]

That's because it isn't there.  Changing the CSV output properties is a system-wide setting available only through Cognos Administration.