none
Text column with numeric values exporting to CSV without double quotes in SSRS

    Question

  • I'm exporting an SSRS report to CSV.  This report has quite a few columns that are text and numeric.  Can someone show me what i should do so that when i export a report to a csv file that columns designated as text will have double quotes on the values irregardles of whether the value is numeric or text.  Currently, when exporting to CSV, the value will have double quotes if the value starts with a character and no double quotes if the starts with a number. 

    Friday, July 17, 2009 7:10 PM

Answers

  • Sorry, my initial answer wasn't correct.  The RS CSV renderer doesn't add explicit quotes around strings, so the quotes must already exist in the fields that come from your data source.

    You could use the following expression pattern in your textboxes to add explicit quotes if a field is numeric or if it contains an unquoted string:
    =iif(Left(CStr(Fields!A.Value), 1) = """", Fields!A.Value, """" & CStr(Fields!A.Value) & """")


    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 20, 2009 11:14 PM
    Owner
  • Hi,

    For the csv export, the double quote (") character is the qualifier character, since in excel (which can consume csv), having special characters inside of a csv field such as newline, tab, comma, etc... requires you to wrap the csv field inside of the qualifier for it to show up correctly in excel. Furthermore, if you have inside of your field the qualifier, in addition to wrapping your field with the qualifier (which is also double quote by default), but the csv renderer needs to replace the qualifier with 2 qualifiers. Therefore, if you have a field value such as: hello",world\r\n, for that value to show up exactly as it is in excel when opening up a csv file, the value of the that field inside of the csv file needs to actually be: "hello"",world\r\n". Therefore, even if in your report you add explicit quotes around your value, in the raw csv file, your field value of "<fieldValue>" will show up as """<fieldValue>""" in order for excel to interpret that csv field correctly and show it as "<fieldValue>". You can set the qualifier to some other value instead of the double-quote character (see http://msdn.microsoft.com/en-us/library/ms155397.aspx), but keep in mind that your field value of "<fieldValue>" will now show up as <qualifier>"<fieldValue>"<qualifier> in the csv file, and AFAIK, you can't set the qualifier to empty string.

    HTH,
    Ke
    Ke Xu - MSFT Reporting Services
    Tuesday, July 21, 2009 9:08 PM
    Answerer

All replies

  • In the report textboxes that show numeric values, you could apply an explicit string conversion so that all textboxes contain string values.
    For example:
    =CStr(Fields!NumericField.Value)
    =Format(Fields!PercentField.Value, "P2")

    HTH,
    Robert

    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Tau LiangModerator Sunday, July 19, 2009 11:47 PM
    • Marked as answer by DDavid_SF Monday, July 20, 2009 4:53 PM
    • Unmarked as answer by DDavid_SF Monday, July 20, 2009 4:54 PM
    Sunday, July 19, 2009 12:06 AM
    Owner
  • Robert:  I tried using the CStr in the Expression section of the textbox but it still didnt put any double quotes when i exported the report to csv.  The field that i'm looking at is an accounting period.  So in this case it would be a 1 or a numeric value.  I was expecting to see it in the csv file as "1".  Thanks for your reply.
    Monday, July 20, 2009 4:58 PM
  • Sorry, my initial answer wasn't correct.  The RS CSV renderer doesn't add explicit quotes around strings, so the quotes must already exist in the fields that come from your data source.

    You could use the following expression pattern in your textboxes to add explicit quotes if a field is numeric or if it contains an unquoted string:
    =iif(Left(CStr(Fields!A.Value), 1) = """", Fields!A.Value, """" & CStr(Fields!A.Value) & """")


    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 20, 2009 11:14 PM
    Owner
  • Hi,

    For the csv export, the double quote (") character is the qualifier character, since in excel (which can consume csv), having special characters inside of a csv field such as newline, tab, comma, etc... requires you to wrap the csv field inside of the qualifier for it to show up correctly in excel. Furthermore, if you have inside of your field the qualifier, in addition to wrapping your field with the qualifier (which is also double quote by default), but the csv renderer needs to replace the qualifier with 2 qualifiers. Therefore, if you have a field value such as: hello",world\r\n, for that value to show up exactly as it is in excel when opening up a csv file, the value of the that field inside of the csv file needs to actually be: "hello"",world\r\n". Therefore, even if in your report you add explicit quotes around your value, in the raw csv file, your field value of "<fieldValue>" will show up as """<fieldValue>""" in order for excel to interpret that csv field correctly and show it as "<fieldValue>". You can set the qualifier to some other value instead of the double-quote character (see http://msdn.microsoft.com/en-us/library/ms155397.aspx), but keep in mind that your field value of "<fieldValue>" will now show up as <qualifier>"<fieldValue>"<qualifier> in the csv file, and AFAIK, you can't set the qualifier to empty string.

    HTH,
    Ke
    Ke Xu - MSFT Reporting Services
    Tuesday, July 21, 2009 9:08 PM
    Answerer