0

I am creating an expression combining multiple fields into a single field in SSRS. However when I am exporting to CSV, some of the records are being appended with additional double quotes. How do I fix this?

Original Data:

SCode|BuildingNumber|StreetName|City|State|
---------------------------------------------
ABC|   |123 Street|New York   |NY|
DEF|   |456 Street|Los Angeles|CA|
IJK|123|7th Ave   |Chicago    |IL|
XYZ|   |789 Cir   |Atlanta    |GA|

Expression I am using:

=Fields!SCode.Value & "#" & IIF(IsNothing(Fields!BuildingNumber.Value), Fields!StreetName.Value, Fields!BuildingNumber.Value & "\," & Fields!StreetName.Value) & "#" & Fields!City.Value & "#" & Fields!State.Value"

Data after exporting to CSV:

ABC#123 Street#New York#NY
DEF#456 Street#Los Angeles#CA
"IJK#123, 7th Ave#Chicago#IL"
XYZ#789 Cir#Atlanta#GA

Thanks!

Julaayi
  • 403
  • 2
  • 8
  • 23

1 Answers1

0

The CSV export should only be adding the text delimiter around a field if there's a delimiter character (a comma) or some sort of return charter.

Text qualifiers are added only when the value contains the delimiter character or when the value has a line break.

MS Docs

Check you text for commas, return characters, and line feeds.

Your examples don't have a comma but it may still have a return or line feed.

SELECT * 
FROM TABLE 
WHERE FIELD LIKE '%' + CHAR(13) + '%'
    OR FIELD LIKE '%' + CHAR(10) + '%'

The line feed and return characters are character numbers 10 and 13 in ASCII.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Thank you for pointing out that. I've updated my original post with the right data along with the expression I am using. I am still seeing the record enclosed with double quotes upon exporting to CSV. – Julaayi Jan 09 '20 at 18:08
  • @Julaayi - The row with quotes has a comma in it. The text qualifier quotes are added so the comma in the text isn't interpreted as a column break. You would need to replace the comma but whatever you're importing the CSV into should be able to read it though. – Hannover Fist Jan 09 '20 at 20:05
  • Not sure I understood you. I have two fields which I am combining in the SSRS expression into a single field with comma as separator. If either of the fields are empty, I only take the non-empty field but when both the fields have values, I separate them with comma. When I export the report to CSV, it adds the double quotes for the records that have the comma. I tried the escape character but it didn't work. Let me know if you want me to provide any other info. Thanks! – Julaayi Jan 09 '20 at 20:20
  • The quotes are being added because the data for Chicago has a comma in it while the others do not. `"IJK#123, 7thAve#Chicago#IL"` has a comma after the `123`. I'm not sure if that expression is the right one - none of your data has a slash and only one has a comma. – Hannover Fist Jan 09 '20 at 21:19
  • My apologies if it is confusing. The slash will be replaced only if there is one and I don't think that is the issue here. When I am appending both the Building Number and the Street Name, I am appending them with a comma. The data posted was after exporting it to CSV. Let me know if that doesn't make sense. – Julaayi Jan 09 '20 at 21:35
  • I've updated the original post to make it more clear. – Julaayi Jan 09 '20 at 21:46