Cognos tab delimited CSV including quotes and tabs on some values within field

[Patrick77]

Hi,

I am using Report Studio 10.2.2 with a relational package to query some data and return the results in a csv text file, which is tab delimited by default.  My problem is that a handful of the million or so values in an address field are returned with quotes and unintended tabs (shown as <TAB>) which leads to file import problems in downstream processing. The data type for the field in question is VARCHAR2. This is an example of what I mean:

ID   ADDRESS
53   453 Greenbriar Dr.
56   "0100 Mound Road <TAB> , APT 1"
57   Rua Barao de Loreto,581
24   "330 W Sycamore St<TAB> "
25   111 N Morrison
26   128 N Indiana Ave
66   46 Jeff Drive
67   215 N Bell St
23   24 Myrtle Street

ID #66 was the same as #24, but with leading spaces - I manually removed the leading spaces and it is no longer an issue. My question is, I know how to use trim, but this is not working.

For example, this does not work to prevent the issue with ID #24

Code Select Expand

Select
ID,
TRIM(ADDRESS)
FROM MYDATA


I figured the space preceding the comma (which exists) must be the culprit for ID#56, so I was going to basically just use: replace(trim(address), ' , '  , ','))
however, this does not work either. 

Please let me know if you have any ideas.  I would like to continue using the CSV output if possible and do not want to have to manually edit these addresses for my purposes.

[dax]

Hi

It looks like TRIM will only remove leading and trailing characters.  Given that you mention the data type is VARCHAR2 I'm assuming that you're using Oracle?  If so, can you use something like this?

replace(replace ([Address],chr (10),''),chr(34),'')

I've tested this on SQL server using the char function and works fine.  I know the use of database specific functions is generally not advised but in this case I don't know how you specify a tab character without using an Oracle function (happy if someone can shed light on this).

Cheers
Dax

[Patrick77]

Thanks for the suggestion,
I tried this, but the result is the same. To be clear, these quotes and tabs don't show up if I open the file in Excel, but this is the case when opened in a text editor, or imported as a text file. 

I might be able to deal with the situation if I change the import specifications.

Hi

It looks like TRIM will only remove leading and trailing characters.  Given that you mention the data type is VARCHAR2 I'm assuming that you're using Oracle?  If so, can you use something like this?

replace(replace ([Address],chr (10),''),chr(34),'')

I've tested this on SQL server using the char function and works fine.  I know the use of database specific functions is generally not advised but in this case I don't know how you specify a tab character without using an Oracle function (happy if someone can shed light on this).

Cheers
Dax

[dax]

Are you certain that the characters are tabs and double-quotations (ASCII 9 and 34 respectively?).  Happy to have a further look if you can post the file exactly as it's output by Cognos - just ID 56 would be ok.

[Patrick77]

These are the exact outputs:
"30100 Mound Road   , Office S127"
"330 W Sycamore St   "

Please do know that I "fixed the issue" by changing the import specs to let " " indicate a character string.
I still don't understand though why what we have tried doesn't work.

Are you certain that the characters are tabs and double-quotations (ASCII 9 and 34 respectively?).  Happy to have a further look if you can post the file exactly as it's output by Cognos - just ID 56 would be ok.

[dax]

Would you be able to attach the actual text file?  I'm not sure if it will render the same within a web page.

[Patrick77]

Attached - open in a text editor to see the tabs, not Excel.
Sorry for the ID number changes, basically the same data.

I'm not so much worried about the double quotes as I am the tabs because the output file is tab delimited.
Again, I tried this in Cognos and it didn't work... it does, however in Access.

replace ([Address1],chr (9),'')


I have fixed the situation on my end by changing the input specification, but it is still annoying.

Would you be able to attach the actual text file?  I'm not sure if it will render the same within a web page.

[dax]

Not sure what I can suggest here.  I loaded your data for row 22 into an Oracle field.  I then created a SQL query like so:

select address
,replace(address,chr(9)) as updated_value

I ran this report, exported to CSV and opened in a text editor - the "updated_value" field had no quotation marks and no tab characters.

strange.....