I have a requirement to run a report as a data dump to a .csv file where every column is wrapped in double quotes.
Each column is defined as '"' || cast([Data Item] as varchar(256)) || '"'.
This works perfectly when the report is run to HTML, but when run to .csv, only the first column is wrapped in single double quotes. All other subsequent columns are wrapped in triple double quotes!
Any thoughts on how to resolve or explain the post first column behavior would be greatly appreciated.
Thanks in advance,
Adam.
What is the expected outcome? Are you just trying to get the CSV file to look like...
"a","b","123","2025-02-20","Hello, World!", "She said, ""I meant 20 widgets"""
...but you are getting...
"a",b,123,2025-02-20,"Hello, World!", "She said, ""I meant 20 widgets"""
...or...
a,b,123,2025-02-20,"Hello, World!", "She said, ""I meant 20 widgets"""
...?
To confirm this is CA 12.0.2
Per your format, I want:
"a","b","123","2025-02-20","Hello, World!", ....
A set of double quotes around every column regardless of column data type.
But I am getting:
"a","""b"""","""123""","""2025-02-20""","""Hello, World!""", ....
A set of double quotes around the first data item and all subsequent data items have 3 sets of double quotes around them.
This is not what I would expect given they are all using the same concatenate calculations around each data item.
The first column is always the one that is correct regardless of which column is put first and the subsequent ones are always incorrect.
Could this be in anyway related to RSVP.CSV. Configuration Environment variables?
I have:
RSVP.CSV.DELIMITER = ,
RSVP.CSV.TERMINATOR = CRLF
Is there any thing else I need to set?
Again, when run to HTML output looks perfect, so to me this indicates something odd in producing the .csv
Any thoughts would be greatly appreciated.
Thanks,
Adam.
QuoteThis is not what I would expect given they are all using the same concatenate calculations around each data item.
That does seem odd.
Quotewhen run to HTML output looks perfect
Define "perfect". Do you mean you see the quotes? Because, unless the quotes are part of the data, you should not.
The routine that produces the CSV will do whatever is needed to produce valid CSV. Since you are adding quotes to your values, the value is being surrounded by quotes and the quotes in the data are being escaped by being doubled.
QuoteI have a requirement to run a report as a data dump to a .csv file where every column is wrapped in double quotes.
Is there a good technical reason that you would need...
"a","b","123","2025-02-20","Hello, World!","She said, ""I meant 20 widgets"""
...rather than...
a,b,123,2025-02-20,"Hello, World!","She said, ""I meant 20 widgets"""
...?
People don't read CSV, computers do. Any requirement to output CSV from Cognos indicates to me that you are using a reporting tool to perform data integration. There are data integration tools (SSIS, Informatica, etc.) that are designed for that task. Short of that, I'd use PowerShell or C# before I'd use Cognos Analytics.
To answer some of your questions.
When I run to HTML the results look as I would expect, a single set of quotes wrapped around each data item regardless of data type. To confirm each data item looks like '"' || cast([Data Item] as varchar(256)) || '"'.
Results are:"a","b","123","2025-02-20","Hello, World!",....
Again, this indicates that .csv output format is doing something unexpected to the result set.
The need is to replace a manual data dump for a Vendor executed out of Toad.
The idea is to match the current data format of the existing data feed so that the vendor has no impact on their end.
We want to automatically trigger it to run on a dependency of two file loads completing on the database (this we have successfully done).
I don't see any reason why Cognos shouldn't be a perfectly good fit to produce this output - There may be other tools, but Cognos should work too!
Again, any help would be greatly appreciated.
Thanks,
Adam.
QuoteResults are:"a","b","123","2025-02-20","Hello, World!",....
Again, this indicates that .csv output format is doing something unexpected to the result set.
If that is what you see in HTML,
"""a""","""b""","""123""","""2025-02-20""","""Hello, World!"""...is the expected outcome. That's because if a value contains a quote, the CSV must be encoded as such a way as to not lose that character when the data is read.
Using Excel, for example, if I start with this data...
| c1 | c2 | c3 | c4 | c5 |
| a | b | 123 | 2025-02-20 | Hello, World! |
| "a" | "b" | "123" | "2025-02-20" | "Hello, World!" |
|
...and save as CSV, I get...
c1,c2,c3,c4,c5
a,b,123,2025-02-20,"Hello, World!"
"""a""","""b""","""123""","""2025-02-20""","""Hello, World!"""Clearly, modifying your data by adding characters to every value is not the right answer here.
Looking through https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=reference-report-service-batch-report-service-advanced-settings I see you can set the qualifier, but there is no way to force the qualifier to be used when it is not needed.
A CSV parser will read the following rows as identical:
a,b,123,2025-02-20,"Hello, World!"
"a","b","123","2025-02-20","Hello, World!"It seems unlikely that the vendor can't consume generic CSV. Is the vendor really not using a generic CSV parser? Did they go to the effort to develop their own, non-standard CSV parser?
If this truly is a requirement, something like this PowerShell script would work.
$fileIn = Join-Path (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path "Book1.csv"
$fileOut = Join-Path (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path "Book2.csv"
Import-Csv -LiteralPath $fileIn | Export-Csv -LiteralPath $fileOut -UseQuotes AlwaysBut if you're going that route, you should also have PowerShell (or your script runtime of choice) run the query to get the data to begin with. This is not a good task for Cognos Analytics.
Doug, I agree with the fundamental points you are making for getting the vendor to accept a standard .CSV output (without any double quotes).
However, what I don't understand is why the following results would occur in Cognos for HTML vs. .CSV output???
Column1 '"' || cast([Data Item1] as varchar(256)) || '" where Data Item1 = a
Column2 '"' || cast([Data Item2] as varchar(256)) || '" where Data Item2 = b
In HTML, output is:
"a", "b"
In .CSV, output is:
"a", """b"""
For the .CSV output, why isn't the double quote wrapping working consistently for the first column vs. all subsequent columns?
It doesn't even matter what the data type is - character, numeric, date - I always get the same formatted set of results. First column single double-quotes, subsequent columns triple double-quotes.
If it is a matter of wrapping double quotes in additional double quotes, why isn't it doing it gor the first column?
It just doesn't make sense to me, but again I could be misunderstanding something!
Adam.